Заметки о типах данных

Метод nodes()

Разбивает XML-структуру на одно или несколько поддеревьев, в соответствии с указанным выражением XPath.

Выполним следующий запрос:

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

Данный запрос разобьет исходную структуру на строки, по количеству элементов "album" и вернет по две строки для каждого исполнителя:

Разберем текст запроса подробнее.

CROSS APPLY A.xmlData.nodes('/albums[1]/album') – разбивает каждую строку таблицы на столько строк, сколько элементов "album" было найдено.

col – это имя производной таблицы, а album – это имя столбца. Они нужны будут для дальнейшей работы с результатами.

album.query('.') – здесь осуществляется запрос к каждой строке результатов, при помощи псевдонима. Данный подзапрос просто выбирает все данные из поддерева.

Разберём другой пример. Допустим мы хотим получить в виде таблицы первые две песни каждого альбома для каждой из групп.

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] – указывает, что нам нужны только два первых элемента "song" внутри каждого элемента "album".

'../@title' – обращается к родительскому элементу и берёт его атрибут @title.

Результат отобразился корректно, но при больших объёмах данных такой запрос будет крайне медленным и неэффективным. Причина заключается в том, что для каждой песни среда выполнения запроса ищет родительский элемент и считывает его атрибут. Перепишем запрос следующим образом:

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);

Результат будет аналогичным, однако теперь логика запроса изменена следующим образом: сначала выбираются все альбомы, а потом для каждого альбома будут присоединяться песни.

Поскольку песен всегда будет намного больше, чем альбомов, выполнение данного запроса покажет существенный прирост производительности по сравнению с предыдущим.