4 Replies Latest reply on Feb 5, 2013 1:47 PM by odie_63

    Reading duplicate tags under its parent tab

    989214
      Here is the sample XML, i intend to use XMLTABLE to read the structured XML
      ---------------------------------------------------------------------------------------------------
      <employee>
      <empid>101</empid>
      <empname>Mike</empname>
      <emptype desig="PAT" confirmed="NO"/>
      <projects>
           <projname>Oracle BI migration</projname>
           <projname>Oracle XML parsing</projname>
           <projmanagerid>102</projmanagerid>
      </projects>     
      <empid>102</empid>
      <empname>Sophie</empname>
      <emptype desig="M" confirmed="YES"/>
      <projects>
           <projname>Midwest US</projname>
           <projname>Automotive Insurance for US</projname>
           <projmanagerid>103</projmanagerid>
      </projects>
      </employee>
      ---------------------------------------------------------------------------------------------------

      Based on the condition
      if the employee is confirmed as YES, then get the different project he/she has worked along with the employee id

      Its is required to fetch the details conditionally,
      so the above xml would yeild the below result columns
      EMPID     PROJECTS
      --------- -----------------------
      102     Midwest US
      102     Automotive Insurance for US
        • 1. Re: Reading duplicate tags under its parent tab
          989214
          its really important
          • 3. Re: Reading duplicate tags under its parent tab
            odie_63
            986211 wrote:
            its really important
            I'm sure it is... for you.
            This is a forum of volunteers, who dedicate their free time in helping others or discussing issues.
            Don't expect people to respond as if it were a paid support.
            Not answered
            Yeah, you posted 30 minutes ago. We're not a chat room either.
            • 4. Re: Reading duplicate tags under its parent tab
              odie_63
              - Does your database have a version?
              - Where does the input XML reside?

              The structure is not very friendly, but you can do it like this :
              SQL> with sample_data as (
                2    select xmltype('<employee>
                3  <empid>101</empid>
                4  <empname>Mike</empname>
                5  <emptype desig="PAT" confirmed="NO"/>
                6  <projects>
                7  <projname>Oracle BI migration</projname>
                8  <projname>Oracle XML parsing</projname>
                9  <projmanagerid>102</projmanagerid>
               10  </projects>
               11  <empid>102</empid>
               12  <empname>Sophie</empname>
               13  <emptype desig="M" confirmed="YES"/>
               14  <projects>
               15  <projname>Midwest US</projname>
               16  <projname>Automotive Insurance for US</projname>
               17  <projmanagerid>103</projmanagerid>
               18  </projects>
               19  </employee>') xmldoc
               20    from dual
               21  )
               22  select x.*
               23  from sample_data t
               24     , xmltable(
               25         'for $e in /employee/empid
               26          where $e/following-sibling::emptype[1]/@confirmed = "YES"
               27          return
               28            for $p in $e/following-sibling::projects[1]/projname
               29            return element r { $e, $p }'
               30         passing t.xmldoc
               31         columns empid    number       path 'empid'
               32               , projects varchar2(40) path 'projname'
               33       ) x
               34  ;
               
                   EMPID PROJECTS
              ---------- ----------------------------------------
                     102 Midwest US
                     102 Automotive Insurance for US