Notes about data types

The nodes() Method

Breaks down XML structure on one or many subtrees according to XPath expression provided.

Let's run the following query:

SELECT name AS artist
    , album.query('.') AS album
FROM dbo.tArtist A
CROSS APPLY A.xmlData.nodes('/albums[1]/album')col(album);

This query breaks down the original structure on rows by quantity of "album" elements and returns to rows for each artist:

Let's examine this query in detail.

CROSS APPLY A.xmlData.nodes('/albums[1]/album') – breaks down every table row by quantity of "album" elements found.

col is a name of derived table and album is a name of a column. They will be needed for further work with results.

album.query('.') – this is a query to every single row of results using an alias. This subquery just takes the whole subtree.

Let's examine another example. Assume that we need to get first two songs from every album for every artist in a tabular form.

SELECT name AS artist
    , song.value('../@title', 'varchar(50)') AS album
    , song.value('@title', 'varchar(100)') AS song
FROM dbo.tArtist A
CROSS APPLY A.xmlData.nodes('/albums[1]/album/song[position()<=2]')col(song);

[position()<=2] – specifies that we need only two first elements "song" inside every "album" element.

'../@title' – refers to a parent element and takes it's attribute @title.

Result set is correct but in case of big data such query would be extremely slow and ineffective. Problem is that for every song the runtime searches for the parent element and reads it's attribute. Let's rewrite the query in a following way:

SELECT name AS artist
    , album.value('@title', 'varchar(50)') AS album
    , song.value('@title', 'varchar(100)') AS song
FROM dbo.tArtist A
CROSS APPLY A.xmlData.nodes('/albums[1]/album')c1(album)
CROSS APPLY c1.album.nodes('song[position()<=2]')c2(song);

Result set would be the same, but now query selects all the albums first and then for each of them searches their songs.

Since quantity of songs would always be much greater than quantity of albums, this query, this query will giva a significant performance boost.