2 Replies Latest reply on Mar 7, 2020 9:24 PM by odie_63

    Fastest way to convert lots of WIDE XML records to rowsets

    4155020
    4155020 Mar 7, 2020 6:08 PM
      Hi all.

       

       

      I've got a requirement to load large amounts of XML data (with several hundred attributes) and ultimately convert it to table rowsets in Oracle. I'm looking at high performance options to do this and, frankly, don't care if the XML parsing/conversion happens inside or outside of the database as long as the throughput is extremely fast.

       

       

      The current (horrible) mechanism is to load each XML record via SQL*Plus into an XMLType and then use a giant view with a bunch of EXTRACTVAL functions for each attribute... needless to say, the performance is quite dismal.

       

       

      Options I'm considering (so far) but don't know which would ultimately be faster:

       

      1. Using an external utility to convert the XML to a delimited file (introduces another processing hop, etc.)
      2. Since I have access to it, using Informatica PowerCenter to convert the data (hopefully in a single inline pass) and then insert into Oracle from there (I'm not sure how good Informatica is at parsing XML though and am worried about the round trips)
      3. Trying DBMS_XSLProcessor in a manner similar to: https://oracle-base.com/articles/9i/parse-xml-documents-9i (again, not sure how fast this would ultimately be)
      4. Running away screaming and becoming a Shepard...

       


       

       

      Does anyone have any suggestions on what might be the fastest approach here or have any other suggestions on something else that would be highly performant?)

       

       


       

       

      Thank you!

       

       


       

      • 50 Views
      • Tags:
        • 1. Re: Fastest way to convert lots of WIDE XML records to rowsets
          cormaco
          cormaco Mar 7, 2020 6:55 PM (in response to 4155020)

          What is your Oracle version? EXTRACT and EXTRACTVALUE are deprecated since 11.2:

          https://docs.oracle.com/cd/E18283_01/server.112/e17118/functions061.htm

          Note:

          The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information.

          XMLTABLE was designed exactly for your requirement. I find it a very efficient and powerful tool.

          • 2. Re: Fastest way to convert lots of WIDE XML records to rowsets
            odie_63
            odie_63 Mar 7, 2020 9:24 PM (in response to 4155020)

            4155020 wrote:

             

            Does anyone have any suggestions on what might be the fastest approach here or have any other suggestions on something else that would be highly performant?)

            We'll need more info to suggest the best approach.

             

            - Database version? (SELECT * FROM v$version)

            - How many files? Average size?

            - Where do the files reside initially? Client or server-side? If the former, can you afford moving them to the db server?

            - Are you implementing a recurring process or a one-shot?

             

            Depending on the size and database version, I would either parse them directly into relational format from an Oracle directory (possibly in bulk via an external table), or load them first into a (temp) Binary XMLType column and process them from there.

            Both approaches would use XMLTABLE.