sql-serverxmlxquery-sql

How to shred XML with TSQL XQUERY

I'm struggling to shred the following XML. I'm using Azure SQL database.

Here's my sample data and code attempt.

DROP TABLE IF EXISTS #t ;
CREATE TABLE #t (XMLResult XML) ;
INSERT INTO #t (XMLResult)
VALUES (    '<testsuites>
                  <testsuite id="1" name="Verify" tests="1" errors="0" failures="1" timestamp="2021-12-08" time="0.000" hostname="" package="tSQLt">
                    <properties />
                    <testcase classname="Verify" name="Verify [Feature].[measure] format" time="0.000">
                      <failure message="Format incorrectly set." type="Failure" />
                    </testcase>
                  </testsuite>
                </testsuites>
                ') ; 

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)
 ;

Current output

enter image description here

Where am I going wrong?

Solution

  • As mentioned in the comments, you have XML attributes, not values, which you are trying to retrieve. So you need to reference them with @

    You are also using .nodes wrong: you should refer each one to the previous one. As it is, you are just cross-joining all the node levels together.

    SELECT  --T.* , 
        TS.H.value('@name', 'varchar(255)') AS [Test Method] , 
        TS.H.value('@tests', 'int') AS [Tests] , 
        TS.H.value('@failures', 'int') AS [Failures] , 
        TC.H.value('@name', 'VARCHAR(255)') AS [TestCase] , 
        TF.H.value('@message', 'VARCHAR(255)') AS [Failure Message] 
FROM    #t  T 
CROSS APPLY T.XMLResult.nodes('/testsuites/testsuite')          AS TS(H)
CROSS APPLY TS.H.nodes('testcase') AS TC(H)
CROSS APPLY TC.H.nodes('failure') AS TF(H)
 ;

    Note that if you are referring directly to an attribute of the current node (rather than a child node), you do not need the [1] predicate

    db<>fiddle