loading..
Ðóññêèé    English
12:37
go

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)') 
  3. AS SecondAlbumLabel
  4. 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". Let’s 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
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
https://exchangesumo.com/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.