title: Extracting Attributes from XML Fields in SQL Server 2008 Table
body: <p>I have a table with several columns, one of which is a <code>xml</code> column.  I do not have a namespace to use in the query.  The XML data is always the same structure for all records.</p>
  
  <h2>Contrived Data</h2>
  
  <pre><code>create table #temp (id int, name varchar(32), xml_data xml)
  
  insert into #temp values
  (1, 'one',   '&lt;data&gt;&lt;info x="42" y="99"&gt;Red&lt;/info&gt;&lt;/data&gt;'),
  (2, 'two',   '&lt;data&gt;&lt;info x="27" y="72"&gt;Blue&lt;/info&gt;&lt;/data&gt;'),
  (3, 'three', '&lt;data&gt;&lt;info x="16" y="51"&gt;Green&lt;/info&gt;&lt;/data&gt;'),
  (4, 'four',  '&lt;data&gt;&lt;info x="12" y="37"&gt;Yellow&lt;/info&gt;&lt;/data&gt;')
  </code></pre>
  
  <h2>Desired Results</h2>
  
  <pre><code>Name    Info.x   Info.y   Info
  -----   -------  -------  -------
  one       42       99     Red
  two       27       72     Blue
  three     16       51     Green
  four      12       37     Yellow
  </code></pre>
  
  <h2>Partially Works</h2>
  
  <pre><code>select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as [Info]
  from   #temp
  </code></pre>
  
  <p>It returns the <code>Name</code> and <code>Info</code> columns.  I cannot figure out how to extract the attribute values without using a namespace.  For instance, the following queries returns errors:</p>
  
  <h2>Query 1</h2>
  
  <pre><code>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
  </code></pre>
  
  <h2>Query 2</h2>
  
  <pre><code>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 *'
  </code></pre>
  
  <h2>Query 3</h2>
  
  <pre><code>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
  </code></pre>
  
  <h2>Question</h2>
  
  <p>How do you write a query to return regular column data, and element + attribute values from an <code>xml</code> column in the same table?</p>