1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 2:23 PM by 972590 Go to original post RSS
      • 15. Re: XML extract
        972590
        Thank You.
        Sorry Odie I do not have an XML schema. This is first time I have to/am working on XML. Is that something I can create it.Please let me know.
        • 16. Re: XML extract
          odie_63
          969587 wrote:
          Is that something I can create it.
          If you have a good knowledge of the XML structure you're dealing with, it may be interesting to build a schema.
          However, if the structure is likely to evolve quite often, forget it (for now).


          Here's a first draft.
          It uses a row-by-row approach to load the target tables. It can get slower on large XMLs but I guess it's sufficient to show the idea.

          Tables
          create table product_object (
            ParentProductID    VARCHAR2(10)
          , ProductName        VARCHAR2(100)
          , ProductType        VARCHAR2(100)
          , ProductID          VARCHAR2(10)
          , ProductAccountCode VARCHAR2(10)
          ) ;
          
          create table product_location (
            ProductID    VARCHAR2(10)
          , LocationType VARCHAR2(10)
          , Address1     VARCHAR2(30)
          , Address2     VARCHAR2(30)
          , City         VARCHAR2(30)
          , State        VARCHAR2(10)
          , PostalCode   VARCHAR2(10)
          , PostalPlus   VARCHAR2(10)
          , CountryCode  VARCHAR2(10)
          ) ;
          
          create table product_contact (
            ProductID    VARCHAR2(10)
          , ContactType  VARCHAR2(10)
          ) ;
          Procedure
          create or replace procedure loadProductItems (p_clob in clob)
          is
          begin
          
            for r1 in (
              select /*+ no_xml_query_rewrite */ 
                       x.ParentProductID
                     , x.ProductName
                     , x.ProductType
                     , x.ProductID
                     , x.ProductAccountCode
                     , x.ProductLocations
                     , x.ProductContacts
              from xmltable(
                     'declare function local:getObjects($obj as element(*), $pid as xs:string) as element(r)*
                      { 
                        for $i in $obj/ProductObject | $obj/ProductObjects/ProductObject
                        return element r { 
                          attribute ParentProductID {$pid}
                        , $i/Name
                        , $i/ProductType
                        , $i/ProductID
                        , $i/ProductAccount
                        , $i/ProductLocations
                        , $i/ProductContacts 
                        } | local:getObjects($i, $i/ProductID)
                      }; (: :)
                      local:getObjects(/Product/Pld, "")'
                     PASSING xmlparse(document p_clob)
                     COLUMNS ParentProductID    VARCHAR2(10)  PATH '@ParentProductID'
                           , ProductName        VARCHAR2(100) PATH 'Name'
                           , ProductType        VARCHAR2(100) PATH 'ProductType'
                           , ProductID          VARCHAR2(10)  PATH 'ProductID'
                           , ProductAccountCode VARCHAR2(10)  PATH 'ProductAccount'
                           , ProductLocations   XMLTYPE       PATH 'ProductLocations'
                           , ProductContacts    XMLTYPE       PATH 'ProductContacts'
                           
                   ) x
            )
            loop
             
              insert into product_object 
              (
                ParentProductID
              , ProductName
              , ProductType
              , ProductID
              , ProductAccountCode
              )
              values (
                r1.ParentProductID
              , r1.ProductName
              , r1.ProductType
              , r1.ProductID
              , r1.ProductAccountCode
              ) ;
              
              insert into product_location 
              (
                ProductID
              , LocationType
              , Address1
              , Address2
              , City
              , State
              , PostalCode
              , PostalPlus
              , CountryCode
              )
              select r1.ProductID
                   , LocationType
                   , Address1
                   , Address2
                   , City
                   , State
                   , PostalCode
                   , PostalPlus
                   , CountryCode
              from xmltable(
                     '/ProductLocations/ProductLocation'
                     passing r1.ProductLocations
                     columns LocationType VARCHAR2(10) PATH 'ProductLocationType'
                           , Address1     VARCHAR2(30) PATH 'Address1'
                           , Address2     VARCHAR2(30) PATH 'Address2'
                           , City         VARCHAR2(30) PATH 'City'
                           , State        VARCHAR2(10) PATH 'State'
                           , PostalCode   VARCHAR2(10) PATH 'PostalCode'
                           , PostalPlus   VARCHAR2(10) PATH 'PostalPlus'
                           , CountryCode  VARCHAR2(10) PATH 'CountryCode'
                   ) ;
                   
              insert into product_contact 
              (
                ProductID
              , ContactType
              )
              select r1.ProductID
                   , ContactType
              from xmltable(
                     '/ProductContacts/ProductContact'
                     passing r1.ProductContacts
                     columns ContactType  VARCHAR2(10) PATH 'ProductContactType'
                   ) ;
            
            end loop;  
          
          end;
          Testing
          SQL> declare
            2    p_clob clob;
            3  begin
            4    -- temp storage for my test...
            5    select xmlserialize(document object_value) into p_clob from tmp_xml;
            6  
            7    loadproductitems(p_clob);
            8  end;
            9  /
           
          PL/SQL procedure successfully completed
           
          SQL> select * from product_object;
           
          PARENTPRODUCTID PRODUCTNAME       PRODUCTTYPE    PRODUCTID  PRODUCTACCOUNTCODE
          --------------- ----------------- -------------- ---------- ------------------
                          XYZTEST           TESTTYPE       B000148    C10023
          B000148         XYZTEST-2         TESTTYPE-2     B000148-2  C10023-2
          B000148-2       XYZTEST-3         TESTTYPE-3     B000148-3  C10023-3
           
          SQL> select * from product_location;
           
          PRODUCTID  LOCATIONTYPE ADDRESS1                       ADDRESS2                       CITY                           STATE      POSTALCODE POSTALPLUS COUNTRYCODE
          ---------- ------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- -----------
          B000148    L            43414 SP LN                    NULL                           CHANTILLI                      VA         45245      1123       USA
          B000148    L1           43415 SPRINGFIELD LN           NULL1                          CHANTILLI1                     VA1        45241      1121       USA1
          B000148-2  L-2          43414 SPRINGFIELD LN-2         NULL-2                         CHANTILLI-2                    VA-2       45040-2    1123-2     USA-2
          B000148-3  L-3          43414 SPRINGFIELD LN-3         NULL-3                         CHANTILLI-3                    VA-3       45040-3    1123-3     USA-3
           
          SQL> select * from product_contact;
           
          PRODUCTID  CONTACTTYPE
          ---------- -----------
          B000148    L
          B000148    P
          B000148-2  L-2
          B000148-3  L-3
           
          • 17. Re: XML extract
            972590
            You are the Man. Awesome and thank you for taking pain and shedding light. I will play with the real data and will let you know.

            Thanks again
            Ed
            • 18. Re: XML extract
              972590
              Odie is very helpful
              1 2 Previous Next