loading..
   English
11:00

XML Data Type Methods page 3

The Value() Method

 The value() method allows you to pull out the data of a single element or a single attribute and specify it's data type.

If provided XPath expression points to multiple nodes, an error message will be thrown.

  1. SELECT name,
  2. xmlData.value('/albums/album[2]/labels/label[1]/text()', 'varchar(100)') AS SecondAlbumLabel
  3. FROM dbo.tArtist;

XQuery [dbo.tArtist.xmlData.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

This error message tells that there can be more than one sequence found with provided XPath expression. For example, in case where there are many "albums" elements or if there would be more than one "labels" element.

In order to fix the error change the XPath expression on one of the following:

  •  '/albums[1]/album[2]/labels[1]/label[1]/text()[1]'
  •  '(/albums/album[2]/labels/label/text())[1]'

Result set will be the same:

name SecondAlbumLabel
Radiohead Parlophone
Guns N Roses Geffen Records

Here is an example data type "time". Lets assume that we want to get name of the first album, it's first song name and this song's length for each artist.

  1. SELECT name
  2. , xmlData.value('/albums[1]/album[1]/@title', 'varchar(100)') AS FirstAlbum
  3. , xmlData.value('/albums[1]/album[1]/song[1]/@title', 'varchar(100)') AS FirstSongTitle
  4. , xmlData.value('/albums[1]/album[1]/song[1]/@length', 'time(0)') AS FirstSongLength
  5. FROM dbo.tArtist;

name FirstAlbum FirstSongTitle FirstSongLength
Radiohead The King of Limbs Bloom 05:15:00
Guns N' Roses Use Your Illusion I Right Next Door to Hell 03:02:00

Unfortunately the "FirstSongLength" field is recognised as hh:mm:ss, whereas we a sure that our XML document contains time in mm:ss format. To solve this problem we can use standard  T-SQL (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL syntax or we may use XQuery:

  1. SELECT name AS artist
  2. , xmlData.value('/albums[1]/album[1]/@title', 'varchar(100)') AS FirstAlbum
  3. , xmlData.value('/albums[1]/album[1]/song[1]/@title', 'varchar(100)') AS FirstSongTitle
  4. , xmlData.value('concat("00:", /albums[1]/album[1]/song[1]/@length)', 'time(0)')
  5. AS FirstSongLength
  6. FROM dbo.tArtist;

artist FirstAlbum FirstSongTitle FirstSongLength
Radiohead The King of Limbs Bloom 00:05:15
Guns N' Roses Use Your Illusion I Right Next Door to Hell 00:03:02

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