Showing posts with label explicit. Show all posts
Showing posts with label explicit. Show all posts

Wednesday, March 7, 2012

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.
>
Posted by Wallace at 1:41 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Sunday, February 19, 2012

CDATA & FOR XML PATH

Hi,
Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is it
possible to create a node creating cdata?
For example, the statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit, type)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
Produces;
<info>
<details>
<description>text...</description>
<description>text...</description>
</details>
</info>
Using the nested for xml explicit I was hoping to be able to display the
second description node as;
<description><![CDATA[text...]]></description>
When the 'type' directive is specified it ignores the fact it should contain
cdata.
Am I missing something?...Is this possible to do?
Thanks
Pete
Hi Pete
If you use a FOR XML expression with the TYPE directive, you get an XML
datatype. And the XML datatype (since it is based on the XQuery Datamodel)
does not preserve the CDATA section information.
So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
directive at the top.
Now, I would like to better understand why you want to generate a CDATA
section in the first place. The only impact it has is to allow people to
author certain XML content without having to explicitly entitize characters
such as <, & etc.. Why would that be important during serializing a FOR XML
result?
Thanks
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is
> it possible to create a node creating cdata?
> For example, the statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit, type)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> Produces;
> <info>
> <details>
> <description>text...</description>
> <description>text...</description>
> </details>
> </info>
> Using the nested for xml explicit I was hoping to be able to display the
> second description node as;
> <description><![CDATA[text...]]></description>
> When the 'type' directive is specified it ignores the fact it should
> contain cdata.
> Am I missing something?...Is this possible to do?
> Thanks
> Pete
>
|||Hi Michael,
Thanks for your reply. My belief is that the use of the cdata directive is
necessary as I have html content stored in the database and need to be able
to be rendered as html when producing an xslt transformation. is there an
alternative way that you know of to do this in SQL Server 2005.
When I try using the same statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
without using the 'TYPE' directive the result displays as follows;
<info>
<details>
<description>text...</description>
<description><![CDATA[text...]]></description>
</details>
</info>
How can I ensure the both the html and the element containing the cdata are
rendered correctly (eg. <element> rather than '<element>...')?...Is
there any alternative way to do this that I'm missing?
Thanks
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi Pete
> If you use a FOR XML expression with the TYPE directive, you get an XML
> datatype. And the XML datatype (since it is based on the XQuery Datamodel)
> does not preserve the CDATA section information.
> So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
> directive at the top.
> Now, I would like to better understand why you want to generate a CDATA
> section in the first place. The only impact it has is to allow people to
> author certain XML content without having to explicitly entitize
> characters such as <, & etc.. Why would that be important during
> serializing a FOR XML result?
> Thanks
> Michael
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
>
|||XSLT should not make a difference between the CDATA section and content that
just had been entitized.
Did you try to take your original output (with the TYPE directive) and pass
it through your XSLT style sheet?
Alternatively, if you need to preserve your CDATA section, you need to use a
top-level EXPLICIT mode query:
select 1 as Tag, NULL as Parent, pt_description as
[details!1!description!element],pt_description as
[details!1!description!cdata]
from property where pt_id = 8957627
for xml explicit, root('info')
Best regards
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eHmIDS0dFHA.1612@.tk2msftngp13.phx.gbl...
> Hi Michael,
> Thanks for your reply. My belief is that the use of the cdata directive
> is necessary as I have html content stored in the database and need to be
> able to be rendered as html when producing an xslt transformation. is
> there an alternative way that you know of to do this in SQL Server 2005.
> When I try using the same statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> without using the 'TYPE' directive the result displays as follows;
> <info>
> <details>
> <description>text...</description>
> <description><![CDATA[text...]]></description>
> </details>
> </info>
> How can I ensure the both the html and the element containing the cdata
> are rendered correctly (eg. <element> rather than
> '<element>...')?...Is there any alternative way to do this that I'm
> missing?
> Thanks
> Pete
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
Posted by Wallace at 2:23 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)