Tuesday 22 March 2016

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


1 comment:

  1. There is another Article you can refer...
    http://sqlknowledgebank.blogspot.in/2012/05/xquery.html

    ReplyDelete