How do I modify a xml attribute in a xml field of a Sql Server table

Question

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!

Mikael Eriksson · Accepted Answer · 2014-01-30 13:33:39Z

1

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"' )

SQL Fiddle

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

SQL Fiddle

edited Jan 30, 2014 at 13:33

answered Jan 29, 2014 at 6:44

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ganesh Over a year ago

How would I use this on a UPDATE statement?

Ganesh Over a year ago

Thanks, @Mikael. That worked perfectly well for my situation although I'm not sure if this would be an optimized approach for frequent updates.

Backs · Accepted Answer · 2014-01-29 01:35:20Z

0

If you need to replace a constant string to another one, I advise you yo use REPLACE function and perfom it on string.

DECLARE @XML XML /*your xml value*/
SELECT REPLACE(CONVERT(NVARCHAR(MAX),@XML),'vendor="1"','"vendor="2"')

In many cases it's much easier then do it in xml-style.

answered Jan 29, 2014 at 1:35

Backs

25k5 gold badges66 silver badges92 bronze badges

Collectives™ on Stack Overflow

How do I modify a xml attribute in a xml field of a Sql Server table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related