How to use XML Data Type in SQL Server 2005

INTRODUCTION

If I am asked to list down the coolest things of SQL Server 2005, with no doubt the support of XML and native XML datatype will be on top.
This datatype makes possible to use XML specific SQL to query the xml data. Moreover this datatype is available for both tables and variables. In this post I will use XQuery and Xpath expressions to query the XML Data with methods like query() and exists(). Besides querying the elements of the XML data, I will also teach you to query the attributes. So lets get started a sample XML data is shown in next section.

XML IN ACTION


DECLARE @MyXml XML
DECLARE @Exists BIT
SET @MyXml='
<Employees>
<Employee>
<Empid Status="On Vacation">1</Empid>
<FirstName>Tejas</FirstName>
<LastName>Desai</LastName>
<Age>21</Age>
</Employee>
<Employee>
<Empid Status="Active">2</Empid>
<FirstName>Manohar</FirstName>
<LastName>Pandey</LastName>
<Age>34</Age>
</Employee>
<Employee>
<Empid Status="Left">3</Empid>
<FirstName>Anis</FirstName>
<LastName>Shaikh</LastName>
<Age>45</Age>
</Employee>
</Employees>'SELECT @MyXml.query('data(/Employees/Employee/FirstName)') AS EmpFirstName --getting all the records of FirstName

SELECT @MyXML.query('data(/Employees/Employee[FirstName = "Tejas"])') -- getting record where FirstName ='Tejas'

SELECT @MyXML.query('data(/Employees/Employee[Age < 30])') --getting list of employees where Age < 30

SELECT @MyXML.query('data(/Employees/Employee/Empid[@Status="Active"])') -- querying the Status Attribute with @

SET @Exists = @MyXML.exist('/Employees/Employee/FirstName[text()="Muhammad"]') -- using exists method where xml node exists in xmldata
SELECT @Exists

CODE EXPLANATION

 The code is pretty self explanatory as it is neatly commented. It also assumes that you are familiar with XQuery Syntax. If you are not then I strongly recommend you to read this.
The code declare MyXml variable of XML datatype and then initializes this variable with sample xml data.
If you have a close look to the sample Xml data you will observe that Status is an attribute whereas Empid, FirstName, LastName and Age are the elements of the XML Data.
To query the XML attribute you must prefix the @ symbol to it. You have to trace down the path in xml data just as you do with the elements. In all queries you must have seen that I am using the root element (Employees) by name.

CLOSURE

XML has become the most obvious choice to exchange the data between heterogeneous systems and for storing the application data.
The native support of SQL Server 205 to XML enables developers to query the data using Xquery and Xpath expressions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s