This discussion is archived
3 Replies Latest reply: May 2, 2013 5:13 AM by odie_63 RSS

Reading nested XML using XQUERY in PL/SQL procedure

865717 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points