4 Replies Latest reply: Feb 5, 2013 7:47 AM by odie_63 RSS

    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
          • 2. Re: Reading duplicate tags under its parent tab
            989214
            Not answered
            • 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