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)