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.