SQL: xpath and xQuery

--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)}

)}

')