How to Insert XML in Oracle Database from ASP.NET

Do you know why and how we insert xml in database ?

Here is the answer. If you want to insert multiple rows and multiple column ( like DataTable)  in a single execution.Then we insert using xml. Now we will see how to insert xml…

Conversion Datatable to XML

First of all i will be using Microsoft Visual Studio as an web development tool through which we will convert Datatable  to  XML . Lets see the code.

Datatable dt;
StringWriter strREC = new StringWriter();
dt.TableName = "Recovery";
dt.WriteXml(strREC);

Xml made by WriteXml

<DocumentElement>
 <Recovery>
 <RCOL4>Programmer</RCOL4>
 <RCOL5>Dehradun</RCOL5>
 <RCOL6>Uttarakhand</RCOL6>
</Recovery>
</DocumentElement>

I have a datatable name as dt and a StringWriter strREC used for XML conversion now you will set datatable name as Recovery . In next line i will convert datatable to XML by WriteXml method  & strREC is an XML for insertion . I think you got till this point.

Insertion into Database

Now you will create the package in that you will create one procedure with name procedure1.

Create package & procedure

 PROCEDURE PROCEDURE1 (PXMLREC SYS.XMLTYPE,PMSG OUT VARCHAR2)
IS
PCOL1 Varchar2;
PCOL2 Varchar2;
PCOL3 Varchar2;
BEGIN
PMSG := 'Y';
Select id,firstname,lastname into PCOL1, PCOL2, PCOL3 from employee where employeecode='xyz';
INSERT INTO TABLE1 (COL1, COL2,COL3,
COL4, COL5, COL6)
SELECT PCOL1, PCOL2, PCOL3,
TBL.EXTRACT ('//Recovery/RCOL4/text()').GETSTRINGVAL(),
TBL.EXTRACT ('//Recovery/RCOL5/text()').GETSTRINGVAL (),
TBL.EXTRACT ('//Recovery/RCOL6/text()').GETSTRINGVAL ()
FROM TABLE (XMLSEQUENCE (PXMLREC.EXTRACT ('//DocumentElement/Recovery') ) ) TBL;
END;

In this procedure we will use PXMLREC SYS.XMLTYPE variable for xml & select query for accessing XML data and insert it in table1.

ASP.NET Code for insertion

OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["str"].ConnectionString);
OracleCommand cmd = new OracleCommand();
con.Open();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "package1.procedure1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("PXMLREC", OracleDbType.XmlType).Value = strREC;
cmd.Parameters.Add("PMSG", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();

Here you will write in asp.net code for inserting the xml using package.procedure by establishing connection by con connectionstring for oracle database.And passing xml parameter by oracle command and value strREC .Executenonquery for executing the procedure.

table

 

 

 

 

Finally the xml strREC is inserted by you in table.

 

About the Author

Prashant Goswami

I am the blog writer who will teach how to love programming. My experience in programming is about 6 years. And i am the lover of programming , so i will make you people.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: