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),
OrderDate DATETIME DEFAULT GETDATE()
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>’
Now we will change attribute “Dept”’s value from “VOC” to appropriate value.
— updating new attribute
replace value of (/TestDetails/Test/@Dept)
if (/TestDetails/Test/@Name="SVOC Full") then
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
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.