6 Replies Latest reply: May 10, 2012 10:23 AM by Manjusha Muraleedas RSS

    xml parsing for child nodes

    Manjusha Muraleedas
      Hi,

      I have the following xml

      '<AllocSimulation>
      <items>
           <ItemNo>76</ItemNo>     
           <DisplayCodes>
                <DisplayCode>     1     </DisplayCode>
                <DisplayCode>2     </DisplayCode>     
           </DisplayCodes>
      </items>
      <items>
           <ItemNo>767</ItemNo>     
           <DisplayCodes>
                <DisplayCode>     3     </DisplayCode>
                <DisplayCode>5     </DisplayCode>     
           </DisplayCodes>
      </items>
      </AllocSimulation>'

      How can I extract the elements like

      item no display code
      76 1,2
      767 3,5



      I ran the following query
           Select  * From
      xmltable('AllocSimulation/items'  passing  xmltype('<AllocSimulation>
      <items>
           <ItemNo>76</ItemNo>     
           <DisplayCodes>
                <DisplayCode>     1     </DisplayCode>
                <DisplayCode>2     </DisplayCode>     
           </DisplayCodes>
        </items>
        <items>
           <ItemNo>767</ItemNo>     
           <DisplayCodes>
                <DisplayCode>     3     </DisplayCode>
                <DisplayCode>5     </DisplayCode>     
           </DisplayCodes>
        </items>
      </AllocSimulation>')      columns       
           Item Varchar2(10) Path  'ItemNo',
        display xmltype path '//DisplayCode');
      result
      76 <DisplayCode>     1</DisplayCode><DisplayCode>2     </DisplayCode>
      767 <DisplayCode>     3     </DisplayCode><DisplayCode>5     </DisplayCode>
        • 1. Re: xml parsing for child nodes
          odie_63
          Try this one :
          SQL> SELECT *
            2  FROM XMLTable(
            3        '/AllocSimulation/items'
            4        passing xmltype('<AllocSimulation>
            5  <items>
            6       <ItemNo>76</ItemNo>
            7       <DisplayCodes>
            8            <DisplayCode>1</DisplayCode>
            9            <DisplayCode>2</DisplayCode>
           10       </DisplayCodes>
           11    </items>
           12    <items>
           13       <ItemNo>767</ItemNo>
           14       <DisplayCodes>
           15            <DisplayCode>3</DisplayCode>
           16            <DisplayCode>5</DisplayCode>
           17       </DisplayCodes>
           18    </items>
           19  </AllocSimulation>')
           20        columns
           21              Item    varchar2(10) path 'ItemNo',
           22          display varchar2(40) path 'string-join(DisplayCodes/DisplayCode, ",")'
           23       )
           24  ;
           
          ITEM       DISPLAY
          ---------- ----------------------------------------
          76         1,2
          767        3,5
           
          • 2. Re: xml parsing for child nodes
            Manjusha Muraleedas
            Thank you...

            Is it possible for us to get the display codes in a collection?

            Suppose the xml is as below
            <AllocSimulation>
                 <items>
                      <ItemNo>76</ItemNo>
                      <DisplayCodes>
                           <DisplayCode>
                                <code>DS01</code>
                                <Min>1</Min>
                                <Max>2</Max>
                           </DisplayCode>
                           <DisplayCode>
                                <code>DS02</code>
                                <Min>4</Min>
                                <Max>87</Max>
                           </DisplayCode>
                      </DisplayCodes>
                 </items>
                 <items>
                      <ItemNo>086823</ItemNo>
                      <DisplayCodes>
                           <DisplayCode>
                                <code>AB01</code>
                                <Min>4</Min>
                                <Max>75</Max>
                           </DisplayCode>
                           <DisplayCode>
                                <code>AB02</code>
                                <Min>3</Min>
                                <Max>7</Max>
                           </DisplayCode>
                      </DisplayCodes>
                 </items>
            </AllocSimulation> 
            I have a 2 types
            SQL> create or replace type displaycode is OBJECT
              2  (
              3       display_code varchar2(100),
              4       MIN          number(10),
              5       MAX          number(10)
              6  )
              7  ;
              8  /
            
            Type created
            
            SQL> create or replace type displaycodes_vtt is table of displaycode;
              2  /
            
            Type created
            Is it possible to get display codes information for each item in to displaycodes_vtt in one query?

            Also , Can u please provide me a link to study more about string-join

            Edited by: Manjusha Muraleedas on 9 May, 2012 10:34 PM
            • 3. Re: xml parsing for child nodes
              Manjusha Muraleedas
              This is one of the solutions... any straight forwatd solution for this?
               with datatab as (SELECT *
               FROM XMLTable(       
                     '/purchase/items'
                     passing xmltype('<purchase>
              <items>
                   <ItemNo>76</ItemNo>     
                   <DisplayCodes>
                    <DisplayCode>
                        <code>DS01</code>
                        <Min>1</Min>
                        <Max>2</Max>     
                     </DisplayCode>
                      <DisplayCode>
                        <code>DS02</code>
                        <Min>4</Min>
                        <Max>87</Max>     
                   </DisplayCode>
                   </DisplayCodes>
                </items>
                <items>                                                                       
                   <ItemNo>086823</ItemNo>     
                   <DisplayCodes>
                    <DisplayCode>
                        <code>AB01</code>
                        <Min>4</Min>
                        <Max>75</Max>     
                     </DisplayCode>
                      <DisplayCode>
                        <code>AB02</code>
                        <Min>3</Min>
                        <Max>7</Max>     
                   </DisplayCode>
                   </DisplayCodes>
                </items>
              </purchase>        
              ')
                     columns             
                              ItemNo path 'ItemNo'            ,
                              display xmltype path 'DisplayCodes'
                    )      )
                             select d.itemno, cast (collect(displaycode(p.code,Min,Max)) as diaplaycodes_vtt) from datatab d ,xmltable ('DisplayCodes/DisplayCode' passing d.display
                             columns 
                             code varchar2(10) path 'code', 
                             Min   number(10) path 'Min', 
                                  Max   number(10) path 'Max'
                             ) p  group by  d.itemno  ;                      
              • 4. Re: xml parsing for child nodes
                odie_63
                Manjusha Muraleedas wrote:
                This is one of the solutions... any straight forwatd solution for this?
                That's the one I would have suggested too.

                Another possibility would be to build the collection in a subquery using the MULTISET operator, but I prefer the first approach.

                To summarize (assuming the document is stored in TMP_XML table) :
                SELECT x.itemno
                     , cast(
                         multiset(
                           select code, min, max
                           from XMLTable('DisplayCodes/DisplayCode' 
                                 passing x.display
                                 columns 
                             code  varchar2(100) path 'code'
                                 , Min   number(10)    path 'Min'
                                 , Max   number(10)    path 'Max'
                          )
                         )
                         as displaycodes_vtt
                       ) as display_collection 
                FROM tmp_xml t
                   , XMLTable('/purchase/items'
                       passing t.object_value
                       columns             
                      ItemNo  varchar2(30) path 'ItemNo'
                       , display xmltype      path 'DisplayCodes'
                     ) x
                ;
                or,
                SELECT d.itemno
                     , cast(collect(displaycode(p.code, p.Min, p.Max)) as displaycodes_vtt) as display_collection
                FROM tmp_xml t
                   , XMLTable('/purchase/items'
                       passing t.object_value 
                       columns             
                      ItemNo  varchar2(30) path 'ItemNo'
                       , display xmltype      path 'DisplayCodes'
                     ) d
                   , XMLTable('DisplayCodes/DisplayCode' 
                       passing d.display
                       columns 
                      code  varchar2(100) path 'code'
                       , Min   number(10)    path 'Min'
                       , Max   number(10)    path 'Max'
                     ) p  
                GROUP BY d.itemno
                ;
                • 5. Re: xml parsing for child nodes
                  odie_63
                  Also , Can u please provide me a link to study more about string-join
                  There's not much to study about this function actually. It takes two arguments, first one is a sequence of items, second one a string separator.
                  You'll find everything you need by just googling "xpath string-join".
                  • 6. Re: xml parsing for child nodes
                    Manjusha Muraleedas
                    Thanks Alot...