CODEWITHSUNDEEP
xmlsql-server-2008-r2
codingrocks
codingrocks

Reputation: 11

Update an XML column value in sql

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?

Upvotes: 1

Views: 754

Answers (2)

marc_s
marc_s

Reputation: 754250

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!

Upvotes: 1

Himanshu
Himanshu

Reputation: 2454

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.

Upvotes: 0

Related Questions