title: How to query for Xml values and attributes from table in SQL Server?
body: <p>I have a table that contains a <code>Xml</code> column:</p>
  
  <pre><code>SELECT * 
  FROM Sqm
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/nVgUG.png" alt="enter image description here"></p>
  
  <p>A sample of the <code>xml</code> data of a row would be:</p>
  
  <pre><code>&lt;Sqm version="1.2"&gt;
    &lt;Metrics&gt;
      &lt;Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490"   average="21490"   minValue="73701"    maxValue="73701"                               &gt;73701&lt;/Metric&gt;
      &lt;Metric id="TransactionCleanupThread.RefundOldTrans"             type="timer" unit="µs" count="1" sum="184487"  average="184487"  minValue="632704"   maxValue="632704"                              &gt;632704&lt;/Metric&gt;
      &lt;Metric id="Database.CreateConnection_SaveContextUserGUID"       type="timer" unit="µs" count="2" sum="7562"    average="3781"    minValue="12928"    maxValue="13006"    standardDeviation="16"     &gt;12967&lt;/Metric&gt;
      &lt;Metric id="Global.CurrentUser"                                  type="timer" unit="µs" count="6" sum="4022464" average="670411"  minValue="15"       maxValue="13794345" standardDeviation="1642047"&gt;2299194&lt;/Metric&gt;
      &lt;Metric id="Global.CurrentUser_FetchIdentityFromDatabase"        type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614"                            &gt;13752614&lt;/Metric&gt;
    &lt;/Metrics&gt;
  &lt;/Sqm&gt;
  </code></pre>
  
  <p>In the case of this data, I would want:</p>
  
  <pre><code>SqmId  id                                                   type   unit  count  sum      minValue  maxValue  standardDeviation  Value
  =====  ===================================================  =====  ====  =====  ======   ========  ========  =================  ======
  1      TransactionCleanupThread.RecordUsedTransactionShift  timer  µs    1      21490    73701     73701     NULL               73701
  1      TransactionCleanupThread.RefundOldTrans              timer  µs    1      184487   632704    632704    NULL               632704
  1      Database.CreateConnection_SaveContextUserGUID        timer  µs    2      7562     12928     13006     16                 12967
  1      Global.CurrentUser                                   timer  µs    6      4022464  15        13794345  1642047            2299194
  1      Global.CurrentUser_FetchIdentityFromDatabase         timer  µs    1      4010057  13752614  13752614  NULL               13752614
  2      ...
  </code></pre>
  
  <p>In the end I'll actually be performing <code>SUM()</code>, <code>MIN()</code>, <code>MAX()</code> aggregation. But for now I'm just trying to <em>query</em> an xml column.</p>
  
  <p>In pseudo-code, I would try something like:</p>
  
  <pre><code>SELECT
      SqmId,
      Data.query('/Sqm/Metrics/Metric/@id') AS id,
      Data.query('/Sqm/Metrics/Metric/@type') AS type,
      Data.query('/Sqm/Metrics/Metric/@unit') AS unit,
      Data.query('/Sqm/Metrics/Metric/@sum') AS sum,
      Data.query('/Sqm/Metrics/Metric/@count') AS count,
      Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue,
      Data.query('/Sqm/Metrics/Metric/@maxValue') AS maxValue,
      Data.query('/Sqm/Metrics/Metric/@standardDeviation') AS standardDeviation,
      Data.query('/Sqm/Metrics/Metric') AS value
  FROM Sqm
  </code></pre>
  
  <p>But that SQL query doesn't work:</p>
  
  <blockquote>
    <p>Msg 2396, Level 16, State 1, Line 2<br>
    XQuery [Sqm.data.query()]: Attribute may not appear outside of an element</p>
  </blockquote>
  
  <p>I've hunted, and it's amazing how poorly documented, or exampled, Xml querying is. Most resources rather than querying a <strong>table</strong>, query a <strong>variable</strong>; which I'm not doing. Most resources only use xml querying for filtering and selection, rather than reading values. Most resources read hard-coded child nodes (by index), rather than actual values.</p>
  
  <h2>Related resources that I read</h2>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008">https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008</a></li>
  <li><a href="https://stackoverflow.com/questions/12913724/sql-server-query-xml-attribute-for-an-element-value">SQL Server query xml attribute for an element value</a></li>
  <li><a href="https://stackoverflow.com/questions/14343263/sql-querying-xml-attributes">SQL querying XML attributes</a></li>
  <li><a href="http://www.codeguru.com/csharp/.net/net_data/article.php/c19491/SQL-Server-2005-XQuery-and-XMLDML--Part-1.htm" rel="noreferrer">SQL Server 2005 XQuery and XML-DML - Part 1</a></li>
  <li><a href="http://msdn.microsoft.com/en-us/library/ms345117.aspx" rel="noreferrer">BOL: XML Support in Microsoft SQL Server 2005</a></li>
  <li><a href="http://blog.jontav.com/post/6811942997/querying-xml-in-sql-server" rel="noreferrer">Querying XML in SQL Server</a></li>
  <li><a href="http://www.mssqltips.com/sqlservertip/2889/basic-sql-server-xml-querying/" rel="noreferrer">Basic SQL Server XML Querying</a></li>
  <li><a href="http://technet.microsoft.com/en-us/library/ms191474.aspx" rel="noreferrer">BOL: query() Method (xml Data Type)</a></li>
  <li><a href="http://jacobsebastian.blogspot.ca/2007/11/xml-workshop-v-reading-values-from-xml.html" rel="noreferrer">XML Workshop V - Reading Values from XML Columns</a></li>
  <li><a href="http://blog.sqlauthority.com/2012/04/27/sql-server-introduction-to-discovering-xml-data-type-methods-a-primer/" rel="noreferrer">SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer</a></li>
  </ul>
  
  <h1>Update: .value rather than .query</h1>
  
  <p>I tried randomly using <code>.value</code>, in place of <code>.query</code>:</p>
  
  <pre><code>SELECT
      Sqm.SqmId,
      Data.value('/Sqm/Metrics/Metric/@id', 'varchar(max)') AS id,
      Data.value('/Sqm/Metrics/Metric/@type', 'varchar(max)') AS type,
      Data.value('/Sqm/Metrics/Metric/@unit', 'varchar(max)') AS unit,
      Data.value('/Sqm/Metrics/Metric/@sum', 'varchar(max)') AS sum,
      Data.value('/Sqm/Metrics/Metric/@count', 'varchar(max)') AS count,
      Data.value('/Sqm/Metrics/Metric/@minValue', 'varchar(max)') AS minValue,
      Data.value('/Sqm/Metrics/Metric/@maxValue', 'varchar(max)') AS maxValue,
      Data.value('/Sqm/Metrics/Metric/@standardDeviation', 'varchar(max)') AS standardDeviation,
      Data.value('/Sqm/Metrics/Metric', 'varchar(max)') AS value
  FROM Sqm
  </code></pre>
  
  <p>But that also doesn't work: </p>
  
  <blockquote>
    <p>Msg 2389, Level 16, State 1, Line 3 XQuery [Sqm.data.value()]:<br>
    'value()' requires a singleton (or empty sequence), found operand of
    type 'xdt:untypedAtomic *'</p>
  </blockquote>