FOR XML PATH nested query refer to parent variable

I have the following query:
Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
(Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
FROM ods_TransmitFile WHERE BunCode = 1821
FOR XML PATH, TYPE)
FROM ods_TransmitFile
GROUP BY BunCode
ORDER BY BunCode
FOR XML PATH('BunCode'), ROOT('DM')
The query returns the correct structure however I would like the nested
query to refer to the '@.BunCode' of the parent query.
At present the nested results will always be for BunCode 1821. How can
I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
element?
Hope this makes sense?
Maybe by aliasing the tables? NOTE: *Untested*:
Select otf2.BunCode as '@.BunCode', Min(otf2.TradingDate) as '@.TradingDate',
(Select otf1.ItemQty, otf1.TransactionSequenceNumber, otf1.GsiCode,
otf1.RetalItemCode
FROM ods_TransmitFile otf1 WHERE otf1.BunCode = otf2.BunCode
FOR XML PATH, TYPE)
FROM ods_TransmitFile otf2
GROUP BY otf2.BunCode
ORDER BY otf2.BunCode
FOR XML PATH('BunCode'), ROOT('DM')
<justin.drerup@.gmail.com> wrote in message
news:1166543884.982800.201900@.t46g2000cwa.googlegr oups.com...
>I have the following query:
> Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
> (Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
> FROM ods_TransmitFile WHERE BunCode = 1821
> FOR XML PATH, TYPE)
> FROM ods_TransmitFile
> GROUP BY BunCode
> ORDER BY BunCode
> FOR XML PATH('BunCode'), ROOT('DM')
> The query returns the correct structure however I would like the nested
> query to refer to the '@.BunCode' of the parent query.
> At present the nested results will always be for BunCode 1821. How can
> I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
> element?
> Hope this makes sense?
>
|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.
|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.

FOR XML PATH nested query refer to parent variable

I have the following query:
Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
(Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
FROM ods_TransmitFile WHERE BunCode = 1821
FOR XML PATH, TYPE)
FROM ods_TransmitFile
GROUP BY BunCode
ORDER BY BunCode
FOR XML PATH('BunCode'), ROOT('DM')
The query returns the correct structure however I would like the nested
query to refer to the '@.BunCode' of the parent query.
At present the nested results will always be for BunCode 1821. How can
I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
element?
Hope this makes sense?Maybe by aliasing the tables? NOTE: *Untested*:
Select otf2.BunCode as '@.BunCode', Min(otf2.TradingDate) as '@.TradingDate',
(Select otf1.ItemQty, otf1.TransactionSequenceNumber, otf1.GsiCode,
otf1.RetalItemCode
FROM ods_TransmitFile otf1 WHERE otf1.BunCode = otf2.BunCode
FOR XML PATH, TYPE)
FROM ods_TransmitFile otf2
GROUP BY otf2.BunCode
ORDER BY otf2.BunCode
FOR XML PATH('BunCode'), ROOT('DM')
<justin.drerup@.gmail.com> wrote in message
news:1166543884.982800.201900@.t46g2000cwa.googlegroups.com...
>I have the following query:
> Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
> (Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
> FROM ods_TransmitFile WHERE BunCode = 1821
> FOR XML PATH, TYPE)
> FROM ods_TransmitFile
> GROUP BY BunCode
> ORDER BY BunCode
> FOR XML PATH('BunCode'), ROOT('DM')
> The query returns the correct structure however I would like the nested
> query to refer to the '@.BunCode' of the parent query.
> At present the nested results will always be for BunCode 1821. How can
> I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
> element?
> Hope this makes sense?
>|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.