Update XML attribute value with XQUERY in SQL Server
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>’
While making an initial entry of DEPT, I have provided “VOC” but now I felt I was wrong and I have to put “SVOC” instead of “VOC” and in this case I have to update existing value of “Dept” attribute. Here is the script which I would use to update attribute value without any checking/condition.
DECLARE @Dept VARCHAR(10) SELECT @Dept = 'SVOC' -- updating attribute UPDATE Orders SET TestDetails.modify(' replace value of (/TestDetails/Test/@Dept) with sql:variable("@Dept") ') 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:
- 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.