Insert XML data into Database.

The simplest way to read an XML and insert them into the database is to use an XML parser and then write them into the database through insert statements or through hibernate mapping. But that would be time consuming while dealing with huge XML files.

In this topic, lets explore a more efficient way of doing it by passing an XML into a Stored Procedure.

Ingredients:

Java, XML, XSL, Oracle Stored Procedure.

Step 1: Execute the Stored Procedure

–DBMS_XMLSave is used before Oracle 10g. DBMS_XMLSTORE is the new PL/SQL package was introduced in Oracle Database 10g
–This package knows how to insert the data from a Canonical XML into an Oracle TABLE.

create or replace
PROCEDURE “UPDATEPROC” (xmlDoc IN CLOB, tableName IN VARCHAR2, records IN NUMBER, keyColumn IN VARCHAR2) AS
updCtx DBMS_XMLSave.ctxType;
ROWS NUMBER;
BEGIN
updCtx := DBMS_XMLSave.newContext(tableName);
DBMS_XMLSave.setKeyColumn(updCtx,keyColumn);
DBMS_XMLSave.setBatchSize(updCtx, records);
ROWS := DBMS_XMLSave.updateXML(updCtx,xmlDoc);
DBMS_XMLSave.closeContext(updCtx);
END UPDATEPROC;
/

NOTE: To INSERT, use  DBMS_XMLSave.insertXML remove DBMS_XMLSave.setKeyColumn(…);

Step 2: Transform your XML file to a canonical XML format and then to a String Object.

java.io.Writer outWriter = new java.io.StringWriter();
javax.xml.transform.Result result = new javax.xml.transform.Stream.StreamResult(outWriter);
javax.xml.transform.TransformerFactory factory = javax.xml.transform.TransformerFactory.newInstance();
Source xsl = new StreamSource(“your xsl File”);
Transformer transformer = factory.newTransformer(xsl);
transformer.setOutputProperty(OutputKeys.INDENT, “yes”);
transformer.transform(xml, result);
String xmlString = outWriter.toString();

//The canonical XML format would look like this, for example. The ROWSET & ROW tags are mandatory and
//the other tags represent the field name of the target table.

<ROWSET>
  <ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>20-FEB-81</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>22-FEB-81</HIREDATE>
    <SAL>1250</SAL>
    <COMM>500</COMM>
    <DEPTNO>30</DEPTNO>
  </ROW>
</ROWSET>

Step 3: Insert XML into the database via Stored procedure.

//java.sql.Connection connection = <getYourConnection()>

CallableStatement cstmt = connection.prepareCall(“{call UPDATEPROC(?,?,?,?)}”);
cstmt.setString(1, xmlString);
cstmt.setString(2, <Target Table Name>);
Integer records = getNoOfRecords(xmlString);
cstmt.setInt(3, records);
cstmt.setString(4, “<your Key column>”); // Only for Update
cstmt.execute();

//Function to find the number of records in the Canonical XML

private Integer getNoOfRecords(String xmlString) throws Exception{
javax.xml.parsers.DocumentBuilder builder = javax.xml.parsers.DocumentBuilderFactory.newInstance().newDocumentBuilder();
org.w3c.dom.Document document = builder.parse(new InputSource(new StringReader(xmlString)));
org.w3c.dom.Element docElement = document.getDocumentElement();
org.w3c.dom.NodeList list = docElement.getElementsByTagName(“ROW”);
return list.getLength();
}

Ref: http://www.oracle.com/technetwork/issue-archive/2005/05-sep/o55xml-101951.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s