locked
From xml to oracle RRS feed

  • Question

  • User-1578974752 posted

    If I get the file in xml format. How can I insert into oracle database. Appreciate the help

    Wednesday, July 10, 2019 8:31 AM

Answers

  • User-719153870 posted

    Hi shsu,

    The general idea of importing XML files into Oracle is:

    1.    Import the XML file into Dataset and get a Datatable to load all the data needed to be imported into the database.

    2.    Create table in Oracle using ADO.NET according to the XML file name.

    3.    Insert the data in Datatable row by row into the table in the database.

    You can refer to below codes in a webform application:

    ASPX:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:TextBox ID="textBoxXml" runat="server"></asp:TextBox>
                <asp:Label ID="Message" runat="server" Text="Label"></asp:Label>
                <asp:Button ID="buttonSubmit" runat="server" Text="Submit" OnClick="buttonSubmit_Click" />
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </div>
        </form>
    </body>
    </html>

    CS:

    using System;
    using System.Data;
    using System.Data.OracleClient;
    using System.Web;
    
    public partial class XMLToOracleDemo : System.Web.UI.Page
        {
            protected DataSet dataSetXml;
            protected DataTable dataTableXml;
            protected string tableName;
            protected string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["dbConString"].ConnectionString;
    
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void buttonSubmit_Click(object sender, EventArgs e)
            {
                OracleConnection conn = new OracleConnection(strConn);
                conn.Open();
                Message.Text = "";
                dataSetXml = new DataSet();
                dataTableXml = new DataTable();
                try
                {
                    dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));
                    dataTableXml = dataSetXml.Tables[0];
                    tableName = textBoxXml.Text.Substring(0, textBoxXml.Text.Length - 4);
                    GridView1.DataSource = dataTableXml.DefaultView;
                    GridView1.DataBind();
                }
                catch
                {
                    Message.Text = "Can not open Xml file or Xml wrong format!";
                }
                if (dataTableXml.Rows.Count > 0)
                {
                    TableCheck();//create a table
                    TableInsert();//insert data into table in database
                }
                conn.Close();
            }
    
            private void TableCheck()
            {
                OracleConnection conn = new OracleConnection(strConn);
                try
                {
                    conn.Open();
                    int count = 0;
                    DataTable schemaTable = conn.GetSchema("TABLES", new string[] { "SCOTT", tableName });
                    string sqlCmd = "";
                    if (schemaTable.Rows.Count == 1)
                    {
                        sqlCmd = " create table " + tableName + "( ";
                        for (int i = 0; i < dataTableXml.Columns.Count; i++)
                        {
                            sqlCmd += dataTableXml.Columns[i].ColumnName.ToString() + " varchar2(100),";
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + " )";
                        OracleCommand createcmd = new OracleCommand();
                        createcmd.Connection = conn;
                        createcmd.CommandText = sqlCmd;
                        createcmd.ExecuteNonQuery();
                    }
                }
                catch
                {
                    Message.Text = "Wrong SQL connection string!";
                }
                finally
                {
                    conn.Close();
                }
            }
    
            private void TableInsert()
            {
                OracleConnection conn = new OracleConnection(strConn);
                try
                {
                    conn.Open();
                    foreach (DataRow dr in dataTableXml.Rows)
                    {
                        string sqlCmd = "Insert Into " + tableName + " (";
                        for (int i = 0; i < dataTableXml.Columns.Count; i++)
                        {
                            sqlCmd += dataTableXml.Columns[i].ColumnName.ToString() + ",";
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + " ) Values ( ";
                        for (int i = 0; i < dataTableXml.Columns.Count; i++)
                        {
                            sqlCmd += "'" + dr[i].ToString() + "',";
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + " )";
                        OracleCommand cmd = new OracleCommand(sqlCmd, conn);
                        cmd.ExecuteNonQuery();
                    }
                    Message.Text = "Xml file has beed inserted into Oracle!";
                }
                catch
                {
                    Message.Text = "Error in inserting data into Oracle!";
                }
                finally
                {
                    conn.Close();
                }
            }
        }

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 11, 2019 2:31 AM
  • User-18289217 posted

    shsu

    If I get the file in xml format. How can I insert into oracle database. Appreciate the help

    If you have a table with a column of XMLTYPE datatype, you could save the XML file AS IS. Voila!

    Oracle XMLTYPE Data Type

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 11, 2019 8:16 AM

All replies

  • User-719153870 posted

    Hi shsu,

    The general idea of importing XML files into Oracle is:

    1.    Import the XML file into Dataset and get a Datatable to load all the data needed to be imported into the database.

    2.    Create table in Oracle using ADO.NET according to the XML file name.

    3.    Insert the data in Datatable row by row into the table in the database.

    You can refer to below codes in a webform application:

    ASPX:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:TextBox ID="textBoxXml" runat="server"></asp:TextBox>
                <asp:Label ID="Message" runat="server" Text="Label"></asp:Label>
                <asp:Button ID="buttonSubmit" runat="server" Text="Submit" OnClick="buttonSubmit_Click" />
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </div>
        </form>
    </body>
    </html>

    CS:

    using System;
    using System.Data;
    using System.Data.OracleClient;
    using System.Web;
    
    public partial class XMLToOracleDemo : System.Web.UI.Page
        {
            protected DataSet dataSetXml;
            protected DataTable dataTableXml;
            protected string tableName;
            protected string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["dbConString"].ConnectionString;
    
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void buttonSubmit_Click(object sender, EventArgs e)
            {
                OracleConnection conn = new OracleConnection(strConn);
                conn.Open();
                Message.Text = "";
                dataSetXml = new DataSet();
                dataTableXml = new DataTable();
                try
                {
                    dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));
                    dataTableXml = dataSetXml.Tables[0];
                    tableName = textBoxXml.Text.Substring(0, textBoxXml.Text.Length - 4);
                    GridView1.DataSource = dataTableXml.DefaultView;
                    GridView1.DataBind();
                }
                catch
                {
                    Message.Text = "Can not open Xml file or Xml wrong format!";
                }
                if (dataTableXml.Rows.Count > 0)
                {
                    TableCheck();//create a table
                    TableInsert();//insert data into table in database
                }
                conn.Close();
            }
    
            private void TableCheck()
            {
                OracleConnection conn = new OracleConnection(strConn);
                try
                {
                    conn.Open();
                    int count = 0;
                    DataTable schemaTable = conn.GetSchema("TABLES", new string[] { "SCOTT", tableName });
                    string sqlCmd = "";
                    if (schemaTable.Rows.Count == 1)
                    {
                        sqlCmd = " create table " + tableName + "( ";
                        for (int i = 0; i < dataTableXml.Columns.Count; i++)
                        {
                            sqlCmd += dataTableXml.Columns[i].ColumnName.ToString() + " varchar2(100),";
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + " )";
                        OracleCommand createcmd = new OracleCommand();
                        createcmd.Connection = conn;
                        createcmd.CommandText = sqlCmd;
                        createcmd.ExecuteNonQuery();
                    }
                }
                catch
                {
                    Message.Text = "Wrong SQL connection string!";
                }
                finally
                {
                    conn.Close();
                }
            }
    
            private void TableInsert()
            {
                OracleConnection conn = new OracleConnection(strConn);
                try
                {
                    conn.Open();
                    foreach (DataRow dr in dataTableXml.Rows)
                    {
                        string sqlCmd = "Insert Into " + tableName + " (";
                        for (int i = 0; i < dataTableXml.Columns.Count; i++)
                        {
                            sqlCmd += dataTableXml.Columns[i].ColumnName.ToString() + ",";
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + " ) Values ( ";
                        for (int i = 0; i < dataTableXml.Columns.Count; i++)
                        {
                            sqlCmd += "'" + dr[i].ToString() + "',";
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + " )";
                        OracleCommand cmd = new OracleCommand(sqlCmd, conn);
                        cmd.ExecuteNonQuery();
                    }
                    Message.Text = "Xml file has beed inserted into Oracle!";
                }
                catch
                {
                    Message.Text = "Error in inserting data into Oracle!";
                }
                finally
                {
                    conn.Close();
                }
            }
        }

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 11, 2019 2:31 AM
  • User-18289217 posted

    shsu

    If I get the file in xml format. How can I insert into oracle database. Appreciate the help

    If you have a table with a column of XMLTYPE datatype, you could save the XML file AS IS. Voila!

    Oracle XMLTYPE Data Type

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 11, 2019 8:16 AM
  • User269602965 posted

    In addition to the above comments: an XML file can be a collection of relational tables, so in that case, not a matter of reading and saving to one table.

    Saturday, July 13, 2019 2:52 AM