SQL Server XQuery Methods

SQL Server XQuery Methods: An Overview

XQuery Methods in SQL Server

XQuery methods in SQL Server are functions that allow you to query and manipulate XML data stored in the database. These methods enable a variety of actions, including querying nodes, filtering data, changing XML structures, and executing transformations.

We have the following XML data to implement all the XQuery methods given below.

1. xml.exist()

This method returns a boolean value depending upon the condition in this method as

SELECT @xml.exist('/Suppliers/User[@Email = "bipul.tiwari@ymail.com"]') as Result1
SELECT @xml.exist('/Suppliers/User[@Email = "bipul.tiwari@yahoo.com"]') as Result2 

These lines of code verify if the XML variable '@xml' includes a '<User>' node with the supplied email addresses "bipul.tiwari@ymail.com" and "bipul.tiwari@yahoo.com", returning a boolean result (1 if found, 0 otherwise) in 'Result1' and 'Result2', respectively.

2. xml.query()

This method takes an XQuery statement and returns an instance of the XML data type as

SELECT @xml.query('/Suppliers/User') as Users
SELECT @xml.query('distinct-values( data(/Suppliers/User/Item/@No))') as Items

These lines retrieve XML elements that match the route '/Suppliers/User' from the XML variable '@xml' and store the results in 'Users'. The second line retrieves separate values of the 'No' property from '<Item>' elements nested within '<User>' elements in '@xml' and stores them as 'Items'.

3. xml.value()

This method takes an XQuery statement and returns a single value after typecasting as

SELECT @xml.value('/Suppliers[1]/User[1]/@Email', 'VARCHAR(20)') as ResultEmail1
SELECT @xml.value('/Suppliers[1]/User[2]/@Email', 'VARCHAR(20)') as ResultEmail2 

These lines extract the email attribute from the first <User> element under the first <Suppliers> element in the XML variable @xml. The result is stored as ResultEmail1. Similarly, the second line pulls the email attribute from the second <User> element under the first <Suppliers> section and stores it as ResultEmail2.

4. xml.nodes()

This method takes an XQuery statement and returns a single value after typecasting as

SELECT x.value('@UserNo', 'int') AS UserNo, x.value('@Email', 'varchar(50)') AS Email
FROM @xml.nodes('/Suppliers/User') TempXML (x)
SELECT x.value('../@UserNo', 'int') AS UserNo, x.value('../@Email', 'varchar(50)') AS Email, x.value('@Name', 'varchar(50)') AS ItemName
FROM @xml.nodes('/Suppliers/User/Item') TempXML (x)

The first query collects the UserNo and Email attributes from each <User> node under <Suppliers> in @xml. The second query retrieves UserNo and Email properties from the <Suppliers> parent node, as well as ItemName attributes from each <Item> node nested under <User> nodes in @xml.

5. xml.modify()

This method takes an XQuery statement and modifies the xml data as

--Insert node in the end of XML 
SET @xml.modify ('insert  as last into (/Suppliers)[1]')
SELECT @xml;

This code adds a new node at the end of the XML structure saved in the variable @xml. The changed XML is then selected and shown.

--Update node in xml
DECLARE @UserNo int =120
SET @xml.modify ('replace value of (/Suppliers/User/@UserNo)[1] with sql:variable("@UserNo")')
SELECT @xml;

This code replaces the value of the '@UserNo' attribute in the first '<User>' node under '<Suppliers>' in the XML variable '@xml' with the SQL variable '@UserNo'. The changed XML is then selected and shown.

--Update node in xml conditionally
SET @xml.modify(' replace value of (/Suppliers/User/@UserNo)[1] with ( if (count(/Suppliers/User[1]/Item) > 2) then "3.0" else "1.0" ) ')
SELECT @xml;

This code adjusts the value of the '@UserNo' attribute in the first '<User>' node under '<Suppliers>' in the XML variable '@xml' using a conditional statement. If the number of '<Item>' components under the first '<User>' node exceeds 2, the '@UserNo' is set to "3.0"; otherwise, it is set to "1.0". The changed XML is then selected and shown.

--Delete node in xml SET @xml.modify(' delete Suppliers/User/Item[@No=1]')
SELECT @xml;

This code removes the '<Item>' node with attribute 'No' equal to 1 from the '<User>' nodes within '<Suppliers>' in the XML variable '@xml'. The changed XML is then selected and shown.

--Delete node in xml depends on condition
DECLARE @ItemNo int=1
SET @xml.modify(' delete Suppliers/User/Item[@No=sql:variable("@ItemNo")]')
SELECT @xml;

This code removes the <Item> node with the provided @ItemNo attribute value from <User> nodes within <Suppliers> in the XML variable @xml, depending on the value contained in the SQL variable @ItemNo. The changed XML is then selected and shown.

Read More:

• Top SQL Server Interview Questions and Answers

Summary

In this article, I try to explain the Sql Server XQuery methods with examples. I hope after reading this article you will be able to query xml in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE