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
Let's talk 