3 Replies Latest reply: May 2, 2013 7:13 AM by odie_63 RSS

    Reading nested XML using XQUERY in PL/SQL procedure

    865717
      I am using Oracle 11G.
      I have a PL/SQL procedure which is reading XML using xquery from XMLTYPE column in a table. The XML contains data of DEPARTMENT and its SECTIONS. DEPARTMENT has one to many relationship with SECTIONS i.e. a DEPARTMENT can have one or multiple SECTIONS and there may be instances where DEPARTMENT will not have any SECTIONS.

      Structure of XML is such that
      <DATA>
      tag identifies a DEPARTMENT and set of its corresponding SECTIONS.

      XML
      <ROWSET> 
      <DATA>
       <DEPARTMENT>
        <DEPARTMENT_ID>DEP1</DEPARTMENT_ID>
        <DEPARTMENT_NAME>myDEPARTMENT1</DEPARTMENT_NAME>
       </DEPARTMENT>
       <SECTIONS>
        <SECTIONS_ID>6390135666643567</SECTIONS_ID>
        <SECTIONS_NAME>mySection1</SECTIONS_NAME>
        </SECTIONS>
         <SECTIONS>
        <SECTIONS_ID>6390135666643567</SECTIONS_ID>
        <SECTIONS_NAME>mySection2</SECTIONS_NAME>
        </SECTIONS>
       </DATA>
       <DATA>
       <DEPARTMENT>
        <DEPARTMENT_ID>DEP2</DEPARTMENT_ID>
        <DEPARTMENT_NAME>myDEPARTMENT2</DEPARTMENT_NAME>
       </DEPARTMENT>
       <SECTIONS>
        <SECTIONS_ID>63902</SECTIONS_ID>
        <SECTIONS_NAME>mySection1</SECTIONS_NAME>
        </SECTIONS>
       </DATA>
      </ROWSET>
      XQUERY
      select
       department_id,
        department_name,
        sections_id,
        sections_name
      from
        OFFLINE_XML xml_list,
        xmltable(
          '
            for $department in $param/ROWSET/DATA
              return $department
          '
          passing xml_list.xml_file as "param"
          columns
            "DEPARTMENT_ID"   varchar2(100) path '//DEPARTMENT/DEPARTMENT_ID',
            "DEPARTMENT_NAME" varchar2(4000) path '//DEPARTMENT/DEPARTMENT_NAME',
            "SECTIONS_ID"     varchar2(100) path '//SECTIONS/SECTIONS_ID',
            "SECTIONS_NAME"   varchar2(4000) path '//SECTIONS/SECTIONS_NAME'
        ) section_list
      where
        xml_list.Status = 5
      On running the query i receive an error
      ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton 
      sequence - got multi-item sequence
      The is natural as i have multiple sections, now how am i going to handle this situations.
        • 1. Re: Reading nested XML using XQUERY in PL/SQL procedure
          odie_63
          This is the usual approach to handle multiple nested collections :
          SQL> select d.department_id
            2       , d.department_name
            3       , s.sections_id
            4       , s.sections_name
            5  from offline_xml t
            6     , xmltable(
            7         '/ROWSET/DATA'
            8         passing t.xml_file
            9         columns
           10           DEPARTMENT_ID   varchar2(20) path 'DEPARTMENT/DEPARTMENT_ID'
           11         , DEPARTMENT_NAME varchar2(30) path 'DEPARTMENT/DEPARTMENT_NAME'
           12         , SECTIONS        xmltype      path 'SECTIONS'
           13       ) d
           14     , xmltable(
           15         '/SECTIONS'
           16         passing d.sections
           17         columns
           18           SECTIONS_ID     varchar2(20) path 'SECTIONS_ID'
           19         , SECTIONS_NAME   varchar2(30) path 'SECTIONS_NAME'
           20      ) s
           21  ;
           
          DEPARTMENT_ID        DEPARTMENT_NAME                SECTIONS_ID          SECTIONS_NAME
          -------------------- ------------------------------ -------------------- ------------------------------
          DEP1                 myDEPARTMENT1                  6390135666643567     mySection1
          DEP1                 myDEPARTMENT1                  6390135666643567     mySection2
          DEP2                 myDEPARTMENT2                  63902                mySection1
           
          • 2. Re: Reading nested XML using XQUERY in PL/SQL procedure
            865717
            @odie_63 Your answer is correct, however, it does not solve my problem. I have mentioned in my request that there are cases in my XML where SECTIONS will not be there whereas, your query only brings the results if their is at least 1 SECTIONS per DEPARTMENT.
            • 3. Re: Reading nested XML using XQUERY in PL/SQL procedure
              odie_63
              862714 wrote:
              I have mentioned in my request that there are cases in my XML where SECTIONS will not be there whereas, your query only brings the results if their is at least 1 SECTIONS per DEPARTMENT.
              Sounds like a reproach...

              Use outer joins :
              SQL> select d.department_id
                2       , d.department_name
                3       , s.sections_id
                4       , s.sections_name
                5  from offline_xml t
                6     , xmltable(
                7         '/ROWSET/DATA'
                8         passing t.xml_file
                9         columns
               10           DEPARTMENT_ID   varchar2(20) path 'DEPARTMENT/DEPARTMENT_ID'
               11         , DEPARTMENT_NAME varchar2(30) path 'DEPARTMENT/DEPARTMENT_NAME'
               12         , SECTIONS        xmltype      path 'SECTIONS'
               13       ) (+) d
               14     , xmltable(
               15         '/SECTIONS'
               16         passing d.sections
               17         columns
               18           SECTIONS_ID     varchar2(20) path 'SECTIONS_ID'
               19         , SECTIONS_NAME   varchar2(30) path 'SECTIONS_NAME'
               20      ) (+) s
               21  ;
               
              DEPARTMENT_ID        DEPARTMENT_NAME                SECTIONS_ID          SECTIONS_NAME
              -------------------- ------------------------------ -------------------- ------------------------------
              DEP1                 myDEPARTMENT1                  6390135666643567     mySection1
              DEP1                 myDEPARTMENT1                  6390135666643567     mySection2
              DEP2                 myDEPARTMENT2                  63902                mySection1
              DEP3                 Dept with no section                                
               
              I used old Oracle proprietary syntax here but you can also write ANSI outer joins :
              LEFT OUTER JOIN  XMLTable( ... )
                  ON 1 = 1
              Edited by: odie_63 on 2 mai 2013 14:12