Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server XQuery Methods

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 120,933   
 

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

  1. 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 
  2. 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 
  3. 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 
  4. 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)
  5. 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.


YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
13 FEB
ASP.NET Core Development (online)

07:00 AM-09:00 AM IST( MON, WED, FRI)

Know More
25 JAN
Angular2 and Angular1 Development (online)

09:00 PM-10:30 PM IST(MON-FRI)

Know More
24 JAN
ASP.NET MVC with Angular2 Development (online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

Know More
21 JAN
Xamarin Forms : Build Cross-platform Apps (offline)

05:00 PM-06:30 PM IST

Know More
20 JAN
NODEJS & MEAN Stack 2 Development (online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (offline)

04:00 PM-05:300 PM

10 JAN
ASP.NET MVC with AngularJS Development (online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

3 JAN
ASP.NET MVC with AngularJS Development (online)

07:00 AM-09:00 AM IST

31 DEC
ASP.NET MVC with AngularJS Development (offline)

09:30 AM-11:00 AM

31 DEC
Angular2 and Angular1 Development (offline)

11:00 AM-12:30 AM

26 NOV
ASP.NET MVC with AngularJS Development (offline)

03:00 PM-04:30 PM

5 NOV
ASP.NET MVC with AngularJS Development (offline)

08:00 AM-09:30 AM

4 SEP
MEAN Stack 2 Development (offline)

11:00 AM-12:30 PM.

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+