XQuery Made Simple

Introduction

As I understand with my development career the XQuery is very important and Lot of Developer just not using it as they don’t like the complexity of XQuery. Here in this article I am trying to make it so simple that everyone can use it.

Hope it will be interesting.

The XML String that I Used

<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common">

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

<Addresses>

<Address type="Office">

<Street>21 R Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>799008</Zip>

</Address>

<Address type="Home">

<Street>SRR Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>700016</Zip>

</Address>

</Addresses>

<Phones>

<Phone type="Mobile">8085554422</Phone>

<Phone type="Home">8085553399</Phone>

</Phones>

</Contact>

How to Learn it

Step – 1 [ Create the Base Table and Insert XML String ]

CREATE TABLE tbl_Employee

(EmpId      INT NOT NULL IDENTITY PRIMARY KEY,

 EmpDetails XML NOT NULL);

GO

INSERT INTO tbl_Employee

(EmpDetails)

VALUES('<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle></Middle>

<Last>Das</Last>

</Name>

<Name type="Common">

<First>Joy</First>

<Middle></Middle>

<Last></Last>

</Name>

</Names>

<Addresses>

<Address type="Office">

<Street>21 R Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>799008</Zip>

</Address>

<Address type="Home">

<Street>SRR Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>700016</Zip>

</Address>

</Addresses>

<Phones>

<Phone type="Mobile">8085554422</Phone>

<Phone type="Home">8085553399</Phone>

</Phones>

</Contact>');

Step – 2 [ Understanding the Value() Method ]

SELECT EmpDetails.value('(Contact/Names/Name)[1]', 'VARCHAR(50)')

       AS EmpName

FROM   tbl_Employee;

EmpName

JoydeepDas

(Contact/Names/Name)[1]

<Contact>

<Names>

<Name type="Good"> à 1

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common"> à 2

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

SELECT EmpDetails.value('(Contact/Names/Name)[2]', 'VARCHAR(50)')

       AS EmpName

FROM   tbl_Employee;

EmpName

Joy

<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common"> à 2

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

Check with others in same way.

SELECT EmpDetails.value('(Contact/Addresses/Address)[1]',

                        'VARCHAR(50)') AS Addresses

FROM   tbl_Employee;

Addresses

21 R StreetKolkataWest Bangal799008

SELECT EmpDetails.value('(Contact/Addresses/Address)[2]',

                        'VARCHAR(50)') AS Addresses

FROM   tbl_Employee;

Addresses

SRR StreetKolkataWest Bangal700016

SELECT EmpDetails.value('(Contact/Phones/Phone)[1]', 'VARCHAR(50)')

       AS Phone

FROM   tbl_Employee;

Phone

8085554422

SELECT EmpDetails.value('(Contact/Phones/Phone)[2]', 'VARCHAR(50)')

       AS Phone

FROM   tbl_Employee;

Phone

8085553399

SELECT EmpDetails.value('(Contact/Names/Name/First)[1]',

                        'VARCHAR(50)') AS EmpName,

       EmpDetails.value('(Contact/Names/Name/Middle)[1]',

                        'VARCHAR(50)') AS MiddleName,

       EmpDetails.value('(Contact/Names/Name/Last)[1]',

                        'VARCHAR(50)') AS LastName

FROM   tbl_Employee;

EmpName      MiddleName        LastName

Joydeep                                          Das

SELECT EmpDetails.value('(Contact/Names/Name/First)[2]',

                        'VARCHAR(50)') AS EmpName,

       EmpDetails.value('(Contact/Names/Name/Middle)[2]',

                        'VARCHAR(50)') AS MiddleName,

       EmpDetails.value('(Contact/Names/Name/Last)[2]',

                        'VARCHAR(50)') AS LastName

FROM   tbl_Employee;

EmpName      MiddleName      LastName

Joy

Step – 3 [ Understanding QUERY() Method ]

It not returns the Values but returns the XML portion of string.

SELECT EmpDetails.query('Contact/Names/Name[@type="Good"]/First')

       AS EmpName,

       EmpDetails.query('Contact/Names/Name[@type="Good"]/Middle')

       AS MiddleName,

       EmpDetails.query('Contact/Names/Name[@type="Good"]/Last')

       AS LastName

FROM   tbl_Employee;

EmpName                        MiddleName     LastName

<First>Joydeep</First>  <Middle />          <Last>Das</Last>

Contact/Names/Name[@type="Good"]/…

<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common">

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

SELECT EmpDetails.query('Contact/Names/Name[@type="Common"]/First')

  AS EmpName,

       EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle')

       AS MiddleName,

       EmpDetails.query('Contact/Names/Name[@type="Common"]/Last')

       AS LastName

FROM   tbl_Employee;

EmpName                 MiddleName      LastName

<First>Joy</First>    <Middle />          <Last />

Step – 4 [ Understanding Text() Method ]

SELECT CONVERT(VARCHAR(50),  EmpDetails.query('Contact/Names/Name[@type="Good"]/First/text()')) 

AS EmpName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Good"]/Middle/text()')) 

AS MiddleName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Good"]/Last/text()')) 

AS LastName

FROM tbl_Employee;

EmpName            MiddleName           LastName

Joydeep Das

SELECT CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/First/text()')) AS EmpName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle/text()')) AS MiddleName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Last/text()')) AS LastName

FROM tbl_Employee;

EmpName          MiddleName         LastName

Joy

Step – 5 [ Understanding Exist() Method ]

