Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: XML input to a normalized table

    We recently added a requirement onto an existing system where some clients could provide their data to us in XML format. So, we need to parse an XML file and load that data into our existing Oracle tables.

    I'm pretty much out of my element. Could someone point me in the right direction so I could get a good start?

    Thanks,
    Chuck

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Hi Chuck!

    There are about a million ways of skinning this one...

    Assuming you using 10g, look up XMLType and dbms_xmlstore. The XMLType has methods to load XML documents from URL, clob and filesystem. You can then use xmlstore.insertxml, xmlstore.updatexml and xmlstore.deletexml to apply document to your tables.

    Note that the xml must be in ROWSET/ROW format when applying the changes to your db.
    Code:
    <ROWSET>
       <ROW num="1">
          <COL>value</COL>
       </ROW>
    </ROWSET>
    In order to get it to this format, use XMLType.transform just before the xmlstore.

    You could be done within 10 lines of code.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Even better:
    google search="xmltype dbms_xmlstore oracle"
    or try
    http://blogs.ittoolbox.com/oracle/gu...d-example-5319

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    And one last thing... take care using dbms_xmlstore. There is a bug in Oracle 10.1... If you have a ' charachter in any of your node values, the package crashes and gives an error about an invalid table or something. Looked this up on Metalink and it is logged as a bug. I didn't find a patch though.
    e.g. The following crashes when using dbms_xmlstore, but works fine using dbms_xmlsave...
    Code:
    <ROWSET>
       <ROW num="1">
          <COL>value's</COL>
       </ROW>
    </ROWSET>
    A work around this is to use the old dbms_save package. It basically provides the exact same functionality, except it is implemented correctly. However, it is has a Java implementation which will consume more resources. Personally, I have not had problems with this yet.
    Code:
    begin
      parammap := 'username="''' || username || '''"';
      h := dbms_xmlsave.newcontext('CARGO');
      dbms_xmlsave.propagateoriginalexception(h, true);
      dbms_xmlsave.setkeycolumn(h, 'ID');
      row_count := dbms_xmlsave.updatexml(h, xmlinput.transform(xslt, parammap).getCLOBVal());
      dbms_xmlsave.closecontext(h);
    exception
      when OTHERS then
        dbms_xmlsave.closecontext(h);
        raise;
    end;

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Cool! I'll give it a try.
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •