Tag Archives: Insert Attribute

XQuery in SQL Server to insert XML attribute in existing XML element

XQuery in SQL Server to insert XML attribute in existing XML element

Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to insert element from XML data.

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 Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

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

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

GO

 

Each test (i.e.: Voc MS Group1, SVOC Full) has one department which is not mentioned as an attribute in TestDetails node. Let us insert “Dept” attribute.

DECLARE @Dept VARCHAR(10)
SELECT @Dept = 'Volatile'

-- Adding new attribute
UPDATE Orders
SET TestDetails.modify('
insert attribute Dept {sql:variable("@Dept")} as first into
(/TestDetails/Test)[1]
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new attribute “Dept” added in “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 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.