4 Replies Latest reply: Apr 26, 2012 5:34 PM by user5000387 RSS

    Trying to separate mst and dtl data while extracting from xml

    user5000387
      Any help is greatly appreciated.
      What I try to get done is as follows:
      My external xml file structure has a header and 2 sub-sections with eachsub-section having multiple nodes.
      <BOM>
      <Header>
      <Article>123</Article>
      <Description></Description>
      <Language>E</Language>
      <Plant>QUO</Plant>
      <ValidFrom>20120424</ValidFrom>
      <ValidTo></ValidTo>
      <BaseQty>1000</BaseQty>
      <BaseUoM>kg</BaseUoM>
      <Price>212.51</Price>
      <LCFfound>Yes</LCFfound>
      <Ingredients>
      <Ingredient>
      <Counter>1</Counter>
      <Component>G52000</Component>
      <CompDescr>AGRICULTURAL SALT</CompDescr>
      <Quantity>557.941</Quantity>
      <UoM>kg</UoM>
      <Percentage>55.794</Percentage>
      <Available>Yes</Available>
      <MinQty></MinQty>
      <MaxQty></MaxQty>
      </Ingredient>
      <Ingredient>
      <Counter>2</Counter>
      <Component>G51000</Component>
      <CompDescr>SYLVINITE</CompDescr>
      <Quantity>273.501</Quantity>
      <UoM>kg</UoM>
      <Percentage>27.350</Percentage>
      <Available>Yes</Available>
      <MinQty></MinQty>
      <MaxQty></MaxQty>
      </Ingredient>
      . . .
      <Nutrients>
      <Nutrient>
      <Counter>1</Counter>
      <CharDescr>WEIGHT</CharDescr>
      <Description>WEIGHT</Description>
      <Value>553.985</Value>
      <Usage>3</Usage>
      </Nutrient>
      <Nutrient>
      <Counter>2</Counter>
      <CharDescr>PRICE</CharDescr>
      <Description>PRICE</Description>
      <Value>212.509</Value>
      <Usage>4</Usage>
      </Nutrient>
      . . .
      </Nutrients>
      </Header>
      </BOM>

      I can't get the Ingredients/Ingredient and the Nutrients/Nutrient section separated out without loosing the relationship to the Header.
      I use the following code to insert into my header table and that works fine:
      insert into X_FM_HDR_IMP( ID
      ,PRODUCT
      ,DESCRIPTION
      ,LANG
      ,ORG
      ,FROM_DATE
      ,TO_DATE
      ,QTY
      ,UOM
      ,PRICE
      ,LCF_FOUND)
      select x.*
      from xmltable (XMLNAMESPACES(DEFAULT 'http://www.anysite.com/xmltable'),
      '/BOM/Header'
      passing xmltype(bfilename('XML_IMP_DIR', "testing.xml'), nls_charset_id('CHAR_CS'))
      columns ID FOR ORDINALITY
      ,PRODUCT VARCHAR2(40) path 'Article'
      ,DESCRIPTION VARCHAR2(254) path 'Description'
      ,LANG VARCHAR2(5) path 'Language'
      ,ORG VARCHAR2(3) path 'Plant'
      ,FROM_DATE VARCHAR2(8) path 'ValidFrom'
      ,TO_DATE VARCHAR2(8) path 'ValidTo'
      ,QTY NUMBER path 'BaseQty'
      ,UOM VARCHAR2(3) path 'BaseUoM'
      ,PRICE NUMBER path 'Price'
      ,LCF_FOUND VARCHAR2(1) path 'LCFfound'
      ) as x;

      I need to be able to either load the xml record into one table or preferably into 3 tables such that
      <BOM>
      <Header>
      = record

      and

      <BOM>
      <Header>
      <Ingredients>
      <Ingredient>
      = record with 'Article' and 'Org' from <Header> as columns

      and

      <BOM>
      <Header>
      <Nutrients>
      <Nutrient>
      = record with 'Article' and 'Org' from <Header> as columns

      The xml file structure cannot be changed because it is generated by a 3rd party.

      Thanks again in advance for any insight on how to get this accomplished.
        • 1. Re: Trying to separate mst and dtl data while extracting from xml
          odie_63
          Using the following simplified structure :
          create table header_imp (
            ID number
          , PRODUCT     VARCHAR2(40)
          , DESCRIPTION VARCHAR2(254)
          , LANG        VARCHAR2(5)
          , ORG         VARCHAR2(3)
          );
          
          create table ingredient_imp (
            Product      varchar2(40)
          , org          VARCHAR2(3)
          , Counter      number
          , Component    varchar2(30)
          , CompDescr    varchar2(254)
          , Quantity     number
          , UoM          varchar2(5)
          );
          
          create table nutrient_imp (
            Product      varchar2(40)
          , org          VARCHAR2(3)
          , Counter      number
          , CharDescr    varchar2(254)
          , Description  varchar2(254)
          , Value        number
          , Usage        number
          );
          This multitable insert works for me :
          insert all
            when hrn = 1 then into header_imp 
                     (id, product, description, lang, org)
              values (id, product, description, lang, org)
          
          when irn = 1 then into ingredient_imp 
                     (product, org, counter, component, compdescr, quantity, uom)
              values (product, org, i_counter, component, compdescr, quantity, uom)
          
          when nrn = 1 then into nutrient_imp 
                     (product, org, counter, chardescr, description, value, usage)
              values (product, org, n_counter, chardescr, n_description, value, usage)
          
          select -- header info
                 h.id
               , h.product
               , h.description
               , h.lang
               , h.org
               , rownum hrn 
               -- ingredient info
               , i.counter i_counter
               , i.component
               , i.compdescr
               , i.quantity
               , i.uom
               , row_number() over(partition by irn order by null) irn
               -- nutrient info
               , n.counter n_counter
               , n.chardescr
               , n.description n_description
               , n.value
               , n.usage
               , row_number() over(partition by nrn order by null) nrn
          from xmltable (
                 '/BOM/Header'
                 passing xmltype(bfilename('TEST_DIR', 'testing.xml'), nls_charset_id('CHAR_CS'))
                 columns 
                   ID FOR ORDINALITY
                 , PRODUCT     VARCHAR2(40)  path 'Article'
                 , DESCRIPTION VARCHAR2(254) path 'Description'
                 , LANG        VARCHAR2(5)   path 'Language'
                 , ORG         VARCHAR2(3)   path 'Plant'
                 , INGREDIENTS XMLType       path 'Ingredients'
                 , NUTRIENTS   XMLType       path 'Nutrients'
               ) as h
            , xmltable(
                '/Ingredients/Ingredient'
                passing h.ingredients
                columns
                  Counter      number        path 'Counter'
                , Component    varchar2(30)  path 'Component'
                , CompDescr    varchar2(254) path 'CompDescr'
                , Quantity     number        path 'Quantity'
                , UoM          varchar2(5)   path 'UoM'
                , irn          for ordinality
              ) i
            , xmltable(
                '/Nutrients/Nutrient'
                passing h.nutrients
                columns
                  Counter      number        path 'Counter'
                , CharDescr    varchar2(254) path 'CharDescr'
                , Description  varchar2(254) path 'Description'
                , Value        number        path 'Value'
                , Usage        number        path 'Usage'
                , nrn          for ordinality
              ) n
          ;
          SQL> select * from header_imp;
           
                  ID PRODUCT         DESCRIPTION         LANG  ORG
          ---------- --------------- ------------------- ----- ---
                   1 123                                 E     QUO
          
          SQL> select * from ingredient_imp;
           
          PRODUCT         ORG    COUNTER COMPONENT                      COMPDESCR                     QUANTITY UOM
          --------------- --- ---------- ------------------------------ --------------------------- ---------- -----
          123             QUO          1 G52000                         AGRICULTURAL SALT              557,941 kg
          123             QUO          2 G51000                         SYLVINITE                      273,501 kg
          
          SQL> select * from nutrient_imp;
           
          PRODUCT         ORG    COUNTER CHARDESCR                      DESCRIPTION                 VALUE      USAGE
          --------------- --- ---------- ------------------------------ ---------------------- ---------- ----------
          123             QUO          1 WEIGHT                         WEIGHT                    553,985          3
          123             QUO          2 PRICE                          PRICE                     212,509          4
           
          • 2. Re: Trying to separate mst and dtl data while extracting from xml
            user5000387
            Odie_63 - thanks for the quick reply. I am a big fan of yours.

            I modified my code to match yours and it executed fine.
            I noticed though that the header table only contains 1 record (xml file has 4)
            and the ingredient table holds all 24 child records belonging to the header record that got processed but also contains
            2 sets of data that belong to 2 other header records from the xml file
            The same holds true for the nutrients node.

            I'll take a look at the xml file again but perhaps you see something wrong with the adapted code.

            Here it is:
            insert all
            when hrn = 1 then into apps.XXKF_BM_FM_HDR_IMP
            ( ID, PRODUCT, DESCRIPTION, LANG, ORG, FROM_DATE, TO_DATE, QTY , UOM, PRICE, LCF_FOUND)
            values ( ID, PRODUCT, DESCRIPTION, LANG, ORG, FROM_DATE, TO_DATE, H_QTY , H_UOM, PRICE, LCF_FOUND)

            when irn = 1 then into apps.XXKF_BM_FM_DTL_IMP
            (PRODUCT, ORG, COUNTER, COMPONENT, COMP_DESC, QTY, UOM, PCTG, AVAIL)
            values (PRODUCT, ORG, I_COUNTER, COMPONENT, COMP_DESC, I_QTY, I_UOM, PCTG, AVAIL)

            when nrn = 1 then into apps.XXKF_BM_FM_NUTR_IMP
            (PRODUCT, ORG, COUNTER, CODE, CODE_DESC, VALUE, USAGE)
            values (PRODUCT, ORG, N_COUNTER, CODE, CODE_DESC, VALUE, USAGE)

            select -- header info
            h.ID
            , h.PRODUCT
            , h.DESCRIPTION
            , h.LANG
            , h.ORG
            , h.FROM_DATE
            , h.TO_DATE
            , h.QTY H_QTY
            , h.UOM H_UOM
            , h.PRICE
            , h.LCF_FOUND
            , rownum hrn
            -- ingredient info
            , i.COUNTER I_COUNTER
            , i.COMPONENT
            , i.COMP_DESC
            , i.QTY I_QTY
            , i.UOM I_UOM
            , i.PCTG
            , i.AVAIL
            , row_number() over(partition by irn order by null) irn
            -- nutrient info
            , n.COUNTER N_COUNTER
            , n.CODE
            , n.CODE_DESC
            , n.VALUE
            , n.USAGE
            , row_number() over(partition by nrn order by null) nrn
            from xmltable (
            '/BOM/Header'
            passing xmltype(bfilename('BESTMIX_DIR', 'bestmix.xml'), nls_charset_id('CHAR_CS'))
            columns
            ID FOR ORDINALITY
            , PRODUCT VARCHAR2(40) path 'Article'
            , DESCRIPTION VARCHAR2(254) path 'Description'
            , LANG VARCHAR2(5) path 'Language'
            , ORG VARCHAR2(3) path 'Plant'
            , FROM_DATE VARCHAR2(8) path 'ValidFrom'
            , TO_DATE VARCHAR2(8) path 'ValidTo'
            , QTY NUMBER path 'BaseQty'
            , UOM VARCHAR2(3) path 'BaseUoM'
            , PRICE NUMBER path 'Price'
            , LCF_FOUND VARCHAR2(1) path 'LCFfound'
            , INGREDIENTS XMLType path 'Ingredients'
            , NUTRIENTS XMLType path 'Nutrients'
            ) as h
            , xmltable(
            '/Ingredients/Ingredient'
            passing h.ingredients
            columns
            COUNTER NUMBER path 'Counter'
            ,COMPONENT VARCHAR2(40) path 'Component'
            ,COMP_DESC VARCHAR2(254) path 'CompDescr'
            ,QTY NUMBER path 'Quantity'
            ,UOM VARCHAR2(3) path 'UoM'
            ,PCTG NUMBER path 'Percentage'
            ,AVAIL VARCHAR2(1) path 'Available'
            ,irn for ordinality
            ) i
            , xmltable(
            '/Nutrients/Nutrient'
            passing h.nutrients
            columns
            COUNTER NUMBER path 'Counter'
            ,CODE VARCHAR2(30) path 'CharDescr'
            ,CODE_DESC VARCHAR2(70) path 'Description'
            ,VALUE NUMBER path 'Value'
            ,USAGE VARCHAR2(10) path 'Usage'
            ,nrn for ordinality
            ) n
            ;
            • 3. Re: Trying to separate mst and dtl data while extracting from xml
              odie_63
              I noticed though that the header table only contains 1 record (xml file has 4)
              You didn't mention that a file could contain multiple headers, so I assumed only one occurrence and used ROWNUM = 1 to select the first row of header data among the duplicates.

              It's easy to fix though, just modify the way we compute HRN, IRN and NRN selectors :
              , row_number() over(partition by h.id order by null) hrn 
              
              ...
              
              , row_number() over(partition by h.id, irn order by null) irn
              
              ...
              
              , row_number() over(partition by h.id, nrn order by null) nrn
              • 4. Re: Trying to separate mst and dtl data while extracting from xml
                user5000387
                Thanks odie_63; worked like a charm.