Loading

Using Nodes() Method in Sql Server

Amit Kumar Singh

10y823

DECLARE @xmlDataHandle INT

EXEC Sp_xml_preparedocument

@xmlDataHandle output,

@XmlSourceData

UPDATE source

SET status = Cast(_xmlPayment.xmlstatus AS BIT)

FROM OPENXML(@xmlDataHandle, '/Sources/Source', 2)

WITH( xmlstatus BIT '@status',

xmlid VARCHAR(50) '@id' )_xmlPayment

WHERE source.sourceid = Cast(_xmlPayment.xmlid AS INT)

EXEC Sp_xml_removedocument

@xmlDataHandle

SELECT '1'

Convert this query using Nodes() Method of Sql Server instead of Openxml

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
If you are sending the xml document to stored procedure exclusively for this purpose and no other reason, i strongly suggest that you extract the value in business logic somewhere in C# (or whatever language you are using) and send the value itself to the stored procedure.