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
There is another Article you can refer...
ReplyDeletehttp://sqlknowledgebank.blogspot.in/2012/05/xquery.html