CODEWITHSUNDEEP
xmlt-sqlcross-apply
Yatrix
Yatrix

Reputation: 13785

How do I query all of the child nodes from the parent?

DECLARE @xml AS XML

SET @xml = CAST('<codes><pcc>DFC</pcc><pcc>MAI</pcc><pcc>PFS</pcc></codes>' AS XML)

SELECT  pcc.value('pcc[1]', 'varchar(max)') AS [ColumnTest]
FROM    @xml.nodes('/codes') results ( pcc )

I have this very simple bit of xml and am trying to pull all of the data in the <pcc> nodes into a results set. I read that I am to use CROSS APPLY somehow, but so far my efforts have failed.

Thanks in advance.

Upvotes: 1

Views: 69

Answers (1)

ljh
ljh

Reputation: 2594

    DECLARE @xml AS XML
    SET @xml = CAST('<codes><pcc>DFC</pcc><pcc>MAI</pcc><pcc>PFS</pcc></codes>' AS XML)
    SELECT  pcc.value('.', 'varchar(max)') AS [ColumnTest]
    FROM    @xml.nodes('/codes/pcc') results ( pcc )

Upvotes: 1

Related Questions