XML handling using C#

tusharkale · June 19th, 2008, 11:14 AM

hi,

I want to read xml stored in database (nvarchar column) and mask some of the field (modify the data) of it and again stored the masked xml in other table. How can I achieve this in fastest and most efficient way using C#?

tusharkale · June 19th, 2008, 11:16 AM

any doc/tutorial resources related to this would be helful.

thanks

joefawcett · June 19th, 2008, 11:19 AM

Which database, SQL Server 2005 can probably handle all this server-side which would normally be the most efficient. Otherwise you can read the data using a SqlCommand and ExecuteXmlReader which can then be processed as a stream or via XSLT. Or if you want to be cutting edge you could also do this with LINQ to XML.

--

Joe (Microsoft MVP - XML)

tusharkale · June 19th, 2008, 11:19 AM

xml is stored in ntext column.

tusharkale · June 20th, 2008, 08:38 AM

SOmehow I am not able to get it.
I have a table with two columns. tbl1 (id int, xml_doc ntext)
data is stored in xml format in (xml_doc) ntext column.
here is code which I have written

    mySqlCommand.CommandText = "SELECT TOP 1 xml_doc from tbl1";
    mySqlConnection.Open();
    XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader();
    myXmlReader.Read();
      while (!myXmlReader.EOF) {
        Response.Write(myXmlReader.ReadOuterXml());

What should be my command to select xml_doc? and how do I change one of the parameter and saved it again as xml in table.

Thanks for help!

samjudson · June 20th, 2008, 08:45 AM

http://support.microsoft.com/kb/316016

ExecuteXmlReader is used for returning the results of an SQL XML query - you are not performing an SQL XML query - you are mealy performing a normal SQL query which just happens to return a text value which you know is XML.

Code:

mySqlCommand.CommandText = "SELECT TOP 1 xml_doc from tbl1";
mySqlConnection.Open();
SqlDataReader reader = mySqlCommand.ExecuteReader();
if( reader.Read() )
{
  Response.Write(reader.GetString(0));

  XmlDocument doc = new XmlDocument();
  doc.LoadXml(reader.GetString(0));
}

If you want to use ExecuteXmlReader the look into the "FOR XML" TSQL statements as mentioned in the above KB article.

/- Sam Judson : Wrox Technical Editor -/

joefawcett · June 20th, 2008, 08:50 AM

Sam is correct but you still haven't answered the question I posed in first response.

--

Joe (Microsoft MVP - XML)

tusharkale · June 20th, 2008, 08:54 AM

Hi thanks for reply.
When I tried using the info given on link u provided I am getting follwong error
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Switch from current encoding to specified encoding not supported. Error processing resource 'http://localhost:2308/Default....

<?xml version="1.0" encoding="utf-16"?><ISDA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http:...

And even if this problem gets solved, How do I modify one of value of node? and saved in database again.

tusharkale · June 20th, 2008, 08:55 AM

YEs I am using sql server 2005 database.

tusharkale · June 20th, 2008, 09:00 AM

Reading xml doc stored in ntext column is not my priority basically I want to mask (change some of the values of node in xml). So first reading xml file storing it somewhere modifying it n then updating database..if there is any other way of doing it that would also be fine.

I want to do this in an most efficient way.

Thanks.