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>’
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) 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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.