title: How to shred XML with TSQL XQUERY
body: <p>I'm struggling to shred the following XML. I'm using Azure SQL database.</p>
  <p>Here's my sample data and code attempt.</p>
  <pre><code>DROP TABLE IF EXISTS #t ;
  CREATE TABLE #t (XMLResult XML) ;
  INSERT INTO #t (XMLResult)
  VALUES (    '&lt;testsuites&gt;
                    &lt;testsuite id=&quot;1&quot; name=&quot;Verify&quot; tests=&quot;1&quot; errors=&quot;0&quot; failures=&quot;1&quot; timestamp=&quot;2021-12-08&quot; time=&quot;0.000&quot; hostname=&quot;&quot; package=&quot;tSQLt&quot;&gt;
                      &lt;properties /&gt;
                      &lt;testcase classname=&quot;Verify&quot; name=&quot;Verify [Feature].[measure] format&quot; time=&quot;0.000&quot;&gt;
                        &lt;failure message=&quot;Format incorrectly set.&quot; type=&quot;Failure&quot; /&gt;
                      &lt;/testcase&gt;
                    &lt;/testsuite&gt;
                  &lt;/testsuites&gt;
                  ') ; 
  
  SELECT  T.* , 
          TS.H.value('name[1]', 'varchar(255)') AS [Test Method] , 
          TS.H.value('tests[1]', 'int') AS [Tests] , 
          TS.H.value('failures[1]', 'int') AS [Failures] , 
          TC.H.value('name[1]', 'VARCHAR(255)') AS [TestCase] , 
          TF.H.value('message[1]', 'VARCHAR(255)') AS [Failure Message] 
  FROM    #t  T 
  CROSS APPLY T.XMLResult.nodes('/testsuites/testsuite')          AS TS(H)
  CROSS APPLY T.XMLResult.nodes('/testsuites/testsuite/testcase') AS TC(H)
  CROSS APPLY T.XMLResult.nodes('/testsuites/testsuite/testcase/failure') AS TF(H)
   ; 
  </code></pre>
  <p>Current output</p>
  <p><a href="https://i.stack.imgur.com/Fxodm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Fxodm.png" alt="enter image description here" /></a></p>
  <p>Where am I going wrong?</p>