Update XML attribute value based on condition with XQUERY

Update XML attribute value based on condition with XQUERY

Recently I had one article which shows how to update XML attribute value which doesn’t had any checking/condition while update. One of the reader asked me how can he update value based on condition. Here is the solution for it.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Dept=”VOC” Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Dept=”VOC” Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Dept=”VOC” Name=”PCB” /></TestDetails>’

GO

 

Now we will change attribute “Dept”’s value from “VOC” to appropriate value.

-- updating new attribute
UPDATE Orders
SET TestDetails.modify('
replace value of (/TestDetails/Test/@Dept)[1]
with (
if (/TestDetails/Test/@Name="SVOC Full") then
"SVOC"
else
"VOC"
)
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new valie in attribute “Dept” added inside “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • Update XML attribute value with XQUERY in SQL Server (Click Here)
  • XQuery in SQL Server to insert XML attribute in existing XML element (Click Here)
  • XQuery in SQL Server to delete XML element (Click Here)
  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

One thought on “Update XML attribute value based on condition with XQUERY

Comments are closed.