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

.

Relevant Pages

  • Re: Another idea from pick goes mainstream...
    ... A relational database has nothing to do ... SQL is one of the ... is now considered THE language for any dbms implementing the relational ... Then, XML came out. ...
    (comp.databases.pick)
  • Re: Deviation from object-relational mapping (pySQLFace)
    ... Its goal to separate relational database stuff from algorythmic ... file (XML). ... It provides callable command objects for each sql query. ...
    (comp.lang.python)
  • Is FOR XML EXPLICIT still an accepted technique?
    ... I have inherited an ASP IIS site attached to a SQL ... Server 2000 database. ... I am new at using XML and have done considerable reading. ...
    (microsoft.public.sqlserver.xml)
  • Re: Parsing large amounts of data (200,000 entries) with XML?
    ... since I know the different uses for a database ... and an XML document. ... I didn't invent that either, ... XML and SQL coexist quite nicely. ...
    (microsoft.public.vb.general.discussion)
  • Re: Querying Very Large XML
    ... I was hoping that MSDE (or SQL 2005 Express) might let you get around ... the "we don't want to run a database" argument. ... Yet they provide the same XML ... > XpathDocument at once. ...
    (microsoft.public.dotnet.xml)
Loading