存储过程：传递XML作为参数和INSERT（键/值对）

How would you construct and pass XML as an argument to a stored procedure on an MS SQL 2005 server? And how would you INSERT the XML into a table?

您将如何构造XML并将其作为参数传递给MS SQL 2005服务器上的存储过程？您将如何将XML插入表中？

The data is in the form of key/value pairs:

数据采用键/值对的形式：

[
    0: [key, value],
    1: [key, value],
    2: [key, value]
]

1 个解决方案

#1

Here's one example:

这是一个例子：

/* Create the stored procedure */
create procedure ParseXML (@InputXML xml)
as
begin
    declare @MyTable table (
        id int,
        value int
    )

    insert into @MyTable 
        (id, value)
        select Row.id.value('@id','int'), Row.id.value('@value','int') 
            from @InputXML.nodes('/Rows/Row') as Row(id)        

    select id, value
        from @MyTable
end
go

/* Create the XML Parameter */
declare @XMLParam xml
set @XMLParam = '<Rows>
                     <Row id="1" value="100" />
                     <Row id="2" value="200" />
                     <Row id="3" value="300" />
                 </Rows>'

/* Call the stored procedure with the XML Parameter */
exec ParseXML @InputXML = @XMLParam

/* Clean up - Drop the procedure */
drop procedure ParseXML
go