XQuery Lab 1

XQuery is a database programming language developed for filtering and manipulating XML BLOB data.  It can be especially useful for ad-hoc reporting.  In this post, I want to show a few examples of some very basic XQuery expressions against untyped XML data.

For our examples, we’ll need sample data.  Let’s create a simple table to hold XML data:

CREATE TABLE Avengers (ID INT IDENTITY, AdditionalInfo XML) 
GO

Now let’s populate the table with a few records:

INSERT INTO Avengers VALUES('
 <Person>
 <Names>
 <Name>
 <First>Tony</First>
 <Last>Stark</Last>
 </Name>
 </Names>
 <Aliases>
 <Alias type="Primary">Iron Man</Alias>
 <Alias type="Other">Shellhead</Alias>
 <Alias type="Other">Golden Avenger</Alias>
 </Aliases>
 </Person>'),
 ('
 <Person>
 <Names>
 <Name>
 <First>Bruce</First>
 <Last>Banner</Last>
 </Name>
 </Names>
 <Aliases>
 <Alias type="Primary">Hulk</Alias>
 <Alias type="Other">Joe Fixit</Alias>
 <Alias type="Other">Green King</Alias>
 <Alias type="Other">Green Goliath</Alias>
 <Alias type="Other">Holku</Alias>
 </Aliases>
 </Person>'),
 ('
 <Person> 
 <Names> 
 <Name> 
 <First>Thor</First>
 <Last>Odinson</Last> 
 </Name>
 </Names> 
 <Aliases>
 <Alias type="Primary">Thor</Alias>
 <Alias type="Other">Donald Blake</Alias>
 <Alias type="Other">Sigurd Jarlson</Alias>
 <Alias type="Other">Jake Olsen</Alias>
 </Aliases>
 </Person> 
 ')
GO

Upon running a SELECT * FROM Avengers, we see the contents of the new table:

SELECT * FROM Avengers

You can see that we have 3 records of XML BLOBs.  To perform a simple query, let’s pull the primary Alias for each record:

SELECT AdditionalInfo.query('/Person/Aliases/Alias[@type="Primary"]') 
AS PrimaryAlias 
FROM Avengers
GO

We’ve used the query() XQuery method to do this – it gives us what we want, but we could make this more meaningful by associating the Person’s Name with the Alias, and also remove the XML tags:

SELECT AdditionalInfo.query('/Person/Aliases/Alias[@type="Primary"]/text()')  
AS PrimaryAlias, 
AdditionalInfo.value('(/Person/Names/Name/Last)[1]','VARCHAR(25)') 
AS LastName 
FROM Avengers
GO

This time we’ve added the use of the text() method to remove the XML tags from the PrimaryAlias results, and introduced the value() method to obtain the LastName values – we don’t require the query method for LastName.  The value() method requires a second paramter – data type.  We used VARCHAR, and that effectively converted the result’s XML type to VARCHAR.  We could also convert the PrimaryAlias result values to VARCHAR by wrapping the query in a CONVERT function call.

More XQuery to come!

[XQuery Lab 2]

About these ads

Posted on May 2, 2012, in SQL Server. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: