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["@return_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_result"].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();
}
}

}
}

.

Relevant Pages

  • RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Vari
    ... Instead of using an OUTPUT parameter in the sp, set the Execute SQL Task (on ... With stored procedures, the other connection ... listed in the stored procedure, which makes the connection a little more ... I've created a Master Package that will be used as a template for developing ...
    (microsoft.public.sqlserver.dts)
  • Invalid property size within Parameters
    ... stored procedure and the code. ... output parameter, compared the email to a table and matches an ID in another ... Dim getConn As New Connection ... Dim myConnection As SqlConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Invalid property size within Parameters
    ... values from stored procedures you use ExecuteScalar not ExecuteNonQuery. ... > stored procedure and the code. ... > Dim getConn As New Connection ... > 'Create output parameter ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: RETURING XML AS A PARAMETER .NET
    ... the input directly to the stored procedure and all seems to look fine. ... The sp simply takes in an xml document and returns a response xml in ... // create a local string variable to pass in the xml ... // check to see if the connection is still open ...
    (microsoft.public.sqlserver.xml)
  • CurrentUser Not Working
    ... Are you using ADO to pull the output parameter of the ... be careful about the connection ... you are using for the command object. ...
    (microsoft.public.access.security)
Loading