You must Sign In to post a response.
  • Category: ASP.NET

    How to export datas from xml to sqldatabase?

    hi frnds,
    i want to export xml data to the sql database using c#.net
    I have a xml data on fileupload i need to insert xml datas to the sql database table..please anyone help me to complete my task.

    thanks in advance

    prem
  • #721538
    Hi,

    You can use the below SQL INSERT statement to Insert XML data into your SQL database.

    INSERT INTO TestTable
    (fileUploadName, xmlDATA)
    VALUES
    ('FileName.xml', CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>', 2);


    Hope the above is helpful to you.

  • #721544
    Hi Prem.
    Following code will help in acheiving your goal.
    Declare @xmlData XML

    Select @xmlData =
    CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'filePath\fileName.xml',SINGLE_BLOB) AS X

    SET ARITHABORT ON

    Insert into [tableName]
    (
    Field1, field2, Field3
    )

    Select
    x.value('Field1[1]','VARCHAR(50)') AS Field1,
    x.value('Field2[1]','VARCHAR(50)') AS Field2,
    x.value('Field3[1]','VARCHAR(200)') AS Field3,
    From @xml.nodes('/dataroot/RootNode') PropertyFeed(P)

    Thanks

    Reena

  • #721624
    Hi,

    Refer my explanation below step by step i explain

    1) I have XML look like below


    <?xml version="1.0" encoding="utf-8" ?>
    <Employee>
    <e1>
    <eno>101</eno>
    <empname>Ravindran</empname>
    <sal>45000</sal>
    </e1>
    <e1>
    <eno>102</eno>
    <empname>James</empname>
    <sal>45000</sal>
    </e1>
    <e1>
    <eno>103</eno>
    <empname>Mike</empname>
    <sal>45000</sal>
    </e1>
    </Employee>


    2) Now i create one table in sql server

    CREATE TABLE [dbo].[emp](
    [eno] [int] NULL,
    [empname] [varchar](50) NULL,
    [sal] [bigint] NULL
    )


    3) Then i load data from XML to sql server using below code

    using System.Data;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    using System.Configuration;

    public partial class _Default : System.Web.UI.Page
    {
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();
    //Export XML data into DB using bulk copy
    protected void btnExport_Click(object sender, EventArgs e)
    {
    try
    {
    //get data from xml to dataset
    ds.ReadXml(Server.MapPath("XMLFile.xml"));
    DataTable dt = ds.Tables[0];

    //Create instance for SQL bulk copy
    SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["Con"].ToString());

    //Assign destination sql server target table name below
    sqlBulk.DestinationTableName = "emp";

    //Write to server table
    sqlBulk.WriteToServer(dt);
    }
    catch (Exception ex)
    {
    }
    }
    }

    Regards
    N.Ravindran
    Your Hard work never fails


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.