19:09

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Notes about data types

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.

SELECT name, xmlData.query('/albums/album[2]/labels/label[1]') AS SecondAlbumLabel
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.

SELECT name, 
xmlData.query ('/albums/album[description[contains(., "record")]]') AS ContainsRecord
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
Radiohead
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.

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

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

Pages

Chebykin XML XQuery XPath query

Previous [Integer division]

[Data Definition Language (SQL DDL)] Next

Last added:

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
several days ago

SQL Problems and solutions

S. I. Moiseenko

Notes about data types

XML Data Type Methods page 2

The query() Method