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

Problem use sqldatasource to access stored procedure and get data bind to label control

Hi every experts

I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput...), after that, whatever I try, I click on Test Query Button at last part, I always get error messageThe Query did not return any data table.

My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web?

Thanks anyone, who can give me any advice.

Satoshi

Here's an example for you:

ASPX

<asp:label id="Label1" runat="server" /><asp:gridview id="GridView1" runat="server" datakeynames="ProductID" datasourceid="SqlDataSource1"></asp:gridview><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onselected="SqlDataSource1_Selected" selectcommand="SELECT * FROM [Products]; SELECT @.count = COUNT(*) FROM [Products];"><selectparameters><asp:parameter direction="Output" name="count" type="int32" /></selectparameters></asp:sqldatasource>

CODE-BEHIND

protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){Label1.Text = String.Concat("Records returned:", e.Command.Parameters["@.count"].Value);}}

|||

Hi ecbruck

Thanks very much your help, I'm able to make it work now.