SQL XML Attribute value

2
votes

I'm trying to figure out how to get an XML attributes value when the element name changes. The attribute will be the same regardless of the element.

<obj1 id="1" name="sally" />
<obj2 id="15" date="yesterday" />

I've been trying various forms of this, but it's not working:

SELECT 
   [OriginalRecordXml].value('(/./id)[1]', 'varchar(MAX)')
FROM [AuditRecords]

Is this possible?

1
sql-serverxmltsqlxquery
Querying the XML is done via XPath expressions so you need to show the entire XML you're trying to query to get a proper answer – veljkoz
Those two records are pretty much how they look. An individual XML record is stored in a table column, so we'd only be hitting one at a time. – Dan Champagne
You mean, each tag as a row? – Jorge Campos
Yes, sorry. I should've been more clear. – Dan Champagne

1 Answers

3
votes

Something like this does the trick:

declare @t table (
    Id int identity(1,1) primary key,
    XMLData xml not null
);

insert into @t (XMLData)
values (N'<obj1 id="1" name="sally" />
<obj2 id="15" date="yesterday" />'),
(N'<objM />');

select t.Id, x.c.value('./@id', 'varchar(max)')
from @t t
    cross apply t.XMLData.nodes('//*[@id]') x(c);

Or, you can save a little bit if you only need value from first / single node:

select t.Id, t.XMLData.value('/*[@id][1]/@id', 'varchar(max)')
from @t t;