Change XML attribute values in SQL Server XML column

Question

I have a SQL Server database table like the following:-

Id (int)       Info(xml)
------------------------
1       <xml....
2       <xml ...
3       <xml ...

The XML in each record in the Info field is something like:-

<CodesParameter xmlns="http://mynamespace.com/" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Data Code="A1" HasMoreData="true">
 .
 .
</Data>
</CodesParameter>

I would like to be able to change the value of the <Data Code="..."> within the XML and have tried the following query:-

UPDATE MyTable
SET Info.modify('replace value of (Data[1]/@Code)with ("B1")')
WHERE Id = 2
Go

The message backs says that the query has executed successfully, but the value does not change.

I have tried declaring the namespace:-

UPDATE MyTable
SET Info.modify('declare namespace myns="http://mynamespace.com/";
replace value of (/myns:WebTableParameter[1]/myns:DataTable[1]/@Code)with ("B1")')
WHERE Id = 2
Go

But the same result - the message says the query has executed successfully, but nothing has changed.

Any help appreciated.

marc_s · Accepted Answer · 2014-02-21 17:01:02Z

2

You have several problems:

you're totally ignoring the XML namespace on the XML document in question (in your original UPDATE)
you're using an incomplete XPath expression for your modify

Try this statement:

;WITH XMLNamespaces(DEFAULT 'http://mynamespace.com/')
UPDATE dbo.MyTable
SET Info.modify('replace value of (/CodesParameter/Data[1]/@Code)[1] with ("B1")')
WHERE Id = 1

Basically, you need to start at the root of the XML document - you've missed (or left out) the <CodesParameter> node entirely. Also: you need to select a single attribute - thus you need another [1] after your parenthesis.

With this statement, I was able to update the XML as desired.

answered Feb 21, 2014 at 17:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

fbc Over a year ago

Thanks very much, marc, this worked for me. It was a typo missing out the <CodesParameter> node, but the extra [1] I hadn't considered.

Collectives™ on Stack Overflow

Change XML attribute values in SQL Server XML column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related