Update an XML column value in sql

Question

I have got an XML column in sql table which needs to be updated. Say the structure is as follows:

<JPS>
 <P>
  <JP>
   <IsRequired>true</IsRequired>
   <Name>Folder</Name>
   <Value>C:\Test</Value>
  </JP>
  <JP>
   <IsRequired>false</IsRequired>
   <Name>Email Addresses</Name>
   <Value>[email protected]; [email protected]</Value>      
  </JP>
</P>

I want to update the email addresses in all the XML values in all the rows in the table to a specific value. How can i achieve the same?

This is really horribly bad design.... do not concatenate together multiple values into a single XML tag! Use multiple tags - XML is very flexible that way.... otherwise you have to resort to string matching and replacements with regexes and other ugly stuff.... — marc_s
– marc_s, Commented Jul 23, 2013 at 10:30
Thanks Marc. I understand the design issue. But consider I have only one email address, but in that case how do we update. — codingrocks
– codingrocks, Commented Jul 23, 2013 at 10:32

marc_s · Accepted Answer · 2013-07-23 11:09:45Z

1

If you want to update the contents of the <Value> element of the <JP> tag that has a <Name>Email Addresses</Name> value, then you can use something like this:

;WITH XmlEmail AS
(
SELECT 
    SomeUniqueID,  // some unique/primary key ID from your table - adapt as needed!
    JPReq = XJP.value('(IsRequired)[1]', 'varchar(20)'),
    JPName = XJP.value('(Name)[1]', 'varchar(20)'),
    JPValue = XJP.value('(Value)[1]', 'varchar(20)')
FROM 
    dbo.YourTable
CROSS APPLY
    YourXmlColumn.nodes('/JPS/P/JP[Name="Email Addresses"]') AS XTbl(XJP)
)
UPDATE dbo.YourTable
SET YourXmlColumn.modify('replace value of (/JPS/P/JP[Name="Email Addresses"]/Value/text())[1] with "[email protected]"')
FROM XmlEmail xe 
WHERE dbo.YourTable.SomeUniqueID = xe.SomeUniqueID

This will update all rows, and all <JP>/<Value> nodes to the same value - is that what you're looking for?

Update: added support for checking and updating only those rows where the XML column actually does contain a <JP> tag with Email Addresses as its name - it requires that there is a primary key on your table (not sure what it is, since you didn't say anything about it) .... I've used SomeUniqueID as column name - adapt to your table as needed!

edited Jul 23, 2013 at 11:09

answered Jul 23, 2013 at 10:44

marc_s

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

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

2 Comments

codingrocks Over a year ago

after running the update, it said all 60 rows are updated. But when opening each xml column value to verify I see that it is not updated actually. Also noticed that some rows are actually missing the Email Address tag name attribute in xml. How can we check in the update statement if the Email Address name tag exists in xml rows and then update.

marc_s Over a year ago

I don't know your table structure, so it's kinda hard to suggest anything... I was trying to find out which rows contain that Email Addresses node, but in the end, I couldn't use that knowledge because I don't know how to uniquely identify a row in your table ...

Himanshu · Accepted Answer · 2013-07-23 10:28:41Z

0

You cannot do it in standard sql.

With standard sql you should read the relevant xml row one at a time, update the xml and insert it back.

answered Jul 23, 2013 at 10:28

Himanshu

2,4743 gold badges26 silver badges42 bronze badges

1 Comment

marc_s Over a year ago

You can of course do this is you're using a database system that supports XML datatypes and XML operations ...

Collectives™ on Stack Overflow

Update an XML column value in sql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related