Year End Sale: Get Upto 40% OFF on Live Training! Offer Ending in
D
H
M
S
Get Now
SQL Server XQuery Methods

SQL Server XQuery Methods

18 Mar 2024
Advanced
11K Views
9 min read
Learn with an interactive course and practical 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

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.

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.

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.

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
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan, Founder and CEO of ScholarHat by DotNetTricks, is a renowned expert in System Design, Software Architecture, Azure Cloud, .NET, Angular, React, Node.js, Microservices, DevOps, and Cross-Platform Mobile App Development. His skill set extends into emerging fields like Data Science, Python, Azure AI/ML, and Generative AI, making him a well-rounded expert who bridges traditional development frameworks with cutting-edge advancements. Recognized as a Microsoft Most Valuable Professional (MVP) for an impressive 9 consecutive years (2016–2024), he has consistently demonstrated excellence in delivering impactful solutions and inspiring learners.

Shailendra’s unique, hands-on training programs and bestselling books have empowered thousands of professionals to excel in their careers and crack tough interviews. A visionary leader, he continues to revolutionize technology education with his innovative approach.
Accept cookies & close this