• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2566
  • Last Modified:

Save XML to SQL Server database - C# - CRASH - Please Help

I am trying to save XML to SQL Server 2005 database through C# in a ASP.Net Application ?

It's crashing on
 myCommand.ExecuteNonQuery();

Error getting while debugging is - "Failed to convert parameter value from a XmlDocument to a String."
Error showing in error page is "Object must implement IConvertible"

What I am doing wrong here ?

Appreciates your suggestion that could resolve this issue!

Thanks
Raj


This is the Stored Procedure
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[uspS_WorkspeedXML]
(
	@WorkspeedTransDetailsID	BIGINT,
	@RequestXML					XML,
	@RequestTime				DATETIME,
	@ResponseXML				XML,
	@ResponseTime				DATETIME
)
AS
BEGIN

	DECLARE @ATTEMPT INT
	SET @ATTEMPT = 0

	SELECT @ATTEMPT = COUNT(*) FROM TBL_WORKSPEED_XMLs
		WHERE WorkspeedTransDetailsID = @WorkspeedTransDetailsID

	INSERT INTO TBL_WORKSPEED_XMLs
	(
		WorkspeedTransDetailsID,
		AttemptNumber,
		RequestXML,
		ResponseXML,
		RequestTime,
		ResponseTime
	)
	VALUES
	(
		@WorkspeedTransDetailsID,
		@ATTEMPT + 1,
		@RequestXML,
		@ResponseXML,
		@RequestTime,
		@ResponseTime
	)

END

Open in new window


Here is the C# Code
 
private void SaveXMLToDatabase(XmlDocument xmlRequest, XmlDocument xmlResponse, DateTime requestTime,
                    DateTime responseTime, SqlConnection mySQLConnection)
    {
        SqlCommand myCommand = default(SqlCommand);

        if (mySQLConnection.State == 0)
            mySQLConnection.Open();


        myCommand = new SqlCommand("uspS_WorkspeedXML", mySQLConnection);
        myCommand.CommandType = CommandType.StoredProcedure;

        // @WorkspeedTransDetailsID	    BIGINT
        myCommand.Parameters.Add(new SqlParameter("@WorkspeedTransDetailsID", SqlDbType.BigInt));
        myCommand.Parameters["@WorkspeedTransDetailsID"].Value = objUser.SubmitTransMasterID; // TBL_WORKSPEED_DETAILS.WorkspeedTransDetailsID

        // @RequestXML	XML
        myCommand.Parameters.Add(new SqlParameter("@RequestXML", SqlDbType.Xml));
        myCommand.Parameters["@RequestXML"].Value = xmlRequest;

        // @RequestTime				DATETIME
        myCommand.Parameters.Add(new SqlParameter("@RequestTime", SqlDbType.DateTime));
        myCommand.Parameters["@RequestTime"].Value = requestTime;

        // @ResponseXML				XML
        myCommand.Parameters.Add(new SqlParameter("@ResponseXML", SqlDbType.Xml));
        myCommand.Parameters["@ResponseXML"].Value = xmlResponse;
        
        // @ResponseTime				DATETIME
        myCommand.Parameters.Add(new SqlParameter("@ResponseTime", SqlDbType.DateTime));
        myCommand.Parameters["@ResponseTime"].Value = responseTime;

        myCommand.ExecuteNonQuery();

        myCommand.Dispose();

        if (mySQLConnection.State == ConnectionState.Open)
            mySQLConnection.Close();

    }

Open in new window

0
Rajkumar Gs
Asked:
Rajkumar Gs
  • 17
  • 5
4 Solutions
 
crisco96Commented:
I think you need to convert your xmlDocument to a string representation of the xml.

Use the function below like so:
        myCommand.Parameters["@RequestXML"].Value = GetXmlString(xmlRequest);
        myCommand.Parameters["@ResponseXML"].Value = GetXmlString(xmlResponse);

public string GetXmlString(XmlDocument doc)
{
	StringWriter sw = new StringWriter();
	XmlTextWriter xw = new XmlTextWriter(sw);
	doc.WriteTo(xw);
	return sw.ToString();
}

Open in new window

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thanks crisco96.

It is not working. When I implement this
"XML parsing: line 1, character 38, unable to switch the encoding"

The code you posted above & xmlDocument.InnerXML - both are returning the same result - the XML
When I tried your code & xmlDocument.InnerXML - both are returning the above mentioned error

Any idea ?
Raj
0
 
crisco96Commented:
So where is the error happening? Is it happening in my function or is it happening when you try to send the xml to the database?

Try changing the second line of my function to:

            XmlTextWriter xw = new System.Xml.XmlTextWriter(sw, System.Text.Encoding.Unicode);

So now you'll have
public string GetXmlString(XmlDocument doc)
{
    StringWriter sw = new StringWriter();
    XmlTextWriter xw = new System.Xml.XmlTextWriter(sw, System.Text.Encoding.Unicode);
    doc.WriteTo(xw);
    return sw.ToString();
}

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rajkumar GsSoftware EngineerAuthor Commented:
@crisco96,

