This discussion is archived
2 Replies Latest reply: Feb 28, 2013 8:07 AM by chris001 RSS

XML query structure problem

chris001 Newbie
Currently Being Moderated
Hi experts,
I am looking for help on the following XML query. I have a table (example) organized as such:
meas     data_element     
------- ------------------- 
ABC     rate                   
DEF     rate1                    
DEF     rate2                    
DEF     rate3                    
GHI     rate                    
JKL     rate1a            
JKL     rate2b             
I'm trying to keep the "meas" column as the top node attribute, with the "data_elements" as attributes of the children of "meas"

In short, I'm looking for the following output, but, well..keep coming up short.
<meas id="ABC"> 
     <data_elements>
        <data_element id="rate">
            <rpt>false</rpt> 
         </data_element>
    </data_elements>
</meas>
<meas id="DEF">
     <data_elements>
          <data_element id="rate1">
               <rpt>false</rpt>
          </data_element>
                 <data_element id="rate2">
               <rpt>false</rpt> 
          </data_element>
          <data_element id="rate3">
                  <rpt>false</rpt> 
          </data_element>
     <data_elements>
</meas>
.....and so forth...
I'm having trouble with getting the multiple rows of 'data_element' under 'data_elements', and under the single 'meas' one node, any suggestions? My code keeps generating multiple rows. The rpt node is a constant value of 'false'
Thanks
  • 1. Re: XML query structure problem
    odie_63 Guru
    Currently Being Moderated
    You have to group, and aggregate using XMLAgg :
    SQL> set long 5000
    SQL> 
    SQL> with sample_data (meas, data_element) as (
      2    select 'ABC',     'rate'   from dual union all
      3    select 'DEF',     'rate1'      from dual union all
      4    select 'DEF',     'rate2'  from dual union all
      5    select 'DEF',     'rate3'  from dual union all
      6    select 'GHI',     'rate'   from dual union all
      7    select 'JKL',     'rate1a' from dual union all
      8    select 'JKL',     'rate2b' from dual
      9  )
     10  select xmlserialize(content
     11           xmlagg(
     12             xmlelement("meas",
     13               xmlattributes(meas as "id")
     14             , xmlelement("data_elements",
     15                 xmlagg(
     16                   xmlelement("data_element",
     17                     xmlattributes(data_element as "id")
     18                   , xmlelement("rpt", 'false')
     19                   )
     20                   order by data_element -- if necessary
     21                 )
     22               )
     23             )
     24           )
     25           indent
     26         ) as result
     27  from sample_data
     28  group by meas ;
     
    RESULT
    --------------------------------------------------------------------------------
    <meas id="ABC">
      <data_elements>
        <data_element id="rate">
          <rpt>false</rpt>
        </data_element>
      </data_elements>
    </meas>
    <meas id="DEF">
      <data_elements>
        <data_element id="rate1">
          <rpt>false</rpt>
        </data_element>
        <data_element id="rate2">
          <rpt>false</rpt>
        </data_element>
        <data_element id="rate3">
          <rpt>false</rpt>
        </data_element>
      </data_elements>
    </meas>
    <meas id="GHI">
      <data_elements>
        <data_element id="rate">
          <rpt>false</rpt>
        </data_element>
      </data_elements>
    </meas>
    <meas id="JKL">
      <data_elements>
        <data_element id="rate1a">
          <rpt>false</rpt>
        </data_element>
        <data_element id="rate2b">
          <rpt>false</rpt>
        </data_element>
      </data_elements>
    </meas>
     
  • 2. Re: XML query structure problem
    chris001 Newbie
    Currently Being Moderated
    Odie,
    You are too good at this stuff. Thanks again.

Legend

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