Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
SQL Server XQuery Methods

SQL Server XQuery Methods

18 Mar 2024
Advanced
8.93K Views
9 min read

SQL Server XQuery Methods: An Overview

SQL Server provides XQuery methods to query XML files or XML data, enabling operations like Insertion, Updating, and Deletion within XML files or XML Data Type variables. It's important to note that XQuery statements in SQL Server are case-sensitive due to XML's inherent case sensitivity, so attention to the case is crucial when querying XML data. For further understanding, consider exploring resources such as a SQL Server Tutorial or enrolling in a SQL Server Certification Course.

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.

XQuery Methods

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:

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.

FAQs

Q1. What is XQuery?

XQuery is a language for querying XML data. XQuery for XML is similar to SQL for databases. XQuery is based on XPath expressions. All major databases support XQuery.

Q2. What's the difference between XPath & XQuery?

XPath is an xml path language that allows you to pick nodes from an xml document using queries. XQuery is used to extract and manipulate data from xml documents, relational databases, and Microsoft Office documents that have an xml data source.

Q3. When to use XQuery?

Use XQuery when you need constructors or conditional logic. XQuery code is more readable than code that constructs documents using XPath's XMLDOCUMENT, XMLELEMENT, and XMLATTRIBUTES functions.

Q4. What type of file is XQuery?

XQuery is a query language for XML. XQuery is most commonly used for XML publishing, which generates XML for Web communications, dynamic websites, and publishing apps. The original data may be stored in XML files or in a relational database.

Take our free sqlserver skill challenge to evaluate your skill

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

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this