Notes about data types

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

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

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.

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

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.

SELECT name
    , xmlData.exist('
        /albums[1]/album/song/@length[
                                    (
                                    if (string-length(.) = 4)
                                    then xs:time(concat("00:0", .))
                                    else xs:time(concat("00:", .))
                                    )
                                    > xs:time("00:10:00")
                                ]') AS LongSongExists
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.

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.