RE: Shredding XML – SQLServerCentral

SSC Guru

Points: 61136

May 5, 2015 at 3:01 pm

Erland Sommarskog (5/5/2015)
Indeed you can. You can have multiple CROSS APPLY on nodes that relate to each other:
SELECT
testsuite.c.value('@name', 'nvarchar(20)') as testsuitename,
tescase.c.value('@internalid', 'int') as testcaseid,
testcase.c.value('@name', 'nvarchar(20)') as testcasename
FROM ttt
CROSS APPLY c1.nodes('/testsuite') AS testuite(c)
CROSS APPLY testsuite.c.nodes('testcase') AS testcase(c)
I have changed the data types as you should be more judicious which types you have. nvarchar(MAX) has a higher overhead than regular nvarchar.

Minor correction to your code:

SELECT

testsuite.c.value('@name', 'nvarchar(20)') as testsuitename,

testcase.c.value('@internalid', 'int') as testcaseid,

testcase.c.value('@name', 'nvarchar(20)') as testcasename

FROM ttt

CROSS APPLY c1.nodes('/testsuite') AS testsuite(c)

CROSS APPLY testsuite.c.nodes('testcase') AS testcase(c)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001