Extracting Attributes from XML Fields in SQL Server 2008 Table

Question

I have a table with several columns, one of which is a xml column. I do not have a namespace to use in the query. The XML data is always the same structure for all records.

Contrived Data

create table #temp (id int, name varchar(32), xml_data xml)  insert into #temp values (1, 'one',   '<data><info x="42" y="99">Red</info></data>'), (2, 'two',   '<data><info x="27" y="72">Blue</info></data>'), (3, 'three', '<data><info x="16" y="51">Green</info></data>'), (4, 'four',  '<data><info x="12" y="37">Yellow</info></data>') 

Desired Results

Name    Info.x   Info.y   Info -----   -------  -------  ------- one       42       99     Red two       27       72     Blue three     16       51     Green four      12       37     Yellow 

Partially Works

select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as [Info] from   #temp 

It returns the Name and Info columns. I cannot figure out how to extract the attribute values without using a namespace. For instance, the following queries returns errors:

Query 1

select Name, xml_data.query('/data/info/@x') as [Info] from   #temp  Msg 2396, Level 16, State 1, Line 12 XQuery [#temp.xml_data.query()]: Attribute may not appear outside of an element 

Query 2

select Name, xml_data.value('/data/info/@x', 'int') as [Info] from   #temp  Msg 2389, Level 16, State 1, Line 12 XQuery [#temp.xml_data.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *' 

Query 3

select Name, xml_data.query('/data/info/.').value('@x', 'int') as [Info] from   #temp  Msg 2390, Level 16, State 1, Line 9 XQuery [value()]: Top-level attribute nodes are not supported 

Question

How do you write a query to return regular column data, and element + attribute values from an xml column in the same table?

Answer 1

Just after I posted the question, I stumbled across this answer. Don't know why I couldn't find it in prior searches. It was the answer I was looking for. Here is the query that works:

Query

select Name       ,xml_data.value('(/data/info/@x)[1]', 'int') as [Info.x]       ,xml_data.value('(/data/info/@y)[1]', 'int') as [Info.y]       ,xml_data.value('(/data/info/.)[1]', 'varchar(10)') as [Info] from   #temp 

Result

Name     Info.x    Info.y    Info -------  --------  --------  --------- one         42        99     Red two         27        72     Blue three       16        51     Green four        12        37     Yellow 

.

------ Edit [2014-01-29] ------

I found another case that is worth adding to this answer. Given multiple <info> elements within the <data> element, it is possible to return all <info> nodes by using cross apply:

create table #temp (id int, name varchar(32), xml_data xml)  insert into #temp values (1, 'one',   '<data><info x="42" y="99">Red</info><info x="43" y="100">Pink</info></data>'), (2, 'two',   '<data><info x="27" y="72">Blue</info><info x="28" y="73">Light Blue</info></data>'), (3, 'three', '<data><info x="16" y="51">Green</info><info x="17" y="52">Orange</info></data>'), (4, 'four',  '<data><info x="12" y="37">Yellow</info><info x="13" y="38">Purple</info></data>')  select Name       ,C.value('@x', 'int') as [Info.x]       ,C.value('@y', 'int') as [Info.y]       ,C.value('.', 'varchar(10)') as [Info] from #temp cross apply      #temp.xml_data.nodes('data/info') as X(C)  drop table #temp 

This example returns the following dataset:

Name      Info.x      Info.y      Info --------- ----------- ----------- ---------- one       42          99          Red one       43          100         Pink two       27          72          Blue two       28          73          Light Blue three     16          51          Green three     17          52          Orange four      12          37          Yellow four      13          38          Purple