How to use XML Data Type in SQL Server 2005

Goldytech
Posted by in Sql Server category on for Intermediate level | Views : 6775 red flag
Rating: 4 out of 5  
 1 vote(s)

This post describes how to use XML DataType and shows varrious methods to query it.

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 2005 to XML enables developers to query the data using Xquery and Xpath expressions.
Page copy protected against web site content infringement by Copyscape

About the Author

Goldytech
Full Name: Muhammad Afzal Qureshi
Member Level: Bronze
Member Status: Member
Member Since: 8/4/2009 10:58:17 PM
Country: India

http://goldytech.wordpress.com
Hello Everyone Myself Muhammad Afzal , aka GoldyTech. Thats my pen name. I reside in India and work as a solution Architect on .NET platform. I hope you must have enjoyed reading my blog. Please leave your comments or suggestions good or bad and help me to improve

Login to vote for this post.

Comments or Responses

Posted by: Pandians on: 8/30/2009
Hi
Nice example.
Regards
Posted by: Vipinpaliwal on: 8/31/2009
Hi,

Very nice example.

Login to post response

Comment using Facebook(Author doesn't get notification)