XML Data Type Methods page 2

The query() Method

This method basically needs an XPath expression in the XQuery parameter and returns an XML data type.

The XPath expression '/albums/album[2]/labels/label[1]' specifies that we want to get the first label of the second album for every single artist.

The query() method returns the XML fragment containing everything between (and including) the beginning and ending tags of that "label" element.

  1. SELECT name, xmlData.query('/albums/album[2]/labels/label[1]') AS SecondAlbumLabel
  2. FROM dbo.tArtist;

name SecondAlbumLabel
Radiohead <label>Parlophone</label>
Guns N Roses <label>Geffen Records</label>

Let's consider another example. We need to find albums that have word "record" in description.

  1. SELECT name,
  2. xmlData.query ('/albums/album[description[contains(., "record")]]') AS ContainsRecord
  3. FROM dbo.tArtist;

XPath expression in detail can be described with the following phrase: find in list of albums (albums) such an album (album), which has a description (description), that contains word "record" (contains(., "record")). A dot in function contains() refers to the current element, in this case it is description element.

name ContainsRecord
Guns N Roses <album title="Use Your Illusion I">...</album>

We see that such an album is found only among Guns N' Roses' albums. But Radiohead has in a description of OK Computer album word "Records". This album wasn't found because XQuery is case-sensitive. To make this album be also found we'll use lower-case() function that brings the processed text to lowercase.

  1. SELECT name,
  2. xmlData.query ('/albums/album[description[contains(lower-case(.), "record")]]')
  3. AS ContainsRecord
  4. FROM dbo.tArtist;

name ContainsRecord
Radiohead <album title="OK Computer">...</album>
Guns N Roses <album title="Use Your Illusion I">...</album>

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.