This discussion is archived
4 Replies Latest reply: Oct 31, 2013 10:26 AM by Jason_(A_Non) RSS

data missing

user4423142 Newbie
Currently Being Moderated

Hi everybody,

 

I used Oracle 11g Release 11.2.0.3.0

 

Document XML

<?xml version="1.0" encoding="UTF-8"?>
<people xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="pour_tests.xsd" societe="COMPAGNIE DUPONT">
     <groupe name="ADMINISTRATIF">
         <sous-groupe name="SECRETARIAT">
             <people_infos>
                 <nom>DUPONT</nom>
                 <prenom>Pierre</prenom>
             </people_infos>
             <people_infos>
                 <nom>MARTIN</nom>
                 <prenom>Roger</prenom>
             </people_infos>
         </sous-groupe>
         <sous-groupe name="LABORATOIRE">
             <people_infos>
                 <nom>LATOUR</nom>
                 <prenom>André</prenom>
             </people_infos>
         </sous-groupe>
         <sous-groupe name="FACTURATION"/>
         <sous-groupe name="EXPEDITION">
             <people_infos>
                 <nom>LEMERLU</nom>
                 <prenom>Christian</prenom>
             </people_infos>
         </sous-groupe>
     </groupe>
</people>

 

With the following request :

select gr.group_name,sub_gr.subgroup_name,people.firstname from pour_test ,

XMLTable('for $i in /people/groupe return $i' passing people columns group_name varchar2(100) PATH '@name' , sous_groupe xmltype PATH 'sous-groupe') gr ,

XMLTable('/sous-groupe' passing gr.sous_groupe columns subgroup_name varchar2(100) PATH '@name', people xmltype PATH 'people_infos') sub_gr,

XMLTable('/people_infos' passing sub_gr.people columns firstname varchar2(100) PATH 'nom') people;

 

the result is :

ADMINISTRATIFSECRETARIATDUPONT
ADMINISTRATIFSECRETARIATMARTIN
ADMINISTRATIFLABORATOIRELATOUR
ADMINISTRATIFEXPEDITIONLEMERLU

 

But I want to display also the sous-groupe FACTURATION that is empty. I think we have to use a syntax like '(+)' but my different tests failed.

Thank you for your help.

  • 1. Re: data missing
    odie_63 Guru
    Currently Being Moderated

    I think we have to use a syntax like '(+)' but my different tests failed.

    That's correct.

    You can do it like this (or using ANSI join syntax if you prefer) :

    SQL> select gr.group_name

      2       , sub_gr.subgroup_name

      3       , people.firstname

      4  from pour_test

      5     , XMLTable('for $i in /people/groupe return $i'

      6         passing people

      7         columns group_name varchar2(100) PATH '@name'

      8               , sous_groupe xmltype PATH 'sous-groupe'

      9       ) gr

    10     , XMLTable('/sous-groupe'

    11         passing gr.sous_groupe

    12         columns subgroup_name varchar2(100) PATH '@name'

    13               , people xmltype PATH 'people_infos'

    14       ) (+) sub_gr

    15     , XMLTable('/people_infos'

    16         passing sub_gr.people

    17         columns firstname varchar2(100) PATH 'nom'

    18       ) (+) people

    19  ;

     

    GROUP_NAME        SUBGROUP_NAME    FIRSTNAME

    ----------------- ---------------- ------------

    ADMINISTRATIF     SECRETARIAT      DUPONT

    ADMINISTRATIF     SECRETARIAT      MARTIN

    ADMINISTRATIF     LABORATOIRE      LATOUR

    ADMINISTRATIF     FACTURATION     

    ADMINISTRATIF     EXPEDITION       LEMERLU

     

  • 2. Re: data missing
    Jason_(A_Non) Expert
    Currently Being Moderated

    For your specific example, you only needed the Oracle ( +) syntax on your people table and not the sub_gr table as Odie's example shows.  It still works though.

     

    Here is the ANSI style join that Odie mentioned (yes it looks weird, but the PASSING clause is already doing the join for us and the ANSI style requires an ON clause so we simply need something that evaluates to true)

    select gr.group_name,sub_gr.subgroup_name,people.firstname
      from pour_test ,
           XMLTable('for $i in /people/groupe return $i'
                    passing people
                    columns
                    group_name varchar2(100) PATH '@name' ,
                    sous_groupe xmltype PATH 'sous-groupe') gr
           INNER JOIN
           XMLTable('/sous-groupe'
                    passing gr.sous_groupe
                    columns
                    subgroup_name varchar2(100) PATH '@name',
                    people xmltype PATH 'people_infos') sub_gr
           ON 1=1
           LEFT OUTER JOIN
           XMLTable('/people_infos'
                    passing sub_gr.people
                    columns
                    firstname varchar2(100) PATH 'nom') people
           ON 1=1;
    
  • 3. Re: data missing
    odie_63 Guru
    Currently Being Moderated

    For your specific example, you only needed the Oracle ( +) syntax on your people table and not the sub_gr table as Odie's example shows.  It still works though.

     

    There are a few bugs around outer joins usage unfortunately.

    My example above was tested on 11.2.0.2 (XE) where I did require an outer join on the "sub_gr" level.

     

    If I run the same queries on 11.2.0.3, it produces wrong results :


    with one outer join :

    SQL> select gr.group_name

      2       , sub_gr.subgroup_name

      3       , people.firstname

      4  from pour_test

      5     , XMLTable('for $i in /people/groupe return $i'

      6         passing people

      7         columns group_name varchar2(20) PATH '@name'

      8               , sous_groupe xmltype PATH 'sous-groupe'

      9       ) gr

    10     , XMLTable('/sous-groupe'

    11         passing gr.sous_groupe

    12         columns subgroup_name varchar2(20) PATH '@name'

    13               , people xmltype PATH 'people_infos'

    14       ) sub_gr

    15     , XMLTable('/people_infos'

    16         passing sub_gr.people

    17         columns firstname varchar2(20) PATH 'nom'

    18       ) (+) people

    19  ;

     

    GROUP_NAME           SUBGROUP_NAME        FIRSTNAME

    -------------------- -------------------- --------------------

    ADMINISTRATIF        SECRETARIAT         

    ADMINISTRATIF        LABORATOIRE         

    ADMINISTRATIF        FACTURATION         

    ADMINISTRATIF        EXPEDITION        

    with two outer joins :

    SQL> select gr.group_name

      2       , sub_gr.subgroup_name

      3       , people.firstname

      4  from pour_test

      5     , XMLTable('for $i in /people/groupe return $i'

      6         passing people

      7         columns group_name varchar2(20) PATH '@name'

      8               , sous_groupe xmltype PATH 'sous-groupe'

      9       ) gr

    10     , XMLTable('/sous-groupe'

    11         passing gr.sous_groupe

    12         columns subgroup_name varchar2(20) PATH '@name'

    13               , people xmltype PATH 'people_infos'

    14       ) (+) sub_gr

    15     , XMLTable('/people_infos'

    16         passing sub_gr.people

    17         columns firstname varchar2(20) PATH 'nom'

    18       ) (+) people

    19  ;

     

    GROUP_NAME           SUBGROUP_NAME        FIRSTNAME

    -------------------- -------------------- --------------------

    ADMINISTRATIF                          

     

    with three outer joins :

    SQL> select gr.group_name

      2       , sub_gr.subgroup_name

      3       , people.firstname

      4  from pour_test

      5     , XMLTable('for $i in /people/groupe return $i'

      6         passing people

      7         columns group_name varchar2(20) PATH '@name'

      8               , sous_groupe xmltype PATH 'sous-groupe'

      9       ) (+) gr

    10     , XMLTable('/sous-groupe'

    11         passing gr.sous_groupe

    12         columns subgroup_name varchar2(20) PATH '@name'

    13               , people xmltype PATH 'people_infos'

    14       ) (+) sub_gr

    15     , XMLTable('/people_infos'

    16         passing sub_gr.people

    17         columns firstname varchar2(20) PATH 'nom'

    18       ) (+) people

    19  ;

     

    GROUP_NAME           SUBGROUP_NAME        FIRSTNAME

    -------------------- -------------------- --------------------

    ADMINISTRATIF        SECRETARIAT          DUPONT

    ADMINISTRATIF        SECRETARIAT          MARTIN

    ADMINISTRATIF        LABORATOIRE          LATOUR

    ADMINISTRATIF        FACTURATION         

    ADMINISTRATIF        EXPEDITION           LEMERLU

     

    There are some workarounds if we don't want to outer joins unnecessarily, such as wrapping XMLTABLE groups in subqueries using the NO_MERGE hint.

  • 4. Re: data missing
    Jason_(A_Non) Expert
    Currently Being Moderated

    I know the OP was on 11.2.0.3, but got curious.

     

    On both 10.2.0.4 and 11.1.0.6, the three queries you posted all return the same set of correct results, i.e, no difference for the three queries across those two versions.

     

    Alright, this is interesting.  I get back the correct set of results. .... Wonder if due to the WITH clause being used.  Will have to look at that later.

    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
    SQL> WITH pour_test AS
      2  (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
      3  <people xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      4      xsi:noNamespaceSchemaLocation="pour_tests.xsd" societe="COMPAGNIE DUPONT">
      5       <groupe name="ADMINISTRATIF">
      6           <sous-groupe name="SECRETARIAT">
      7               <people_infos>
      8                   <nom>DUPONT</nom>
      9                   <prenom>Pierre</prenom>
     10               </people_infos>
     11               <people_infos>
     12                   <nom>MARTIN</nom>
     13                   <prenom>Roger</prenom>
     14               </people_infos>
     15           </sous-groupe>
     16           <sous-groupe name="LABORATOIRE">
     17               <people_infos>
     18                   <nom>LATOUR</nom>
     19                   <prenom>André</prenom>
     20               </people_infos>
     21           </sous-groupe>
     22           <sous-groupe name="FACTURATION"/>
     23           <sous-groupe name="EXPEDITION">
     24               <people_infos>
     25                   <nom>LEMERLU</nom>
     26                   <prenom>Christian</prenom>
     27               </people_infos>
     28           </sous-groupe>
     29       </groupe>
     30  </people>') people from dual)
     31  select gr.group_name
     32         , sub_gr.subgroup_name
     33         , people.firstname
     34    from pour_test
     35       , XMLTable('for $i in /people/groupe return $i'
     36           passing people
     37           columns group_name varchar2(20) PATH '@name'
     38                 , sous_groupe xmltype PATH 'sous-groupe'
     39         ) gr
     40      , XMLTable('/sous-groupe'
     41          passing gr.sous_groupe
     42          columns subgroup_name varchar2(20) PATH '@name'
     43                , people xmltype PATH 'people_infos'
     44        ) sub_gr
     45      , XMLTable('/people_infos'
     46          passing sub_gr.people
     47          columns firstname varchar2(20) PATH 'nom'
     48        ) (+) people;
    GROUP_NAME           SUBGROUP_NAME        FIRSTNAME
    -------------------- -------------------- --------------------
    ADMINISTRATIF        SECRETARIAT          DUPONT
    ADMINISTRATIF        SECRETARIAT          MARTIN
    ADMINISTRATIF        LABORATOIRE          LATOUR
    ADMINISTRATIF        FACTURATION          
    ADMINISTRATIF        EXPEDITION           LEMERLU

Legend

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