XQuery in SQL Server Examples

---
title: XQuery in SQL Server Examples
date: January 9, 2018
author: Michael Aspengren
source: https://blogs.msdn.microsoft.com/spike/2009/09/21/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.

```sql
declare @xml xml = N'
<RecordStore>
    <Album category="Rock" id="1">
        <Artist>Rockers Utd.</Artist>
        <Title>Rock Until You Drop</Title>
        <ReleaseYear>2005</ReleaseYear>
        <Price>10</Price>
    </Album>
    <Album category="Oldies" id="2">
        <Artist>Oldies Inc.</Artist>
        <Title>Rock Like There Was A Tomorrow</Title>
        <ReleaseYear>1960</ReleaseYear>
        <Price>5</Price>
    </Album>
    <Album category="Rock" id="3">
        <Artist>ShockRockers</Artist>
        <Title>ShockRock</Title>
        <ReleaseYear>2000</ReleaseYear>
        <Price>12</Price>
    </Album>
    <Album category="Dance" id="4">
        <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 data type 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)