Solved

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

Posted on 2010-09-23
22
2,284 Views
Last Modified: 2012-05-10
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
Comment
Question by:Rajkumar Gs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 5
22 Comments
 
LVL 15

Expert Comment

by:crisco96
ID: 33743356
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743442
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
 
LVL 15

Expert Comment

by:crisco96
ID: 33743544
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743624
@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
 
LVL 15

Assisted Solution

by:crisco96
crisco96 earned 500 total points
ID: 33743690
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743782
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743798
Ok. I will try your last suggestion now - http:#33743690 
Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743915
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33743925
I think there is a good news. I will be back soon.!

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744019
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744067
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744091
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
 
LVL 15

Assisted Solution

by:crisco96
crisco96 earned 500 total points
ID: 33744186
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744312
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744380
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744399
Your code using function and without function with .innerXML works in the same manner!

Thanks great for your help
Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33744407
Great dude!! - You hit it at last :)

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

Raj
0
 
LVL 15

Assisted Solution

by:crisco96
crisco96 earned 500 total points
ID: 33745021
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33745556
I will check reloading XML from database tomorrow and let you know.

Thanks
Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 0 total points
ID: 33751658
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
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33751673
THIS IS ANOTHER SOLUTION - http:#33744067

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 33753745
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to filter by key press ? 6 57
Need a modeling tool 2 43
Retreiving SOAP FAULT messages using classical ASP 14 31
Calculating percentage 2 32
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question