CODEWITHSUNDEEP
xmlsql-server-2005
user2457530
user2457530

Reputation: 17

Stored Procedure to insert multiple rows from XML

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>

Upvotes: 1

Views: 1618

Answers (1)

marc_s
marc_s

Reputation: 754518

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.

Upvotes: 1

Related Questions