Problem with CDATA section in XQuery

Hi,
i am trying to insert a CDATA section using XQuery in T-SQL. I am following
the example from books online
http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting th
e
error 'XQuery [modify()]: Syntax error near '<!''
The code is:
DECLARE @.myDoc xml
SET @.myDoc =
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features> </Features>
</ProductDescription>
</Root>'
SELECT @.myDoc
SET @.myDoc.modify('
insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
into (/Root/ProductDescription/Features)[1] ')
SELECT @.myDoc ;
Does anyone have a solution for this?
Thanks,
Stefan
I think you can achieve the same effect by doing this
SET @.myDoc.modify('
insert text {"<notxml> as text </notxml> or cdata"}
into (/Root/ProductDescription/Features)[1]')|||Hello markc600@.hotmail.com,
Not really. If he really does want to use the CDATA tag here, he can't --
the content doesn't mater. I suspect, but XMLRW isn't parsing it correctly.
This smells like a bug to me.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||In this case I believe you should be able to use the text node constructor,
like
this
SET @.myDoc.modify('insert text{"<notxml> as text </notxml> or cdata"} into
(/Root/ProductDescription/Features)[1] ')
Denis Ruckebusch
SQL Server XML datatype test team
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mindflower" <mindflower@.discussions.microsoft.com> wrote in message
news:9DBECB6E-006B-4537-A039-CEEA784B9FD8@.microsoft.com...
> Hi,
> i am trying to insert a CDATA section using XQuery in T-SQL. I am followin
g
> the example from books online
> http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting
the
> error 'XQuery [modify()]: Syntax error near '<!''
> The code is:
> DECLARE @.myDoc xml
> SET @.myDoc =
> '<Root>
> <ProductDescription ProductID="1" ProductName="Road Bike">
> <Features> </Features>
> </ProductDescription>
> </Root>'
> SELECT @.myDoc
> SET @.myDoc.modify('
> insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
> into (/Root/ProductDescription/Features)[1] ')
> SELECT @.myDoc ;
> Does anyone have a solution for this?
> Thanks,
> Stefan

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.

SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT

in above query misc1 column may contain some non printable ascii
characters.

But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"

DECLARE @.XMLMessage XML

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)

I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.

Let me know if something dose'nt make sense

any help is appreciated

Thanks

You should ask the question in the SQL Server XML Forums.

Can you base64 encode your varbinary data instead of using CDATA section?

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML AUTO, BINARY BASE64)

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanks
prashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
XML FAQ: http://xml.silmaril.ie/
|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant
|||If you need to transport these unprintable characters in XML, you need to
cast the column to varbinary(max) and transport it as base64 encoded binary
data.
Best regards
Michael
<prashant.k.jain@.gmail.com> wrote in message
news:1162602860.975903.50180@.m73g2000cwd.googlegro ups.com...
> Peter Flynn wrote:
> Just want to make sure is their no way other then filtering out or
> encoding in some other manner. I thought their musst be some way within
> SQL server to handel this.
> Thanks,
> Prashant
>
sql

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanksprashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant