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 CoursesSELECT @xmlRecord.query('distinct-values(data(/Students/Student/Course/@CourseName))') as CoursesSELECT @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.