RE: Nested FOR XML PATH – SQLServerCentral

Eirikur Eiriksson

SSC Guru

Points: 182879

October 18, 2014 at 4:19 am

#1753302

Quick runnable demonstration on the flexibility of FOR XML PATH

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH BASE_DATA(BD_ID,BD_VAL) AS

(SELECT * FROM (VALUES

(1,'ABC')

,(2,'DEF')

,(3,'GHI')

,(4,'JKL')

,(5,'MNO')

) AS X(BD_ID,BD_VAL)

)

,ADDITIONAL_CODE (BD_ID,AC_CODE) AS

(SELECT * FROM (VALUES

(1,'A01')

,(1,'B02')

,(2,'C03')

,(2,'D04')

,(3,'E05')

,(3,'F06')

,(4,'G07')

,(4,'H08')

,(5,'I09')

,(5,'J10')

,(2,'K11')

,(4,'L12')

,(5,'M13')

) AS X(BD_ID,AC_CODE)

)

SELECT

AC.AC_CODE AS 'MYStuff/@Code'

,BD.BD_ID AS 'MYStuff/MYOtherStuff/@BDElement'

,(SELECT COUNT(*) FROM ADDITIONAL_CODE AA

WHERE AA.BD_ID = AC.BD_ID) AS 'MYStuff/MYOtherStuff/@Count'

,BD.BD_VAL AS 'MYStuff/MYOtherStuff'

,(SELECT

AA.AC_CODE AS 'Details/@CODE'

,AA.BD_ID AS 'Details'

FROM ADDITIONAL_CODE AA

WHERE AA.BD_ID = AC.BD_ID

FOR XML PATH(''),TYPE) AS 'MYStuff/MYOtherStuff'

,(SELECT

AA.AC_CODE AS 'Details/@CODE'

,AA.BD_ID AS 'Details'

,(SELECT

BD.BD_VAL

FROM BASE_DATA BB

WHERE BB.BD_ID = AA.BD_ID

FOR XML PATH('MYOtherStuff'),TYPE) AS 'BDCode'

FROM ADDITIONAL_CODE AA

WHERE AA.BD_ID = AC.BD_ID

FOR XML PATH('MYOtherStuff'),TYPE) AS 'MYStuff'

FROM BASE_DATA BD

LEFT OUTER JOIN ADDITIONAL_CODE AC

ON BD.BD_ID = AC.BD_ID

FOR XML PATH('MyPath'), ROOT ('MyRoot');

Results

<MyRoot>

<MyPath>

<MYStuff Code="A01">

<MYOtherStuff BDElement="1" Count="2">ABC<Details CODE="A01">1</Details><Details CODE="B02">1</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="A01">1</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>ABC</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="B02">1</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>ABC</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="B02">

<MYOtherStuff BDElement="1" Count="2">ABC<Details CODE="A01">1</Details><Details CODE="B02">1</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="A01">1</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>ABC</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="B02">1</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>ABC</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="C03">

<MYOtherStuff BDElement="2" Count="3">DEF<Details CODE="C03">2</Details><Details CODE="D04">2</Details><Details CODE="K11">2</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="C03">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="D04">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="K11">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="D04">

<MYOtherStuff BDElement="2" Count="3">DEF<Details CODE="C03">2</Details><Details CODE="D04">2</Details><Details CODE="K11">2</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="C03">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="D04">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="K11">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="K11">

<MYOtherStuff BDElement="2" Count="3">DEF<Details CODE="C03">2</Details><Details CODE="D04">2</Details><Details CODE="K11">2</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="C03">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="D04">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="K11">2</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>DEF</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="E05">

<MYOtherStuff BDElement="3" Count="2">GHI<Details CODE="E05">3</Details><Details CODE="F06">3</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="E05">3</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>GHI</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="F06">3</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>GHI</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="F06">

<MYOtherStuff BDElement="3" Count="2">GHI<Details CODE="E05">3</Details><Details CODE="F06">3</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="E05">3</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>GHI</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="F06">3</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>GHI</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="G07">

<MYOtherStuff BDElement="4" Count="3">JKL<Details CODE="G07">4</Details><Details CODE="H08">4</Details><Details CODE="L12">4</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="G07">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="H08">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="L12">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="H08">

<MYOtherStuff BDElement="4" Count="3">JKL<Details CODE="G07">4</Details><Details CODE="H08">4</Details><Details CODE="L12">4</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="G07">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="H08">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="L12">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="L12">

<MYOtherStuff BDElement="4" Count="3">JKL<Details CODE="G07">4</Details><Details CODE="H08">4</Details><Details CODE="L12">4</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="G07">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="H08">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="L12">4</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>JKL</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="I09">

<MYOtherStuff BDElement="5" Count="3">MNO<Details CODE="I09">5</Details><Details CODE="J10">5</Details><Details CODE="M13">5</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="I09">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="J10">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="M13">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="J10">

<MYOtherStuff BDElement="5" Count="3">MNO<Details CODE="I09">5</Details><Details CODE="J10">5</Details><Details CODE="M13">5</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="I09">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="J10">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="M13">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

<MyPath>

<MYStuff Code="M13">

<MYOtherStuff BDElement="5" Count="3">MNO<Details CODE="I09">5</Details><Details CODE="J10">5</Details><Details CODE="M13">5</Details></MYOtherStuff>

<MYOtherStuff>

<Details CODE="I09">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="J10">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

<MYOtherStuff>

<Details CODE="M13">5</Details>

<BDCode>

<MYOtherStuff>

<BD_VAL>MNO</BD_VAL>

</MYOtherStuff>

</BDCode>

</MYOtherStuff>

</MYStuff>

</MyPath>

</MyRoot>