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.
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:
Result set will be the same:
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.
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: