Update XML attribute value with XQUERY in SQL Server

Update XML attribute value with XQUERY in SQL Server

Recently I have written article on Inserting attribute in existing XML Elements and Delete XML element. Today I am going to show how to update existing attribute value in XML elements.

[sourcecode language=”sql”]
CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO
[/sourcecode]

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

 

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.

[sourcecode language=”sql”]
DECLARE @Dept VARCHAR(10)
SELECT @Dept = ‘SVOC’

— updating attribute
UPDATE Orders
SET TestDetails.modify(‘
replace value of (/TestDetails/Test/@Dept)[1]
with sql:variable("@Dept")
‘)
GO
[/sourcecode]

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?

[sourcecode language=”sql”]
SELECT * FROM Orders
GO
[/sourcecode]

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)

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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Update XML attribute value with XQUERY in SQL Server”

Comments are closed.