SQL: xPath and xQuery

xPath and xQuery allow SQL to retrieve data from an xml source.



--XML xpath example
DECLARE @xml XML


SET @xml = N'



Gambardella, Matthew

Computer

44.95
2000-10-01
An in-depth look at creating applications
with XML.



Ralls, Kim
Fantasy
5.95
2000-12-16
A former architect battles corporate zombies,
an evil sorceress, and her own childhood to become queen
of the world.

';

SELECT x.books.value('@id[1]','varchar(10)') AS bookid
,x.books.value('title[1]','varchar(50)') AS bookName
,x.books.value('author[1]','varchar(50)') AS [BookAuth]
,x.books.value('description[1]','varchar(500)') AS [Desc]
,x.books.value('price[1]','money') as price
,x.books.value('publish_date[1]','datetime') as datePublished
FROM @xml.nodes('//catalog/book[price > 5]') as x(books)
where x.books.value('price[1]','money') > 6
order by bookid

--xQuery
SELECT @xml.query('
for $book in //catalog/book
where $book/price > 5
order by $book/title[1]
return $book')


SELECT @xml.query('
for $book in //catalog/book
where $book/price > 5
order by $book/title[1]
return
element Book
{(
element Title { data($book/title)},
element Author { data($book/author)}
)}
')


SELECT @xml.query('
for $book in //catalog/book
where $book/price > 5
order by $book/title[1]
return
element Book
{(
attribute Title { data($book/title)},
attribute Author { data($book/author)}
)}
')