2 Replies Latest reply: Feb 28, 2013 10:07 AM by chris001 RSS

    XML query structure problem

    chris001
      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
          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
            Odie,
            You are too good at this stuff. Thanks again.