Home » Developer & Programmer » JDeveloper, Java & XML » Loading XML documents into relational tables (Oracle 10g r2)
Loading XML documents into relational tables [message #314383] Wed, 16 April 2008 08:52
seagull
Messages: 1
Registered: April 2008
Location: UK
Junior Member
We are receiving data from another system using AQ streams. The payloads are XML documents, the structure of the various XML fragments closely match the target relational tables. The AQ part works ok but we need to extract the data from the XML document and insert/update the data in the database.

I am new to XML and have tried to read the Oracle documentation but my brain now hurts.

Here is the proposed solution

1) dequeue message
2) locate XML fragment in the document to load
3) load into table using DBMS_XMLSAVE (then do some other clever stuff using PL/SQL in triggers)
4) repeat steps 1..3 as required

One problem is that the Java developers are complaining about the tag names in the XML file not conforming to standard (e.g. <FIRST_NAME>) to match the field names in the tables, they would want <firstName>.

I have found the dbms_XMLSAVE.setignorecase procedure so that the XML tags can be in mixed case but can do nothing about the actual tag names.

Here is what I have at present...

CREATE TABLE EMPLOYEES
(
  EMPLOYEE_ID    NUMBER,
  SALARY         NUMBER                         NOT NULL,
  HIRE_DATE      DATE                           NOT NULL,
  JOB_ID         VARCHAR2(20 BYTE)              NOT NULL,
  EMAIL          VARCHAR2(100 BYTE),
  DEPARTMENT_ID  NUMBER                         NOT NULL,
  FIRST_NAME     VARCHAR2(30 BYTE),
  LAST_NAME      VARCHAR2(30 BYTE)
)


DECLARE
  insctx   dbms_xmlsave.ctxtype;
  l_rows     NUMBER;
  xmldoc   CLOB
    := '<EXAMPLE>
     <ROWSET>
       <EMPLOYEE NUM="1">
         <EMPLOYEE_ID>920</EMPLOYEE_ID>
         <SALARY>1800</SALARY>
         <DEPARTMENT_ID>30</DEPARTMENT_ID>
         <HIRE_DATE>17-DEC-2002T10:30:00</HIRE_DATE>
         <last_name>Strauss</last_name>
         <EMAIL>JSTRAUSS</EMAIL>
         <JOB_ID>ST_CLERK</JOB_ID>
       </EMPLOYEE>
       <EMPLOYEE>
         <USER_NAME>Fred3</USER_NAME>       
         <employee_id>921</employee_id>
         <SALARY>2000</SALARY>
         <DEPARTMENT_ID>30</DEPARTMENT_ID>
         <hire_date>31-DEC-2004T00:00:00</hire_date>
         <LAST_NAME>Jones</LAST_NAME>
         <EMAIL>EJONES</EMAIL>
         <JOB_ID>ST_CLERK</JOB_ID>
       </EMPLOYEE>
     </ROWSET>
     </EXAMPLE>';
  xvar     XMLTYPE;
  l_str    VARCHAR2 (2000);
BEGIN
  insctx := dbms_xmlsave.newcontext ('EMPLOYEES');                                                             -- Get saved context
  dbms_xmlsave.clearupdatecolumnlist (insctx);                                                         -- Clear the update settings
  dbms_xmlsave.propagateoriginalexception (insctx, TRUE);
  dbms_xmlsave.setdateformat (insctx, 'dd-MMM-yyyy''T''HH:mm:ss');
  dbms_xmlsave.setrowtag (insctx, 'EMPLOYEE');
  dbms_xmlsave.setignorecase (insctx, dbms_xmlsave.ignore_case);

  dbms_xmlsave.setupdatecolumn (insctx, 'EMPLOYEE_ID');
  dbms_xmlsave.setupdatecolumn (insctx, 'SALARY');
  dbms_xmlsave.setupdatecolumn (insctx, 'HIRE_DATE');
  dbms_xmlsave.setupdatecolumn (insctx, 'DEPARTMENT_ID');
  dbms_xmlsave.setupdatecolumn (insctx, 'JOB_ID');
  dbms_xmlsave.setupdatecolumn (insctx, 'EMAIL');
  dbms_xmlsave.setupdatecolumn (insctx, 'LAST_NAME');

  l_rows := dbms_xmlsave.insertxml (insctx, xmldoc);
  DBMS_OUTPUT.put_line (l_rows || ' rows inserted.');
  dbms_xmlsave.closecontext (insctx);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (SQLERRM);
    ROLLBACK;
END;


Would a stylesheet help? And if so, how can I implement one? There seems to be a distinct lack of worked examples in this area. Has anyone got a simple example?

Is this the best approach or is there another route I should try? (I looked at DBMS_XMLSTORE as well but this seemed to hide some of the exceptions).




[mod-edit: added code tags; next time please do it yourself]



[Updated on: Wed, 16 April 2008 18:45] by Moderator

Report message to a moderator

Previous Topic: xml and data storage in warehouse
Next Topic: Create Directories Dynamically on Server - Permissions
Goto Forum:
  


Current Time: Sat Apr 19 03:43:16 CDT 2014

Total time taken to generate the page: 0.13881 seconds