Stewart "Arturius" Campbell (1/27/2012)
The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.
Benefit of the doubt then.
Another possiblity for a right answer could have been:
SELECT[name] = T1.Toy.value('name[1]','VARCHAR(20)'),
componentname = T2.Part.value('name[1]','VARCHAR(20)'),
numberOfItems = T2.Part.value('howMany[1]','TINYINT')
FROM @x.nodes('/toys/myToy') T1(Toy)
CROSS APPLY T1.Toy.nodes('./components/part') T2(Part)
That's the same as the code I posted (just without the /text() optimization). Query plans below:
Query above:
Previous:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi