07
DecSQL Server XQuery Methods
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.
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
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.