RETURING XML AS A PARAMETER .NET

Dear programmers,
I'm having real issues with returning data from an output parameter
from a SQL 2005 stored procedure. I've checked the result by applying
the input directly to the stored procedure and all seems to look fine.
Its simply that when the data returns it looks to be in the wrong
format. I'm getting forward slashes and bits and bobs that do not
deserialize because they don't correspond to the original document
stored in the database.
The sp simply takes in an xml document and returns a response xml in
the output parameter. This is returned the xml such as the
following ...
<result provider_reference=\"iTunes\"><items> ...
when this should be ...
"<result provider_reference="iTunes"><items> ...
this might just be because the result encoding is changed when i look
at the result in the immediate window ... but I'm sure I'm not reading
the result out correctly ... I'd really appreciate it if anyone knows
how to correctly read xml from an output parameters. Any advice most
warmly welcomed.
The code fails when I get to the Deserialize section and it returns
with error ""There is an error in XML document (1, 2)."
My code is as below ...
internal ResponseResult PostRequestToDatabase(ServiceRequest
Request, UserCredentials User)
{
//////////////////////////////////////////////////////////////////////////////////////////////
/// Description: This method posts a request to the
database for verification
/// Created Date: 28th November 2007
/// Created By: T.O'Donnell
///////////////////////////////////////////////////////////////////////////////////////////////
// create a local string variable to pass in the xml
string xml_posted;
string xml_returned;
// create a new connection to the database
this.DataAccess_Connection = new SqlConnection();
this.DataAccess_Connection.ConnectionString =
GetConnectionString(User);
try
{
// create a new instance of the serialiser and
textwritter objects
XmlSerializer ser = new
XmlSerializer(typeof(ServiceRequest));
StringWriter swriter = new StringWriter();
// write the xml formated classes to the xml
variable
ser.Serialize(swriter, Request);
xml_posted = swriter.ToString();
// create a command object for the storedprocedure
this.DataAccess_Command = new SqlCommand();
this.DataAccess_Command.CommandType =
System.Data.CommandType.StoredProcedure;
this.DataAccess_Command.Connection =
this.DataAccess_Connection;
this.DataAccess_Command.CommandText =
"sysadmin_get_response_result";
// set return parameter
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
xml_posted;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
ParameterDirection.Output;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.return_value", SqlDbType.Int)).Direction =
ParameterDirection.ReturnValue;
// check to see if the connection is still open
if (this.DataAccess_Connection.State ==
ConnectionState.Closed)
{
// open the connection and submit the query
this.DataAccess_Connection.Open();
}
// execute the query
this.DataAccess_Command.ExecuteNonQuery();
// get the return value to check for errors
if
((Int32)this.DataAccess_Command.Parameters["@.retur n_value"].Value ==
0)
{
// check that the returning xml is not null
if
(this.DataAccess_Command.Parameters["@.xml_result"].Value !=
DBNull.Value)
{
// get the returning xml object
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
// deserialize the results to a class
structure
XmlSerializer Serializer = new
XmlSerializer(typeof(ResponseResult));
StringReader xmlstream = new
StringReader(xml_returned);
XmlTextReader xmlreader = new
XmlTextReader(xmlstream);
// ***** THIS IS WHERE THE ERROR IS
RETURNED
return
(ResponseResult)Serializer.Deserialize(xmlreader);
}
}
// return nothing to the calling party
return null;
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
return null;
}
finally
{
// check to see if the connection object has been
initialised
if (this.DataAccess_Connection != null)
{
// check to see if the connection is still
open
if (this.DataAccess_Connection.State ==
ConnectionState.Open)
{
// close the Connection
this.DataAccess_Connection.Close();
}
}
}
}
The \" is an escaped quotation mark in C#. VS is probably adding that to
the display when you view it, assuming it's enclosing the entire string in
double quotes. I'd think your problem is probably unrelated to this. Try
printing the results directly to Console and see if the \ still appears
before the ".
"Caspian" <timothy.odonnell@.hotmail.com> wrote in message
news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
> Dear programmers,
> I'm having real issues with returning data from an output parameter
> from a SQL 2005 stored procedure. I've checked the result by applying
> the input directly to the stored procedure and all seems to look fine.
> Its simply that when the data returns it looks to be in the wrong
> format. I'm getting forward slashes and bits and bobs that do not
> deserialize because they don't correspond to the original document
> stored in the database.
> The sp simply takes in an xml document and returns a response xml in
> the output parameter. This is returned the xml such as the
> following ...
> <result provider_reference=\"iTunes\"><items> ...
> when this should be ...
> "<result provider_reference="iTunes"><items> ...
> this might just be because the result encoding is changed when i look
> at the result in the immediate window ... but I'm sure I'm not reading
> the result out correctly ... I'd really appreciate it if anyone knows
> how to correctly read xml from an output parameters. Any advice most
> warmly welcomed.
> The code fails when I get to the Deserialize section and it returns
> with error ""There is an error in XML document (1, 2)."
> My code is as below ...
> internal ResponseResult PostRequestToDatabase(ServiceRequest
> Request, UserCredentials User)
> {
> //////////////////////////////////////////////////////////////////////////////////////////////
> /// Description: This method posts a request to the
> database for verification
> /// Created Date: 28th November 2007
> /// Created By: T.O'Donnell
> ///////////////////////////////////////////////////////////////////////////////////////////////
> // create a local string variable to pass in the xml
> string xml_posted;
> string xml_returned;
> // create a new connection to the database
> this.DataAccess_Connection = new SqlConnection();
> this.DataAccess_Connection.ConnectionString =
> GetConnectionString(User);
> try
> {
> // create a new instance of the serialiser and
> textwritter objects
> XmlSerializer ser = new
> XmlSerializer(typeof(ServiceRequest));
> StringWriter swriter = new StringWriter();
> // write the xml formated classes to the xml
> variable
> ser.Serialize(swriter, Request);
> xml_posted = swriter.ToString();
> // create a command object for the storedprocedure
> this.DataAccess_Command = new SqlCommand();
> this.DataAccess_Command.CommandType =
> System.Data.CommandType.StoredProcedure;
> this.DataAccess_Command.Connection =
> this.DataAccess_Connection;
> this.DataAccess_Command.CommandText =
> "sysadmin_get_response_result";
> // set return parameter
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
> xml_posted;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
> ParameterDirection.Output;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.return_value", SqlDbType.Int)).Direction =
> ParameterDirection.ReturnValue;
> // check to see if the connection is still open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Closed)
> {
> // open the connection and submit the query
> this.DataAccess_Connection.Open();
> }
> // execute the query
> this.DataAccess_Command.ExecuteNonQuery();
> // get the return value to check for errors
> if
> ((Int32)this.DataAccess_Command.Parameters["@.retur n_value"].Value ==
> 0)
> {
> // check that the returning xml is not null
> if
> (this.DataAccess_Command.Parameters["@.xml_result"].Value !=
> DBNull.Value)
> {
> // get the returning xml object
> xml_returned =
> (string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
>
> // deserialize the results to a class
> structure
> XmlSerializer Serializer = new
> XmlSerializer(typeof(ResponseResult));
> StringReader xmlstream = new
> StringReader(xml_returned);
> XmlTextReader xmlreader = new
> XmlTextReader(xmlstream);
> // ***** THIS IS WHERE THE ERROR IS
> RETURNED
> return
> (ResponseResult)Serializer.Deserialize(xmlreader);
> }
> }
> // return nothing to the calling party
> return null;
> }
> catch (Exception ee)
> {
> Console.WriteLine(ee.Message);
> return null;
> }
> finally
> {
> // check to see if the connection object has been
> initialised
> if (this.DataAccess_Connection != null)
> {
> // check to see if the connection is still
> open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Open)
> {
> // close the Connection
> this.DataAccess_Connection.Close();
> }
> }
> }
> }
>
|||Thanks for your help Mike ... I'd spent so long looking into this
problem that I couldn't see the wood through the trees. As it happens
for future readers, it would appear that the returning XML read just
fine by looking at it from the console. Additionally, this following
line is incorrect ...
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
this should read ...
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].Value;
The problem was quite simply because I'd removed the
[XmlType("result")] above the class that I was trying to deserialize
against (which incidentally used a different name).
Hope others benefit from this code.
Kind regards,
Tim
On Jan 14, 11:54Xpm, "Mike C#" <x...@.xyz.com> wrote:
> The \" is an escaped quotation mark in C#. XVS is probably adding that to
> the display when you view it, assuming it's enclosing the entire string in
> double quotes. XI'd think your problem is probably unrelated to this. XTry
> printing the results directly to Console and see if the \ still appears
> before the ".
> "Caspian" <timothy.odonn...@.hotmail.com> wrote in message
> news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
>