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

Question

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)?

SteelyDanFan · Answer 1 · 2015-07-12T17:25:32.787

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.

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

1 Answers1