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.
>
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Cell Formatting
Hi,
On importing an XML file using VBA I/O function, I need to apply rules/format to specific Excel 2003 .Cells.
XML
<Column FieldName="ProductReference" FieldNumber="1">
<DataType>character</DataType>
<Format>x(20)</Format>
<Restrictions/>
<Mandatory>true</Mandatory>
<PermittedValues/>
</Column>
If the above XML-lines (strings) are read, I need to apply the following rules/formats to the identified .Cells(x,1):
1.
.Columns("A").NumberFormat = "General" or "Text" ??
2.
That when inputting a value, the user is restricted to 20 Characters
3.
That this is a Mandatory entry field
These values obviously change as per XML Output. Can anyone help, please?Is this related to Crystal Reports?
On importing an XML file using VBA I/O function, I need to apply rules/format to specific Excel 2003 .Cells.
XML
<Column FieldName="ProductReference" FieldNumber="1">
<DataType>character</DataType>
<Format>x(20)</Format>
<Restrictions/>
<Mandatory>true</Mandatory>
<PermittedValues/>
</Column>
If the above XML-lines (strings) are read, I need to apply the following rules/formats to the identified .Cells(x,1):
1.
.Columns("A").NumberFormat = "General" or "Text" ??
2.
That when inputting a value, the user is restricted to 20 Characters
3.
That this is a Mandatory entry field
These values obviously change as per XML Output. Can anyone help, please?Is this related to Crystal Reports?
Sunday, February 19, 2012
CDATA In .value() Method Result
Hi All,
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDA
TA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![C
DATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDA
TA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![C
DATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
CDATA In .value() Method Result
Hi All,
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]
First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]
First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
CDATA and SQL Server 2005
Hi I am using the June CTP and I am wondering about Inserting XML Documents
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?
CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?
CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
CDATA and SQL Server 2005
Hi I am using the June CTP and I am wondering about Inserting XML Documents
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
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...
>
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...
>
Sunday, February 12, 2012
Casting an in-memory resultset to a DataSet for use in XML file generation
I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).
I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.
I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.
PublicSub Main()
'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)
Dim mySet As DataSet
mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))
mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)
Dts.TaskResult = Dts.Results.Success
EndSub
I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?
Thanks
I worked around this issue by saving the transformed result set to a temporary SQL table.
Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.
Not elegant, but it gets the job done in time for my Wednesday deadline.
I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.
Thanks
|||
Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx
For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.
Add a reference to System.Xml
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
PublicClass ScriptMain
PublicSub Main()
Dim dtAsNew System.Data.DataTable
Dim oleadAsNew Data.OleDb.OleDbDataAdapter
olead.Fill(dt, Dts.Variables("User::rs").Value)
Dim drAs DataRow
ForEach drIn dt.Rows
MsgBox(dr.Item("Name").ToString)
Next
Dts.TaskResult = Dts.Results.Success
EndSub
EndClass
Casting an in-memory resultset to a DataSet for use in XML file generation
I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).
I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.
I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.
Public Sub Main()
'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)
Dim mySet As DataSet
mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))
mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)
Dts.TaskResult = Dts.Results.Success
End Sub
I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?
Thanks
I worked around this issue by saving the transformed result set to a temporary SQL table.
Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.
Not elegant, but it gets the job done in time for my Wednesday deadline.
I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.
Thanks
|||
Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx
For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.
Add a reference to System.Xml
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim dt As New System.Data.DataTable
Dim olead As New Data.OleDb.OleDbDataAdapter
olead.Fill(dt, Dts.Variables("User::rs").Value)
Dim dr As DataRow
For Each dr In dt.Rows
MsgBox(dr.Item("Name").ToString)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Casting an in-memory resultset to a DataSet for use in XML file generation
I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).
I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.
I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.
Public Sub Main()
'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)
Dim mySet As DataSet
mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))
mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)
Dts.TaskResult = Dts.Results.Success
End Sub
I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?
Thanks
I worked around this issue by saving the transformed result set to a temporary SQL table.
Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.
Not elegant, but it gets the job done in time for my Wednesday deadline.
I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.
Thanks
|||
Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx
For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.
Add a reference to System.Xml
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim dt As New System.Data.DataTable
Dim olead As New Data.OleDb.OleDbDataAdapter
olead.Fill(dt, Dts.Variables("User::rs").Value)
Dim dr As DataRow
For Each dr In dt.Rows
MsgBox(dr.Item("Name").ToString)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Friday, February 10, 2012
Cast as XML issue
Hi,
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char ...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
Olivier
Can you write something more about your compression/decompression method?
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "oliviers" <olivier.sanzot@.gmail.com> napisal w wiadomosci
news:1185456328.444876.256200@.d55g2000hsg.googlegr oups.com...
Hi,
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char ...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
Olivier
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char ...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
Olivier
Can you write something more about your compression/decompression method?
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "oliviers" <olivier.sanzot@.gmail.com> napisal w wiadomosci
news:1185456328.444876.256200@.d55g2000hsg.googlegr oups.com...
Hi,
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char ...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
Olivier
Cast as XML issue
Hi,
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char =B5...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
OlivierCan you write something more about your compression/decompression method?
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "oliviers" <olivier.sanzot@.gmail.com> napisal w wiadomosci
news:1185456328.444876.256200@.d55g2000hsg.googlegroups.com...
Hi,
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char ...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
Olivier
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char =B5...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
OlivierCan you write something more about your compression/decompression method?
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "oliviers" <olivier.sanzot@.gmail.com> napisal w wiadomosci
news:1185456328.444876.256200@.d55g2000hsg.googlegroups.com...
Hi,
Here is my problem:
I'm using one table having 2 columns.
Col A has type varbinary(max)
Col B has type XML
I'm storing XML Data in ColB and and a byte array representing the
same XML data compressed in ColA.
The goal is of course to store only ColA data to save space.
If I display the content of ColB from a management studio query,
everything is perfectly displayed.
If I try to decompress and cast the result as XML, I get a "XML
parsing: line 1, character nnnnn, illegal xml character" error
message. The process is stuck on a string containing the char ...
The exact same string is perfectly displayed from ColB.
I tried storing and compressing the XML without encoding attribute,
with other encoding attribute, nothing helped.
Would someone have an advice?
Thanks,
Olivier
Subscribe to:
Posts (Atom)