XML Data Type Methods page 6

The nodes() Method

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

Let's run the following query:

  1. SELECT name AS artist
  2. , album.query('.') AS album
  3. FROM dbo.tArtist A
  4. 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:

artist album
Radiohead <album title="The King of Limbs"><labels><label>S...
Radiohead <album title="OK Computer"><labels><label>Parlop...
Guns N' Roses <album title="Use Your Illusion I"><labels><label>G...
Guns N' Roses <album title="Use Your Illusion II"><labels><label>G...

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.

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

artist album song
Radiohead The King of Limbs Bloom
Radiohead The King of Limbs Morning Mr Magpie
Radiohead OK Computer Airbag
Radiohead OK Computer Paranoid Android
Guns N' Roses Use Your Illusion I Right Next Door to Hell
Guns N' Roses Use Your Illusion I Dust N' Bones
Guns N' Roses Use Your Illusion II Civil War
Guns N' Roses Use Your Illusion II 14 Years

[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:

  1. SELECT name AS artist
  2. , album.value('@title', 'varchar(50)') AS album
  3. , song.value('@title', 'varchar(100)') AS song
  4. FROM dbo.tArtist A
  5. CROSS APPLY A.xmlData.nodes('/albums[1]/album')c1(album)
  6. 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.

Bookmark and Share
Pages 1 2 3 4 5 6
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100