03
JunSql Server provides xquery methods to query xml file or xml data. Using these methods we can Insert, Update, Delete data in xml file or in XML Data Type variable. In Sql Server XQuery statements are case sensitive since xml is case sensitive. Hence while query to xml data remember this thing.
XQuery Methods
we have following xml data to implement all the XQuery methods given below.

xml.exist()
This method returns a boolean value depends upon the condition in this method like 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
xml.query()
This method takes an XQuery statement and returns an instance of the XML data type like as
SELECT @xml.query('/Suppliers/User') as Users
SELECT @xml.query('distinct-values( data(/Suppliers/User/Item/@No))') as Items
xml.value()
This method takes an XQuery statement and returns a single value after type casting like 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
xml.nodes()
This method takes an XQuery statement and returns a single value after type casting like 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)
xml.modify()
This method takes an XQuery statement and modify the xml data like as
--Insert node in the end of XML SET @xml.modify ('insert
as last into (/Suppliers)[1]') SELECT @xml;--Update node in xml DECLARE @UserNo int =120 SET @xml.modify ('replace value of (/Suppliers/User/@UserNo)[1] with sql:variable("@UserNo")') SELECT @xml;
--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;
--Delete node in xml SET @xml.modify(' delete Suppliers/User/Item[@No=1]') SELECT @xml;
--Delete node in xml depends on condition DECLARE @ItemNo int=1 SET @xml.modify(' delete Suppliers/User/Item[@No=sql:variable("@ItemNo")]') SELECT @xml;
Summary
In this article I try to explain the Sql Server XQuery methods with example. 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, question, or comments about this article.
Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.