I'm trying to update a XML column in a SQL Server table using the
XML.modify replace value of (XML DML)
With the below XML example, is there a way I can replace all vendorId
with value 1
into another value? From the documentation in http://technet.microsoft.com/en-us/library/ms190675.aspx it looks like I need to specify the record index for that. But in my case, there would be multiple records within the xml and I would not know the order it would be in.
<LineItems>
<LineItem productId="48" invId="1573" quantity="1" id="1" vendorId="1022" price="1350.0000" cost="450.0000" discount="0" acqu="2" />
<LineItem productId="1" invId="0" quantity="1" id="2" vendorId="1" price="400" cost="0" discount="0" />
<LineItem productId="46" invId="1574" quantity="1" id="3" vendorId="1022" price="789.0000" cost="263.0000" discount="0" acqu="4" />
<LineItem productId="1" invId="0" quantity="1" id="4" vendorId="1" price="300" cost="0" discount="0" />
</LineItems>
Please advice.
Thanks!
You have to use a loop and update one value at a time.
while @XML.exist('/LineItems/LineItem[@vendorId = "1"]') = 1
set @XML.modify('replace value of (/LineItems/LineItem[@vendorId = "1"]/@vendorId)[1] with "2"' )
A version that updates a XML column in a table would look like this.
while exists(
select * from T
where T.XMLColumn.exist('/LineItems/LineItem[@vendorId = "1"]') = 1
--and [some other condition]
)
begin
update T
set XMLColumn.modify('replace value of (/LineItems/LineItem[@vendorId = "1"]/@vendorId)[1] with "2"')
where T.XMLColumn.exist('/LineItems/LineItem[@vendorId = "1"]') = 1
--and [some other condition]
end