loading..
Ðóññêèé    English
11:29

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
Tags
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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.