Insert from xml to sql server table

Question

I have the following XML file:

<test>
    <test2>
        <A>206942</A>
    </test2>
    <test2>
        <A>203405</A>
    </test2>
</test>

I need to insert into a SQL Server table:

XmlNodeList dataNodes = xmlDoc.SelectNodes("/test/test2");

SqlConnection dbConnection = new SqlConnection(connectionString);
try
{
    dbConnection.Open();

    foreach (XmlNode node in dataNodes)
    {
        String A = (node.SelectSingleNode("A") != null) ?
          node.SelectSingleNode("A").InnerText.ToString() : string.Empty;
        try
        {
            using (SqlCommand cmd = dbConnection.CreateCommand())
            {
                cmd.Parameters.AddWithValue(" @A", A);
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

I try the code above and I always get the following error:

Incorrect syntax near @A Must declare the scalar variable @A

How do I resolve this?

What is the SQL you are trying to run? There is no actual CommandText being set for the SqlCommand. Also, you have a space to the left of the @ in the AddWithValue. — Solomon Rutzky
– Solomon Rutzky, Commented Sep 16, 2014 at 14:51
You might also take a look at this. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Sean Lange
– Sean Lange, Commented Sep 16, 2014 at 14:56
Also, how big is the XML document? Meaning, how many nodes of "/test/test2" do you expect to have? If a lot, there is an option that would be rather efficient and not require passing the XML to SQL Server to be parsed. — Solomon Rutzky
– Solomon Rutzky, Commented Sep 16, 2014 at 15:05

Tanner · Accepted Answer · 2014-09-16 15:02:29Z

It would probably be simpler to pass the XML to SQL Server and handle it from there.

You would just need a stored procedure that accepted the xml value as a parameter:

CREATE PROCEDURE [InsertXMLValuesToMyTable]
    @XML xml
AS 
BEGIN...

Within the stored proc you can manipulate the xml to extract the values and perform an INSERT, something like below.

Demo SQL Fiddle

DECLARE @XML xml = 
    '<test>
        <test2>
            <A>206942</A>
        </test2>
        <test2>
            <A>203405</A>
        </test2>
    </test>';

-- this will convert the values into rows and add them to a temp table
SELECT T.r.value('.','int') IdsToInsert
INTO #TMP 
FROM @XML.nodes('test/test2/A') as T(r) 

-- mock object to insert into 
CREATE TABLE #TMP2 (ID int)

-- insert values from the first table into your destination table
INSERT INTO #TMP2
SELECT IdsToInsert 
FROM #TMP

-- show the output
SELECT * 
FROM #TMP2

DROP TABLE #TMP
DROP TABLE #TMP2

Collectives™ on Stack Overflow

Insert from xml to sql server table

1 Answer 1

Demo SQL Fiddle

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related