Challenge: nested FOR XML EXPLICIT query with data not in target t

I am trying to create a nested FOR XML EXPLICIT query where the majority of the data used in the query does not come from the target table being referenced.
Actually, only a few fields will come from the target table and when those fields appear in the data I want the FOR XML EXPLICIT query to duplicate the entire document structure for each time this happens only changing those elements to which the data cor
responds to (since all of the other elements are static anyway).
What I want is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
However what I am getting is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><ITE
M>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
The <standard> tag is repeating multiple times in a single document where I want only 1 <standard> tag to appear a document. Additional <standard> tags should trigger additional documents to be generated.
Here is the FOR XML EXPLICIT query I am currently using:
SET NOCOUNT ON
SELECT
1 AS Tag
,NULL AS Parent
,NULL AS [docs!1]
,NULL AS [doc!2!ordering!hide]
,NULL AS [doc!2]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ordering!hide]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
,NULL AS [multiused!5]
,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,ID
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,'?'
,'?'
,'?'
,'?'
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
ORDER BY
[doc!2!ordering!hide]
,[settings!3!ordering!hide]
,[standard!4!ordering!hide]
,[multiused!5!ordering!hide]
,[item!6!ordering!hide]
FOR XML EXPLICIT
Any assistance would be most helpful.
Thank you.
The solution can be found by envisioning how the relation that is being
aggregated needs to look like:
1 row for every element. Thus if you want as many documents as <standard>
elements, you need to generate more than one and use some common id to group
them with their children.
Try for example (I simplified the query a bit without loss of
functionality):
SELECT
1 AS Tag
,NULL AS Parent
,ID AS [docs!1!id!hide]
,NULL AS [doc!2!ordering!hide]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
--,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
FROM vConsolidationPrinting
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,ID
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,ID
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,'?'
,'?'
,'?'
,'?'
FROM vConsolidationPrinting
/*
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
*/
ORDER BY
[docs!1!id!hide],[doc!2!ordering!hide],Tag,[settings!3!ordering!hide]
,[standard!4!ITEM!element]
,[multiused!5!ordering!hide]
, [item!6!AttachmentID!element]
FOR XML EXPLICIT
HTH
Michael
PS: BTW, this can be done much easier in SQL Server 2005 with the new PATH
mode. Here it is:
SELECT
'Letter_w_Attachments' as "settings/doctype",
'ENGLISH' as "settings/language",
'CoverLetter' as "settings/type",
'Mailroom' as "settings/branch",
'PRT1' as "settings/printer",
ID as "standard/ITEM",
'?' as "multiused/item/AttachmentID",
'?' as "multiused/item/Comment",
'?' as "multiused/item/DocName",
'?' as "multiused/item/NbrOfPages"
FROM vConsolidationPrinting
FOR XML Path('doc'), ROOT('docs')
"JRutberg" <JRutberg@.discussions.microsoft.com> wrote in message
news:303784BC-7CEC-4AA2-A1AD-1CAF38C1CE1D@.microsoft.com...
>I am trying to create a nested FOR XML EXPLICIT query where the majority of
>the data used in the query does not come from the target table being
>referenced.
> Actually, only a few fields will come from the target table and when those
> fields appear in the data I want the FOR XML EXPLICIT query to duplicate
> the entire document structure for each time this happens only changing
> those elements to which the data corresponds to (since all of the other
> elements are static anyway).
> What I want is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> However what I am getting is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><I
TEM>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> The <standard> tag is repeating multiple times in a single document where
> I want only 1 <standard> tag to appear a document. Additional <standard>
> tags should trigger additional documents to be generated.
> Here is the FOR XML EXPLICIT query I am currently using:
>
> SET NOCOUNT ON
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,NULL AS [docs!1]
> ,NULL AS [doc!2!ordering!hide]
> ,NULL AS [doc!2]
> ,NULL AS [settings!3!ordering!hide]
> ,NULL AS [settings!3!doctype!element]
> ,NULL AS [settings!3!language!element]
> ,NULL AS [settings!3!type!element]
> ,NULL AS [settings!3!branch!element]
> ,NULL AS [settings!3!printer!element]
> ,NULL AS [standard!4!ordering!hide]
> ,NULL AS [standard!4!ITEM!element]
> ,NULL AS [multiused!5!ordering!hide]
> ,NULL AS [multiused!5]
> ,NULL AS [item!6!ordering!hide]
> ,NULL AS [item!6!AttachmentID!element]
> ,NULL AS [item!6!Comment!element]
> ,NULL AS [item!6!DocName!element]
> ,NULL AS [item!6!NbrOfPages!element]
> UNION ALL
> SELECT
> 2 AS Tag
> ,1 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 3 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,'Letter_w_Attachments'
> ,'ENGLISH'
> ,'CoverLetter'
> ,'Mailroom'
> ,'PRT1'
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 4 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,ID
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM vConsolidationPrinting
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 6 AS Tag
> ,5 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,'?'
> ,'?'
> ,'?'
> ,'?'
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ORDER BY
> [doc!2!ordering!hide]
> ,[settings!3!ordering!hide]
> ,[standard!4!ordering!hide]
> ,[multiused!5!ordering!hide]
> ,[item!6!ordering!hide]
> FOR XML EXPLICIT
>
> Any assistance would be most helpful.
> Thank you.
>