xml是什么入参格式 存储过程_存储过程：通过XML作为参数，并将（键/值对）

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?

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

[

0: [key, value],

1: [key, value],

2: [key, value]

]

解决方案

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 = '

'

/* Call the stored procedure with the XML Parameter */

exec ParseXML @InputXML = @XMLParam

/* Clean up - Drop the procedure */

drop procedure ParseXML

go