How to shred XML with TSQL XQUERY

Question

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

Where am I going wrong?

All of the parts you want to get from the XML are properties, not values. `name[1]` should be `@name`, `tests[1]` should be `@tests`, etc. `name[1]` would be for extracting the value of a node *called* name (`Value`). *Though, in such a scenario, you should be using `(name/text())[1]`.* — Thom A, Dec 08 '21 at 10:42

score 1 · Accepted Answer · answered Dec 08 '21 at 17:09

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

How to shred XML with TSQL XQUERY

1 Answers1