learned/learning/to learn RSS 2.0
# Thursday, 16 April 2009

Here is a method to import/send XML to Oracle and thus we need not call the database repeatedly for mass insert, update etc. DBMS_XMLSTORE has been used here. There might be some other good way to do this, if so, please let me know. 

 

Here you go:

 

1. Importing Excel from .NET interface to Oracle. Just to get some xml, I have used an excel sheet here, you can replace it with your xml data. 

 

COL1

COL2

COL3

1

Aaa

abc

2

Bbb

pqr

3

Aaa

Abcdef

4

ddd

pqrst

 

 

.NET Code

 

 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Excel\\excel.xls;Extended Properties=Excel 8.0;";

 OleDbDataAdapter daImportExcel = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

 

 DataSet dsExcelData = new DataSet();

 daImportExcel.Fill(dsExcelData, "TABLE_NAME");

 

OracleParameter parameter = new OracleParameter();

parameter = new OracleParameter("XML_CLOB", OracleDbType.Clob);

parameter.Direction = ParameterDirection.Input;

parameter.Value = dsExcelData.GetXml();

 

OracleCommand objCommandImport = new OracleCommand();

objCommandImport.Connection = objConnection;

objCommandImport.CommandType = CommandType.StoredProcedure;

objCommandImport.CommandText = "BIMS.XML_PARSER";

 

objCommandImport.Parameters.Add(parameter);

objCommandImport.ExecuteNonQuery();

 

 

Note: I am passing XML as CLOB.

Oracle Procedure

 

I am inserting the dataset, I mean the XML into table XML_TESTING. Here COL1 is integer and other 2 columns are varchar.

 

CREATE OR REPLACE PROCEDURE XML_PARSER

(

    XML_CLOB    IN CLOB

)

AS

 

INSERT_CONTEXT  DBMS_XMLSTORE.CTXTYPE;

ROW_COUNT       NUMBER;

 

 

BEGIN

 

 

    INSERT_CONTEXT := DBMS_XMLSTORE.NEWCONTEXT('XML_TESTING');

    DBMS_XMLSTORE.SETROWTAG(INSERT_CONTEXT, 'TABLE_NAME');

    DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(INSERT_CONTEXT); 

    DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL1');

    DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL2');

    DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL3'); 

    ROW_COUNT := DBMS_XMLSTORE.INSERTXML(INSERT_CONTEXT, XML_CLOB);

   

    DBMS_XMLSTORE.CLOSECONTEXT(INSERT_CONTEXT);   

 

END XML_PARSER;

 

Note:

Names of the columns that are inserted and of the underlying database table should be same. By default, XML documents are expected to identify rows with the <ROW> tag. This is the same default used by DBMS_XMLGEN when generating XML. This may be overridden by calling the setRowTag function. Here ‘TABLE_NAME'’ has been set as rowtag as it is our table name.

 

Drawback:

.NET is not allowing to use GetXml in huge datasets. If somebody has a solution for it, please let me know.

 

You can understand better about DBMS_XMLSTORE from this article - http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10790/xdb_dbmstore.htm#CACEJGEH

Thursday, 16 April 2009 18:08:18 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
.NET | Oracle
Navigation
Archive
<2009 April>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Gokulnath
Sign In
Statistics
Total Posts: 41
This Year: 0
This Month: 0
This Week: 0
Comments: 47
Themes
Pick a theme:
All Content © 2017, Gokulnath
DasBlog theme 'Business' created by Christoph De Baene (delarou)