This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 12:23 PM by 972590 Go to original post RSS
  • 15. Re: XML extract
    972590 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Odie is very helpful
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points