Get SQL Records using XQuery

XML DataType Functions:

Function	Description
Query	Gets a set of nodes from an XML document/fragment (returns XML)
Value	Gets a single value from an element or attribute of an XML document/fragment (returns scalar value)
Exist	Returns a Boolean value indicating if the XQuery expression returns values
Modify	Changes values in an XML document/fragment
Nodes	Gets a context to a node based upon the XQuery expression

The Following Query runs on AdventureWorks HumanResources.JobCandidate Table to get all Employee First Name & Last Name, Telephone Nos.

SELECT

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Name/Name.First)') FirstName,

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Name/Name.Last)') LastName,

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Address/Addr.Telephone/Telephone/Tel.Number)[1]') Telephone,

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Address/Addr.Telephone/Telephone/Tel.Number)[2]') Telephone

FROM

HumanResources.JobCandidate

OutPut:

Shai Bassli 555-0114 555-0132
Max Benson 555-0101 555-0122
Krishna Sunkammurali 555-0111 555-0198
Stephen Jiang 555-1119 555-1981
Thierry D'Hers 04 02 04 05 04
Christian Kleinerman 05 02 05 02 05
Lionel Penuchot 04 02 03 04 05
Peng Wu 555-1444
Shengda Yang 555-0101
Tai Yee 555-0114
สุทัศน คณาพล 555-0181
สามารถ เบญจศร 555-0101
ชาย บางสุขศรี 555-0114

You can Add Where Condition as follows:

SELECT

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Name/Name.First)') FirstName,

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Name/Name.Last)') LastName,

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Address/Addr.Telephone/Telephone/Tel.Number)[1]') Telephone,

Resume.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Address/Addr.Telephone/Telephone/Tel.Number)[2]') Telephone

FROM

HumanResources.JobCandidate

WHERE

Resume.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

data(/Resume/Name/Name.First)[1]','varchar(50)') = 'Shai'

Which returns only First Name is 'Shai' Record

Shai Bassli 555-0114 555-0132

Reference URL: http://msdn.microsoft.com/en-us/magazine/cc163639.aspx

Thank You.