Nested FOR XML PATH

Question

Post reply

Nested FOR XML PATH

jevitts

Mr or Mrs. 500

Points: 543
More actions
October 16, 2014 at 2:56 pm

#291716

Hello!
I'm trying to reverse engineer an XML output based on a client's need of a very specific format. Besides the issue of getting the XML declaration written in and removing NULLs, I'm having an issue with a three times nested PATH query.
So far the document almost has the correct format, except for the first nested root being returned. Is there any way to prevent this?
*some data redacted
Query:
DECLARE @a XML
SET @a =
(SELECT FileCreationDate,
(
SELECT PCE_TPD.PJN,
(
SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA
FROM PCE_TPD AS TaskData
WHEREPCE_TPD.[Version] = TaskData.Version
FOR XML PATH('TaskData'), TYPE
)
FROM PCE_TPD
FOR XML PATH ('TDBUIDataList'), TYPE
)
FROM PCE_TPMXML
FOR XML PATH ('TDBUIData12'), TYPE
)
;
WITH XMLNAMESPACES (DEFAULT 'http://www.xxxxx)
SELECT @a FOR XML PATH('TDBUIData')
Current Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx">
<TDBUIData12 xmlns=""> --NEED TO ELIMINATE THIS
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
<TaskData>
<TaskCode>CI021</TaskCode>
<TaskName />
<RSI>1</RSI>
<TAFFD>2015-07-13</TAFFD>
<Notes> </Notes>
<PID> </PID>
<CUSID> </CUSID>
<NeedToBeNA />
</TaskData>
Desired Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx">
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
<TaskData>
<TaskCode>CI021</TaskCode>
<TaskName />
<RSI>1</RSI>
<TAFFD>2015-07-13</TAFFD>
<Notes> </Notes>
<PID> </PID>
<CUSID> </CUSID>
<NeedToBeNA />
</TaskData>
Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Quick thought, remove the FOR XML from the nested query.

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Have you tried simply combining the 2 statements into 1? Something like

DECLARE @a XML;

WITH XMLNAMESPACES (DEFAULT 'http://www.xxxxx')

select @a=(SELECT FileCreationDate,

(

SELECT PCE_TPD.PJN,

(

SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA

FROM PCE_TPD AS TaskData

WHERE PCE_TPD.[Version] = TaskData.Version

FOR XML PATH('TaskData'), TYPE

)

FROM PCE_TPD

FOR XML PATH ('TDBUIDataList'), TYPE

)

FROM PCE_TPMXML

FOR XML PATH ('TDBUIData'), TYPE

)

Sorry it's hard to validate this without any dummy data to test against.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

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>

jevitts Mr or Mrs. 500 Points: 543 More actions · Answer 4

Thanks to both of you for your replies! This did help a bit in getting it more into the format I needed. I was tired of trying to juggle XML with those first few lines, so I turned to good old CONVERT to varchar and REPLACE. 🙂

Appreciate the help!

Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller