SQL Server XQuery Methods

SQL Server XQuery Methods

18 Mar 2024
Advanced
9.94K Views
9 min read
Learn via Video Course & by Doing Hands-on Labs

SQL Server Course

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 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

Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
Generative AI For Software Developers Jul 20 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
Angular Certification Course Jul 20 SAT, SUN
Filling Fast
06:00PM to 08:00PM (IST)
Get Details
Azure Master Class Jul 20 SAT, SUN
Filling Fast
03:00PM to 05:00PM (IST)
Get Details
ASP.NET Core Certification Training Jul 28 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
Software Architecture and Design Training Jul 28 SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
.NET Solution Architect Certification Training Jul 28 SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
Azure Developer Certification Training Jul 28 SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details
Advanced Full-Stack .NET Developer Certification Training Jul 28 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
Data Structures and Algorithms Training with C# Jul 28 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
Angular Certification Course Aug 11 SAT, SUN
Filling Fast
09:30AM to 11:30AM (IST)
Get Details
ASP.NET Core Project Aug 24 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details

Can't find convenient schedule? Let us know

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