How to shred XML with TSQL XQUERY-CodePudding

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 formats" tests="1" errors="0" failures="1" timestamp="2021-12-08" time="0.000" hostname="" package="tSQLt">
                    <properties />
                    <testcase classname="Verify formats" name="Verify [Feature engagement].[Test measure] format" time="0.000">
                      <failure message="Format measure incorrectly set." type="Failure" />
                    </testcase>
                    <system-out />
                    <system-err />
                  </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

Where am I going wrong?

CodePudding user response：

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