Stored Procedure to insert multiple rows from XML

Question

I am attempting to write a stored procedure to insert about 1 million rows into a table from a C# app. My app writes out the rows to a XML file and I want the app to then call a stored procedure with the XML file as a parameter. I've been looking around at other similar questions but I can't seem to get them to work and none of them seem to explain their answers. My XML only has 2 columns so I believe it should be fairly simple. I am using SQL Server 2005 and ASP.NET 2.0. My XML file looks like this:

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <Test>
    <o_cus>0</o_cus>
    <o_depot>100001</o_depot>
  </Test>

So you have multiple <Test> elements inside <DocumentElement> then? — marc_s
– marc_s, Commented Nov 10, 2013 at 22:15

marc_s · Accepted Answer · 2013-11-10 22:19:24Z

1

If your XML contains multiple <Test> elements inside the <DocumentElement>, then you could use something like this:

CREATE PROCEDURE dbo.InsertData(@Input XML)
AS
BEGIN
    INSERT INTO dbo.YourTable (O_cus, O_depot)
        SELECT
            XCol.value('(o_cus)[1]', 'int'),
            XCol.value('(o_depot)[1]', 'int')
        FROM 
            @input.nodes('/DocumentElement/Test') AS XTbl(XCol)
END

Basically, you're using XPath to get a list of <Test> XML elements from your @Input XML, and then you're grabbing each fragment's o_cus and o_depot elements as int (adapt as needed) and you insert those into your table.

answered Nov 10, 2013 at 22:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored Procedure to insert multiple rows from XML

1 Answer 1

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