XML and XQuery in SQL Server

Lakhwinder.Ghuman
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 3186 red flag

XML Data type one of the important data types available in SQL Server, offers to declare variable and table columns to store data in the XML format, comes up with 5 main methods to use with the XQuery in SQL Server.

Introduction

The XML data type is one of the important data types available in the SQL Server and was introduced in SQL Server 2005. The XML data type allows us to store xml documents in SQL Server. We can create variables and columns of this data type.

Objective

The main objective of this article is to explore the use of XML data type and various methods available for XQuery in the SQL Server.

Explanation:

There are basically five main methods which we can use the XQuery or xml data type. These methods are:

1.       The exist() method

2.       The query() method

3.       The value() method

4.       The nodes() method

5.       The modify() method

1.       The exist() method: This method is used to check a specific value exist in the xml instance or not. It returns Boolean value, 1 if exists and o if not. Let us understand the use of this method with the help of following example:

----XML
and XQuery in SQL Server
Declare @xmlRecord XML
SET @xmlRecord= '<Students>
                                        <Student RegNo="123" Name="ABC">
                                                        <Course CourseCode="CAP101" CourseName="Introduction to IT"></Course>
                                                        <Course CourseCode="CAP102" CourseName="Introduction to OS"></Course>
                                        </Student>
                                        <Student RegNo="456" Name="XYZ">
                                                        <Course CourseCode="CAP101" CourseName="Introduction to IT"></Course>
                                                        <Course CourseCode="CAP102" CourseName="Introduction to OS"></Course>
                                        </Student>
                                                        <Student RegNo="789" Name="PQR">
                                                        <Course CourseCode="CAP103" CourseName="Programming in C"></Course>
                                        </Student>
 
                        </Students>';
SELECT @xmlRecord
SELECT @xmlRecord.exist('/Students/Student[@Name="XYZ"]') as IsExist
SELECT @xmlRecord.exist('/Students/Student[@Name="MNO"]') as IsExist



Result:

2.       The query() method: This method is used to extract an instance of XML data type. Let us understand the use of this method with the help of following query:

(in continuous to above declared xml variable)

SELECT @xmlRecord.query('/Students/Student/Course') as Courses
SELECT @xmlRecord.query('distinct-values(data(/Students/Student/Course/@CourseName))') as Courses
SELECT @xmlRecord.query('distinct-values( data(/Students[1]/Student[1]/Course[2]/@CourseName))') as CourseName

Result:

3.       The value() method: This method is used to fetch single value  as:

SELECT @xmlRecord.value('/Students[1]/Student[2]/@Name', 'VARCHAR(50)') as StudentName

Result:


4.       The nodes() method:  This method is used to extract multiple rows. Lets understand with example:

SELECT x.value('../@RegNo', 'int') AS RegNo, x.value('../@Name', 'varchar(50)') AS Name, x.value('@CourseCode', 'varchar(50)') AS CourseCode FROM @xmlRecord.nodes('/Students/Student/Course') TempXML (x)

Result:



5      The modify() method: This method is used to update/ modify the xml data, for example suppose we want to replace the name of first student in the above data then the query will look like as:

DECLARE @StudentName varchar(50) ='John'
SET @xmlRecord.modify ('replace value of (/Students/Student/@Name)[1] with sql:variable("@StudentName")')
SELECT @xmlRecord
Result:

As the query methods offer many advantages to manipulate and work with xml data but there are some limitations also. The main are:

i.)                   The maximum storage limit for XML data type is 2GB.

ii.)                  Xml data type does not allow type casting to text and ntext types.

iii.)                Xml data type variable can be compared and sorted.

iv.)                Xml type column cannot be used as key column in an index.

v.)                 Not possible to pass as parameter to scalar function except ISNULL,COALESCE, and DATALENGTH.



Page copy protected against web site content infringement by Copyscape

About the Author

Lakhwinder.Ghuman
Full Name: Lakhwinder Ghuman
Member Level: Starter
Member Status: Member
Member Since: 5/4/2011 7:09:31 AM
Country: India
Best Regards Lakhwinder Ghuman
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)