SELECT 

CASE WHEN EmpDetails.exist('Contact/Names/Name[@type="Common"]/First') = 1

THEN

CONVERT(VARCHAR(50),        EmpDetails.query('Contact/Names/Name[@type="Common"]/First/text()'))

ELSE 'Not Found' END AS EmpName,

CASE WHEN 

EmpDetails.exist('Contact/Names/Name[@type="Common"]/Middle') = 1

THEN

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle/text()'))

ELSE'Not Found ' END AS MiddleName,

CASE WHEN EmpDetails.exist('Contact/Names/Name[@type="Common"]/Last') = 1

THEN

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Last/text()'))

ELSE 'Not Found' END AS LastName

FROM tbl_Employee;

In our case we not find any ‘Not Found’ as the Tag Exists.

EmpName    MiddleName      LastName

Joy

Hope you like it.

Posted by: MR. JOYDEEP DAS

XQuery

XQuery is a very important part of SQL Server and it is introduced from SQL Server 2005. In this article I am trying to explain the very useful features of SQL Server XQuery. Hope it will be interesting.

T-SQL supports the subset of XQuery language that is used for querying the xml data type. XQuery is a language that can query structured or semi-structured XML data.  With the xml data type support provided in the database engine, documents can be stored in a database and then queried by using XQuery.

To query an XML instance stored in a variable or column of xml type, we use the xml data type methods.

For example we can declare a variable of xml type and query it by using the query () method of the xml data type.

Example:

DECLARE @x XML

SET @x = '<ROOT><a>111</a></ROOT>'

SELECT @x.query('/ROOT/a') AS ResultSet

ResumtSet

---------

<a>111</a>

SQL Server 2005 XQuery Function

The following 4 functions are the XQuery function available in SQL Server 2005.

xml.exist

This methods returns a Boolean values based upon a search expression on an XML node.

SELECT @x.exist('/christmaslist/person[@gift = "socks"]')

SELECT @x. exist ('/christmaslist/zach')

SELECT @x.exist('/christmaslist/person[@gift = "Socks"]')

xml.value

This method accepts XQuery statements and returns a single value.

SELECT @x.value('/christmaslist[1]/person[1]/@name', 'VARCHAR(20)')

SELECT @x.value('/christmaslist[1]/person[2]/@name', 'VARCHAR(20)')

xml.query

This method accepts an XQuery statement and returns an instance of the XML data type.

SELECT @x.query('/christmaslist/person')

Which will returns the xml documents

<person name="betty" gift="camera" />

 <person name="zach" gift="elmo doll" />

 <person name="brad" gift="socks" />

xml.nodes

This method is very useful when we need to shred the data from an XML data type variable into relational data. This method accepts an XQuery statement as a parameter and returns a row set that contains logical scalar data from the XML variable.

This following example is from a rules engine I am creating. The XML itself represents a rule, and the XQuery below parses the rule into a table. The beauty of the XML in this case is the extensible and the portability. Meaning, I do not have to define all the elements if I don’t want to, or, I could add additional elements with ease. Then they are also easy to transfer because XML is a relational model in itself.

The XML File

<rule id="100100001">

  <conditions>

    <filter type="and">

      <condition module="person" attribute="age" operator="gt">

        <value>35</value>

      </condition>

      <condition module="transportation" attribute="automobile" operator="neq">

        <value>truck</value>

      </condition>

      <condition module="family" attribute="spouse" operator="eq">

        <value>wife</value>

        <value>child</value>

      </condition>

      <condition module="job" attribute="description" operator="eq">

        <value>receptionist</value>

        <value>sales</value>

      </condition>

    </filter>

  </conditions>

</rule>

Now How I Process It

DECLARE @RuleXML XML

SET @RuleXML =

'<rule id="100100001">

  <conditions>

    <filter type="and">

      <condition module="person" attribute="age" operator="gt">

        <value>35</value>

      </condition>

      <condition module="transportation" attribute="automobile" operator="neq">

        <value>truck</value>

      </condition>

      <condition module="family" attribute="spouse" operator="eq">

        <value>wife</value>

        <value>child</value>

      </condition>

      <condition module="job" attribute="description" operator="eq">

        <value>receptionist</value>

        <value>sales</value>

      </condition>

    </filter>

  </conditions>

</rule>'

SELECT

      Attribute   = N.c.value('(../@attribute)', 'nvarchar(4000)')

      ,Value            = N.c.value('(.)', 'nvarchar(4000)')

      ,Operator   = N.c.value('(../@operator)', 'nvarchar(4000)')

FROM @RuleXML.nodes('/rule/conditions/filter/condition/value') AS N(c)

WHERE N.c.value('(.)', 'nvarchar(4000)') != ''

Output

Attribute         Value             Operator

---------         ------            ---------

age               35                gt

automobile        truck             neq

spouse            wife              eq

spouse            child             eq

description       receptionist      eq

description       sales             eq

Running this self-contained example, we see that we return a table of Attribute, Values, and Operators. These would then be used in order to further output a result set for the rule.

The most important line above is the FROM clause. It defines the path for the actual values we are returning. These values can have multiple rows of output, and in order to return these multiple rows, you need this line to drill down to the most granular element. From there, look at the select list. The select list contains operators that traverse the path upwards. We’ll notice this with the ../ which is the same as saying “go back one directory”. From here you can return the attributes by precluding them with the at (@) symbol.

Hope you like it.

Posted by: MR. JOYDEEP DAS