XQuery in SQL Server, some examples


 


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)


Comments (4)

  1. Sudip says:

    Good to learn. Nice concise document.

  2. no_spacing says:

    ffs remove the double spacing from a codebox…

  3. Good one,Could have added more & different examples explaining different scenarios 🙂

  4. jlgervais says:

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