How do I pull the value of an attribute from XML using XQUERY?

CREATE TABLE SportsEvent
(ID INT, Name NVARCHAR(20), Results XML);
GO


DECLARE @Results XML=
'<Athletics>
  <Event ID="001" Name="100m">
    <Gold>John Doe</Gold>
    <Silver>Harry Smith</Silver>
    <Bronze>Kenneth Brown</Bronze>
  </Event>
  <Event ID="002" Name="High Jump">
    <Gold>Sarah Jones</Gold>
    <Silver>Janice Johnson</Silver>
    <Bronze>Alicia Armstrong</Bronze>
  </Event>
</Athletics>'
INSERT INTO SportsEvent
VALUES(1, 'AthleticsDay', @Results);

SELECT * FROM SportsEvent;

If I want to pull out an element based on the event ID, no problem:

SELECT Results.query('(/Athletics/Event[@ID="001"]/Gold)')
FROM SportsEvent
WHERE ID = 1

I can do the same with a relative reference:

SELECT Results.query('(Athletics/Event)[1]')
FROM SportsEvent
WHERE ID = 1

But what if I want to pull the event Name based on either a relative or absolute ?:

SELECT Results.query('(Athletics/Event[@Name])[@ID="001"]')
FROM SportsEvent
WHERE ID = 1


SELECT Results.query('(Athletics/Event[@Name])[1]')
FROM SportsEvent
WHERE ID = 1

...both bring back ALL the data for that event.

I tried using the value method:

SELECT Results.value('(/Athletics/Event/@Name)[1]','VARCHAR(20)')
FROM SportsEvent
WHERE ID = 1

...but this only works for a relative reference i.e in this case the first set of results in the XML.

What if I want to specify an event ID and return just the event name (either as an XML fragment or as data/value)?

Answers


Ok, so for some (many?) this will seem blindingly obvious, but I'll post my asnwer in case it stops someone else spending a frustrating couple of hours going ground and round in circles (as I have just done)...

SELECT Results.value('(/Athletics/Event[@ID="001"]/@Name)[1]','VARCHAR(20)')
FROM SportsEvent
WHERE ID = 1

SELECT Results.value('(/Athletics/Event[@Name="100m"]/@ID)[1]','VARCHAR(20)')
FROM SportsEvent
WHERE ID = 1

SELECT Results.value('(/Athletics/Event[@Name="High Jump"]/@ID)[1]','VARCHAR(20)')
FROM SportsEvent
WHERE ID = 1

SELECT Results.value('(/Athletics/Event[@ID="002"]/@Name)[1]','VARCHAR(20)')
FROM SportsEvent
WHERE ID = 1

From what I can ascertain you can't use the query method to return just the value of an attribute the query would have to be written with the attribute outside the element which is not allowed.

You can however use the query method as follows - the '[1]'(singletons) at the end of the path aren't obligatory (they are with the value method). What you will get is the whole fragment which contains the specified attribute.

SELECT Results.query('(/Athletics/Event[@ID="002"][@Name])[1]')
FROM SportsEvent
WHERE ID = 1

SELECT Results.query('(/Athletics/Event[@Name="100m"][@Name])[1]')
FROM SportsEvent
WHERE ID = 1

Hope this helps someone.

Any comments, corrections or additions are welcomed. Thanks.


Need Your Help

Calling method after view has loaded

iphone iphone-sdk-3.0

My iPhone app allows for users to type in their usernames and passwords so that they can login. When they login once, their usernames and passwords are saved so that users don't have to type it in ...

Is there a fast parser for date

r date posixct lubridate

For datetimes fasttime provides very fast parsing to POSIXct