How to import XML data in c#

Rajkumar R

15y2.5k.NET

i want to import xml data using C# dot net to the SQLSERVER 2000
any one post solution for this
I have an XML file like this below i mentioned

-
-
   452
-
   COICLT0001
   VIL
   COIVIL0001
   SHG1
   10120aa
   BALA
   RF
   500
   600

-
   453
-
   COICLT0001
   VIL
   COIVIL0001
   SHG2
   10120BB
   BALAJI
   DL
   300
   500

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak15y ago
Accepted answer
Hi Raj kumar,

Try this...

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

    Untitled Page













using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;



    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(connStr);
        DataSet reportData = new DataSet();
        reportData.ReadXml(Server.MapPath("test.xml"));

        SqlBulkCopy sbc = new SqlBulkCopy(con);
        sbc.DestinationTableName ="employee";
        sbc.ColumnMappings.Add("TALLY_REC_ID_CODE", "empid");
        sbc.ColumnMappings.Add("CLUSTER_ID", "empname");

        sbc.ColumnMappings.Add("UNIT_TYPE", "empaddress");
        sbc.ColumnMappings.Add("VPRC_ID", "empcity");
        sbc.ColumnMappings.Add("SHG_ID", "empstate");

        sbc.ColumnMappings.Add("PIP_NO", "empzip");
        sbc.ColumnMappings.Add("NAME_OF_THE_MEMBER", "emplic");
        sbc.ColumnMappings.Add("TYPE_OF_LOAN", "empstatus");

        sbc.ColumnMappings.Add("PRINCIPAL", "empp");
        sbc.ColumnMappings.Add("INTEREST", "empi");
        con.Open();
        sbc.WriteToServer(reportData.Tables[0]);

        con.Close();
        Label1.Text = "Records inserted successfully";

    }
}

Thanks
0
Rajkumar R15y ago
reportData.ReadXml(Server.MapPath("report.xml"));
instead of the above code i want to select and upload the xml.file. but here it is statically declared as report.xml
My need is, i m having an fileupload control when i select a file then i want to insert the corresponding XML file into the table
0
Rajkumar R15y ago
Thanks a lot for ur response satyapriya,
0
Rajkumar R15y ago
i have solved the error, but one more help please
i want to browse the file and to upload that how can select the file using fileupload control and to upload that xml file in sqlserver
0
Rajkumar R15y ago
i have an FileUpload control from this i will brose my xml file then i want that into sqlserver 2000 using C# post solution for this
0
Datta Kharad15y ago
Hi
You can try this code..

class Program
{
private static void SaveXmlToDatabase(DbConnection connection,
XmlDocument xmlToSave)
{
String sql = "INSERT INTO xmlTable(xmlColumn) VALUES (@xml)";

using (DbCommand command = connection.CreateCommand())
{
XPathNavigator nav = xmlToSave.CreateNavigator();
string xml = nav.SelectSingleNode("/catalog/cd[title='Manowar']").InnerXml;

command.CommandText = sql;
command.Parameters.Add(
new SqlParameter("@xml", SqlDbType.Xml)
{Value = new SqlXml(new XmlTextReader(xml
, XmlNodeType.Document, null)) });

DbTransaction trans = connection.BeginTransaction();
try
{
command.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
}

static void Main(string[] args)
{
XmlDocument document = new XmlDocument();
document.Load(args.First());

SqlConnection connection = new SqlConnection(
"Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;");

SaveXmlToDatabase(connection, document);

connection.Close();
}
}
0