This error <b>"XML parsing: line 1, character 38, unable to switch the encoding"</b> is happening NOT in your function, but while save to database - myCommand.ExecuteNonQuery();

When I debug and checked, myCommand.Parameters["@RequestXML"].Value is having the right XML.

I tried the suggestion you posted just now. It is crashing - Please click and view the attachment.

Raj
XMLSaveError.JPG
0
 
crisco96Commented:
Looking at this website: http://geekswithblogs.net/vkamat/archive/2006/01/20/66561.aspx

I think I've found a better solution, remove my function and do the following:
 myCommand.Parameters["@RequestXML"].Value = new SqlXml(new XmlTextReader(xmlRequest, XmlNodeType.Document, null));
 myCommand.Parameters["@ResponseXML"].Value = new SqlXml(new XmlTextReader(xmlResponse, XmlNodeType.Document, null));

Open in new window

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
I changed the datatype of Stored Procedure from XML to NVARCHAR(2000) and tried with the function you posted in http:#33743356 

Same crash - "XML parsing: line 1, character 38, unable to switch the encoding"
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Ok. I will try your last suggestion now - http:#33743690 
Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Re: http:#33743690

Oops! It is also crashing! - See attachment

It is because the first parameter of XmlTextReader is expecting stream, but we are passing XMLDocument.
XmlTextReader(xmlRequest, XmlNodeType.Document, null));

Raj

XMLSaveError-2.JPG
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
I think there is a good news. I will be back soon.!

Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Re: Your comment - http:#33743690

Can you plz tell me, from the link you mentioned in that comment, where you got that code ?

Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Eventhough the code you posted in http:#33743690 not worked, I got the working code from the link you posted.
http://geekswithblogs.net/vkamat/archive/2006/01/20/66561.aspx

In that link, there is a comment starting with "This worked for me", right ? That's it :)

Attached one is the suggestion that worked out!
Raj
StringReader transactionXml = new StringReader(myXmlString);
XmlTextReader xmlReader = new XmlTextReader(transactionXml);
SqlXml sqlXml = new SqlXml(xmlReader);

// Then use the sqlXml in your insert/update statement

Open in new window

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
When checked database, the XML's top portion where the encoding is specifying is not there.
So I think that code is filtering out first line and passing the remaining XML code that SQL Server accepts.

Raj
<?xml version="1.0" encoding="utf-8"?>

Open in new window

0
 
crisco96Commented:
Alright I think this will do what we need:

 myCommand.Parameters["@RequestXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(GetXmlString(xmlRequest), System.Xml.XmlNodeType.Document, null));
 myCommand.Parameters["@ResponseXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(GetXmlString(xmlResponse), System.Xml.XmlNodeType.Document, null));

public string GetXmlString(XmlDocument doc)
{
	StringWriter sw = new StringWriter();
	XmlTextWriter xw = new XmlTextWriter(sw);
	doc.WriteTo(xw);
	return sw.ToString();
}

Open in new window

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
I have a doubt.

What is the difference between the result that the function you posted above returns ?
with
xmlResponse.InnerXml ?

I mean
GetXmlString(xmlResponse)   Vs  xmlResponse.InnerXml

I have mentioned about this in my comment - http:#33743442

Thanks!
Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
If there is no difference, I can simplify the code like attached, right ? (Without using the function)

Raj

myCommand.Parameters["@RequestXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(xmlRequest.InnerXml, System.Xml.XmlNodeType.Document, null));

Open in new window

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Your code using function and without function with .innerXML works in the same manner!

Thanks great for your help
Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Great dude!! - You hit it at last :)

So now I have two solutions :-)
Solution #1:- http:#33744186
Solution #2:- http:#33744067

Raj
0
 
crisco96Commented:
I think #33744380 would work just fine. To be sure try saving the xml to the database then try loading it back into an xml document from the database. If that all works fine you should be good to go.
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
I will check reloading XML from database tomorrow and let you know.

Thanks
Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
What I understood is that if we try to save an XML to SQL Server, that contains the line that specifies the encoding
<?xml version="1.0" encoding="utf-8"?>
SQL Server WILL NOT allow to save it in database. We need to do some type of casting to convert the XML in a format that is acceptable by SQL Server (ie., removing the encoding line)

The datatype of parameters for RequestXML & ResponseXML used in C# code and Stored Procedure is XML

C# Code:-
// @RequestXML      XML
myCommand.Parameters.Add(new SqlParameter("@RequestXML", SqlDbType.Xml));
myCommand.Parameters["@RequestXML"].Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(xmlRequest.InnerXml, System.Xml.XmlNodeType.Document, null));

Stored Procedure:-
ALTER PROCEDURE [dbo].[uspS_WorkspeedXML]
(
      @WorkspeedTransDetailsID      BIGINT,
      @RequestXML            XML,
      @RequestTime            DATETIME,
      @ResponseXML            XML,
      @ResponseTime            DATETIME
)
AS
...

I have also tested it by reading from database back and show it in webpage.

@crisco96,
Highly appreciating your assistance that helped me to solve it out :-)

Thanks Again
Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
THIS IS ANOTHER SOLUTION - http:#33744067

Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now