How to get XElement with xml Descendants into SQL Server

Question

I need help to get my XML Elements using XElement into SQL Server. When I run the code the MessageBox.Show(element.ToString(), "element.value"); is displaying the <FIELD fieldName="ID">1234567</FIELD> and <FIELD fieldName="DateTime">05/02/2018 09:00:28</FIELD> in the MessageBox correctly. Can I now use this to upload to my tblMain in SQL Server?

Basically it is a string saved as input from a .msg body in outlook. This msg body has all the text as an XML in the body as plain text format.

I load the string input as xml document into XmlDocument.

With XElement I then parse this input as xml. I create a variable xitems with the xml Descendants as ("FIELD") where I run a foreach loop as variable element in the xitems. The output is now displayed in the MessageBox.

Please, what I am looking for is detailed help in how to get this into SQL Server to a table called tblMain for simplicity it has two columns ID and DateTime??

The code:

for (int i = 1; i < publicFolder.Items.Count; i++)
{                                                                        
    item = (PostItem)publicFolder.Items[i];
    if (item != null)
    {
        //MessageBox.Show(item.Body, "Body");

        //save the msg body into the string 
        string input = item.Body;


        //load the string input as xml document
        XmlDocument doc = new XmlDocument();
        doc.LoadXml(input);

        XElement xml = XElement.Parse(input);


        var xitems = xml.Descendants("FIELD");

        foreach (var element in xitems)
        {
            MessageBox.Show(element.ToString(), "element.value");

        }


        ////Create new instance of SQL Connection 
        //SqlConnection conn = new SqlConnection();

The xml file is :https://imgur.com/a/epCLd22

Anyone? It should be familiar with people uploading xml to sql surely? — ChrisR, Feb 26 '19 at 16:25
It's not really clear what you've tried so far. It seems to me that you should be able to entirely separate the XML part from the SQL part. Can't you just create a SqlCommand to do the insert, populating the values from the XElement? If that sounds like it would work, which bit is causing you problems? Note that you can research each part of it entirely separately. — Jon Skeet, Feb 26 '19 at 16:34

Dmitry Stepanov · Answer 1 · 2019-02-27T07:03:08.920

This SQL code can guide you how to address your task:

DECLARE @ColNamesCommaSeparated NVARCHAR(MAX)
DECLARE @ValuesCommaSeparated NVARCHAR(MAX)

DECLARE @xml XML
SET @xml = '
<ROOT>
<FIELD fieldName="ID">1234567</FIELD>
<FIELD fieldName="DateTime">05/02/2018 09:00:28</FIELD>
</ROOT>
'

;WITH Result AS
(
    SELECT
     T.n.value('@fieldName','VARCHAR(100)') AS ColName,
     T.n.value('.','VARCHAR(100)') AS Val
    FROM @xml.nodes('/ROOT/*') T(n)
)

SELECT @ColNamesCommaSeparated = 
    STUFF(
          (SELECT ', ' + ColName
          FROM Result
          FOR XML PATH ('')), 1, 1, ''),
    @ValuesCommaSeparated =
    STUFF(
          (SELECT ', ' + '''' + Val + ''''
          FROM Result
          FOR XML PATH ('')), 1, 1, '')

DECLARE @sqlCommand NVARCHAR(MAX)
SET @sqlCommand = 'INSERT INTO tblMain (' + @ColNamesCommaSeparated + ') VALUES (' + @ValuesCommaSeparated + ')'
EXEC(@sqlCommand)

So, you can create a stored procedure that accepts your xml as a passing argument. Then just execute this sp in your c# code.

How to get XElement with xml Descendants into SQL Server

1 Answers1