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