XML Data Type Methods page 4

The exist() Method

In this method you do not want any data returned from the XML stream. You just want a simple check to know whether it is there. The exist() method will check for the existence and even the value of the XPath expression you specify.

In this example you will use the exist() method to determine if a particular song is in the track listing. The song we are looking for is called "Garden of Eden".

  1. SELECT name, xmlData.exist('/albums[1]/album/song[@title="Garden of Eden"]') AS SongExists
  2. FROM dbo.tArtist;

name SongExists
Radiohead 0
Guns N' Roses 1

As you can see, song with this title does exist among Guns N' Roses' songs (1 is for 'True') and doesn't exist among Radiohead's songs (0 is for 'False').

Now let's check if there is a song that lasts more than 10 minutes.

  1. SELECT name
  2. , xmlData.exist('/albums[1]/album/song[@length>"10:00"]') AS LongSongExists
  3. FROM dbo.tArtist;

name SongExists
Radiohead 1
Guns N' Roses 1

From the result set we can see that both artists have at least one song that lasts more than 10 minutes. But that's not true. All Radiohead's songs last less than 10 minutes.

Problem is that @length attribute value has string data type by default. That is we just compare strings, not time. In order to fix this problem we can convert @length attribute value and the value which it compares with to time data type. Both of them should be brought to the "hh:mm:ss" format before converting data types. We'll use xs:time() function which takes a string as an input and returns a time as a result.

  1. SELECT name
  2. , xmlData.exist('
  3. /albums[1]/album/song/@length[
  4. (
  5. if (string-length(.) = 4)
  6. then xs:time(concat("00:0", .))
  7. else xs:time(concat("00:", .))
  8. )
  9. > xs:time("00:10:00")
  10. ]') AS LongSongExists
  11. FROM dbo.tArtist;

Now we see that Radiohead doesn't have a song that lasts more than 10 minutes, whereas Guns N' Roses have at least one.

name SongExists
Radiohead 0
Guns N' Roses 1

In this example function time() is used in xs namespace. All built-in type conversion functions have to be used in this namespace (xs:string, xs:boolean, xs:decimal, xs:float, xs:double, xs:dateTime, xs:time, xs:date etc.). Other built-in functions are being used in fn namespace but its' specification is not necessary. That means that string-length(.) and fn:string-length(.) are identical.

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