8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Parse XML Documents

There is an update to this article (here) that is relevant from Oracle 9i onward. Please use that article in preference to this, unless you are still using Oracle 8i.

Related articles.

The use of XML for data transfer has increased dramatically over the last few years. Since most applications still rely on structured relational data it is necessary to explode the non-structured XML data into relations tables. Oracle provides several APIs to do this including the XDK for PL/SQL which is the focus of this article. I shall present a simple example of using the XDK for PL/SQL to load employee records into the EMP table.

First, download and install the latest copy of the XDK for PL/SQL.

The XDK for PL/SQL API must be loaded into the relevant schema by running the folowing script.

$ORACLE_HOME/xdk/plsql/parser/bin/load.sql

Next we create the EMP table which is the final destination of the data.

CREATE TABLE emp (
  empno     NUMBER(4),
  ename     VARCHAR2(10),
  job       VARCHAR2(9),
  mgr       NUMBER(4),
  hiredate  DATE,
  sal       NUMBER(7, 2),
  comm      NUMBER(7, 2),
  deptno    NUMBER(2)
);

The source of the data is the emp.xml document. Extracting the data from the document involves several steps. First the XML document must be parsed and a DOMDocument created from it. Once the DOMDocument is created the parser is no longer needed so it's resources can be freed.

v_parser := xmlparser.newParser;

xmlparser.parse(v_parser, '/tmp/emp.xml');
v_doc := xmlparser.getDocument(v_parser);

xmlparser.freeParser(v_parser);

Next the XPATH syntax is used to get a DOMNodeList containing all the EMP nodes.

v_nl := xslprocessor.selectNodes(xmldom.makeNode(v_doc),'/EMPLOYEES/EMP');

Once we have the DOMNodeList we can loop through it getting the values for each node in turn. The values are returned using the XPATH sytax and placed in a table collection.

v_n := xmldom.item(v_nl, cur_emp);
t_tab(t_tab.last).empno := xslprocessor.valueOf(v_n,'EMPNO');

With all the data retrieved into the table collection the inserts can be performed. Putting it all together we get the following.

DECLARE
  v_parser  xmlparser.Parser;
  v_doc     xmldom.DOMDocument;
  v_nl      xmldom.DOMNodeList;
  v_n       xmldom.DOMNode;
  
  TYPE tab_type IS TABLE OF emp%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN

  -- Create a parser.
  v_parser := xmlparser.newParser;

  -- Parse the document and create a new DOM document.
  xmlparser.parse(v_parser, '/tmp/emp.xml');
  v_doc := xmlparser.getDocument(v_parser);

  -- Free resources associated with the Parser now it is no longer needed.
  xmlparser.freeParser(v_parser);

  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  v_nl := xslprocessor.selectNodes(xmldom.makeNode(v_doc),'/EMPLOYEES/EMP');

  -- Loop through the list and create a new record in a tble collection
  -- for each EMP record.
  FOR cur_emp IN 0 .. xmldom.getLength(v_nl) - 1 LOOP
    v_n := xmldom.item(v_nl, cur_emp);
    
    t_tab.extend;
    
    -- Use XPATH syntax to assign values to he elements of the collection.
    t_tab(t_tab.last).empno     := xslprocessor.valueOf(v_n,'EMPNO');
    t_tab(t_tab.last).ename     := xslprocessor.valueOf(v_n,'ENAME');
    t_tab(t_tab.last).job       := xslprocessor.valueOf(v_n,'JOB');
    t_tab(t_tab.last).mgr       := TO_NUMBER(xslprocessor.valueOf(v_n,'MGR'));
    t_tab(t_tab.last).hiredate  := TO_DATE(xslprocessor.valueOf(v_n,'HIREDATE'), 'DD-MON-YYYY');
    t_tab(t_tab.last).sal       := TO_NUMBER(xslprocessor.valueOf(v_n,'SAL'));
    t_tab(t_tab.last).comm      := TO_NUMBER(xslprocessor.valueOf(v_n,'COMM'));
    t_tab(t_tab.last).deptno    := TO_NUMBER(xslprocessor.valueOf(v_n,'DEPTNO'));
  END LOOP;

  -- Insert data into the real EMP table from the table collection.
  -- Form better performance multiple collections should be used to allow
  -- bulk binding using the FORALL construct but this would make the code
  -- too long-winded for this example.
  FOR cur_emp IN t_tab.first .. t_tab.last LOOP
    INSERT INTO emp
    (empno,          
     ename,          
     job,            
     mgr,            
     hiredate,       
     sal,            
     comm,           
     deptno)
    VALUES
    (t_tab(cur_emp).empno,
     t_tab(cur_emp).ename,
     t_tab(cur_emp).job,
     t_tab(cur_emp).mgr,
     t_tab(cur_emp).hiredate,
     t_tab(cur_emp).sal,
     t_tab(cur_emp).comm,
     t_tab(cur_emp).deptno);
  END LOOP;

  COMMIT;
  
  -- Free any resources associated with the document now it
  -- is no longer needed.
  xmldom.freeDocument(v_doc);

END;
/

In an attempt to make the code a little shorter I've avoided multiple collections required for bulk binding of inserts. In a production environment it would be advisable to consider bulk binding.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.