Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP .NET
Hi,
I am using c# asp .net as front end and ms sql2005 as backend. I want to convert datas in a dataset to xml format and store it to a string. Then pass this string as a parameter of a stored procedure and store datas in respective columns of an sql table.
Posted 5-Jul-11 21:01pm
N!dh!sh690
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this
 
DataTable dt = new DataTable();
        dt.Columns.Add("roomCode");
        dt.Columns.Add("roomNo");
        dt.Columns.Add("hotelCode");
 

 
        DataRow dr;
 
        dr = dt.NewRow();
        dr["roomCode"] = "1";
        dr["roomNo"] = "MKavs";
        dr["hotelCode"] = "222";
 

 
        dt.Rows.Add(dr);
 
        dr = dt.NewRow();
        dr["roomCode"] = "2";
        dr["roomNo"] = "232";
        dr["hotelCode"] = "33";
 

        dt.Rows.Add(dr);
 
        DataSet ds = new DataSet();
        ds.Tables.Add(dt);
 
        XmlElement xE = (XmlElement)Serialize(ds);
        string strXml = xE.OuterXml.ToString();
 
public XmlElement Serialize(object transformObject)
        {
            XmlElement serializedElement = null;
            try
            {
                MemoryStream memStream = new MemoryStream();
                XmlSerializer serializer = new XmlSerializer(transformObject.GetType());
                serializer.Serialize(memStream, transformObject);
                memStream.Position = 0;
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(memStream);
                serializedElement = xmlDoc.DocumentElement;
            }
            catch (Exception SerializeException)
            {
 
            }
            return serializedElement;
        }
  Permalink  
v2
Comments
Nidhish Chandran at 6-Jul-11 3:26am
   
That much works fine. But how can i enter contents in the string 'strXml' to my sql table
2irfanshaikh at 6-Jul-11 3:36am
   
I think the link given below will help you:
 
http://syntaxhelp.com/SQLServer/OPENXML
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
try this code below
DataSet ds = GetDataSet();
String xmlString=String.Empty;
foreach (DataTable dt in ds.Tables)
{
  xmlString += ConvertDataTableToXML(dt);
}
public static String ConvertDataTableToXML(DataTable tableToExport)
        {
            StringBuilder formattedXML = new StringBuilder();
            XmlDocument doc = new XmlDocument();
            XmlNode node = doc.CreateNode(XmlNodeType.Element, string.Empty, "root", null);
            DataColumnCollection dtColumns=tableToExport.Columns;
 
            foreach (DataRow dataItem in tableToExport.Rows)
            {
                XmlElement element = doc.CreateElement("data");
                foreach (DataColumn thisColumn in dtColumns)
                {
                    object value = dataItem[thisColumn];
                    XmlElement tmp = doc.CreateElement(thisColumn.ColumnName);
                    if (value != null)
                    {
                        tmp.InnerXml = (RegExUtility.HasSpecialCharacter(value.ToString()) ? @"<![CDATA[" + value.ToString() + "]]>" : value.ToString());
                    }
                    else
                    {
                        tmp.InnerXml = string.Empty;
                    }
 
                    element.AppendChild(tmp);
                }
 
                node.AppendChild(element);
            }
 
            doc.AppendChild(node);
 
            return doc.InnerXml;
        }
 
 
Hope this will help.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CPallini 385
1 BillWoodruff 334
2 Sergey Alexandrovich Kryukov 333
3 George Jonsson 285
4 OriginalGriff 267
0 OriginalGriff 5,095
1 CPallini 4,285
2 Sergey Alexandrovich Kryukov 3,767
3 George Jonsson 2,941
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 6 Jul 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100