XQuery in SQL Server, some examples

XQuery in SQL Server, some examples

Rate This
  • Comments 4

 

I got an XQuery in SQL Server related question from a colleague the other day. I thought I had the answer, but it showed that I forgot most of it.

And of course I hadn’t written down my examples or tests from previous encounters regarding this topic.

 

So this is not intended to be an explanation of XQuery in SQL Server, just a quick reference that can be used in the future, and it will be expanded when there is a need.

 

The examples should be pretty self-explanatory.

 

declare @xml xml

set @xml =

'<RecordStore>

               <Album id="1" category="Rock">

                             <Artist>Rockers Utd.</Artist>

                             <Title>Rock Until You Drop</Title>

                             <ReleaseYear>2005</ReleaseYear>

                             <Price>10</Price>

               </Album>

               <Album id="2" category="Oldies">

                             <Artist>Oldies Inc.</Artist>

                             <Title>Rock Like There Was A Tomorrow</Title>

                             <ReleaseYear>1960</ReleaseYear>

                             <Price>5</Price>

               </Album>

               <Album id="3" category="Rock">

                             <Artist>ShockRockers</Artist>

                             <Title>ShockRock</Title>

                             <ReleaseYear>2000</ReleaseYear>

                             <Price>12</Price>

               </Album>

               <Album id="4" category="Dance">

                             <Artist>Swing Its</Artist>

                             <Title>Shake and Bake</Title>

                             <ReleaseYear>2005</ReleaseYear>

                             <Price>15</Price>

               </Album>

</RecordStore>'

 

-- Using @ signifies that we query on attribute, no @ means value of node

 

-- Get all album titles in Category = "Rock"

select @xml.query('/RecordStore/Album[@category="Rock"]/Title')

-- Get all artists that released albums in 2005

select @xml.query('/RecordStore/Album[ReleaseYear=2005]/Artist')

-- Get the price for the Album called ‘ShockRock’

select @xml.query('/RecordStore/Album[Title="ShockRock"]/Price')

-- Get all albums titles with a price higher than 10

select @xml.query('/RecordStore/Album[Price>10]/Title')

-- Get the titles for the two first albums in list

select @xml.query('/RecordStore/Album[position()<=2]/Title')

 

-- Note that the above examples returns the nodes as XML, if you wish to get

-- hold of a singular value, then use value() method instead, this returns a scalar so it needs a datatype as the second argument.

 

-- Get the price for the Album called ShockRock

select @xml.value('(/RecordStore/Album[Title="ShockRock"]/ReleaseYear)[1]', 'int')

-- Get the title for the album with id 4

select @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)')

 

Running this should produce the following results

 

RockAlbums

--------------------------------------------------------------------------

<Title>Rock Until You Drop</Title><Title>ShockRock</Title>

 

(1 row(s) affected)

 

ReleasesIn2005

--------------------------------------------------------------------------

<Artist>Rockers Utd.</Artist><Artist>Swing Its</Artist>

 

(1 row(s) affected)

 

AlbumPrice

-------------------------------------------------------------------------

<Price>12</Price>

 

(1 row(s) affected)

 

PriceAbove

------------------------------------------------------------------------

<Title>ShockRock</Title><Title>Shake and Bake</Title>

 

(1 row(s) affected)

 

FirstTwoAlbums

------------------------------------------------------------------------

<Title>Rock Until You Drop</Title><Title>Rock Like There Was A Tomorrow</Title>

 

(1 row(s) affected)

 

AlbumPrice

------------------------------------------------------------------------

2000

 

(1 row(s) affected)

 

AlbumByID

------------------------------------------------------------------------

Shake and Bake

 

(1 row(s) affected)

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • Good to learn. Nice concise document.

  • ffs remove the double spacing from a codebox...

  • Good one,Could have added more & different examples explaining different scenarios :)

  • Straight to the point, no b*llsh**, thanks !

Page 1 of 1 (4 items)