3 Replies Latest reply: Apr 3, 2013 12:57 PM by 708631 RSS

    xml grouping question

    708631
      Trying to have multiple groups in xml, not sure how to get it to work.
      create table "TEST" (
      partner varchar2(100),
      phase varchar2(100),
      country varchar2(100),
      state varchar2(2),
      county varchar2(100),
      number1 varchar2(100),
      number2 varchar2(100),
      number3 varchar2(100)
      
      );
      
      
      insert into test values ('partner1','phase1','USA','ME','county3','111','222','333');
      insert into test values ('partner1','phase2','USA','NY','county1','111','222','333');
      insert into test values ('partner1','phase1','Russia','NY','county2','111','222','333');
      insert into test values ('partner1','phase1','USA','FL','county1','111','222','333');
      insert into test values ('partner2','phase2','USA','MD','county1','111','222','333');
      insert into test values ('partner2','phase1','China','NY','county1','111','222','333');
      insert into test values ('partner2','phase2','USA','NY','county1','111','222','333');
      insert into test values ('partner2','phase1','Russia','FL','county1','111','222','333');
      insert into test values ('partner3','phase1','USA','NY','county1','111','222','333');
      insert into test values ('partner3','phase2','USA','NY','county1','111','222','333');
      insert into test values ('partner3','phase1','USA','CA','county1','111','222','333');
      
      
      
      Want xml to look something like :
      
      
      <XML>
        <PARTNER=1> 
            <PHASE 1>
                  <COUNTRY>
                          <STATE>
                                    <COUNTY>
                                                <NUMBER1 =1111>
                                                <NUMBER2 =2222>
                                                <NUMBER3 =3333>
                                    </COUNTY>
                          </STATE>
                  <COUNTRY>
                                    
            
            </PHASE 1>
            <PHASE 2>
            
            
            </PHASE 2>
            <PHASE 3>
            
            </PHASE 3>
            
      
        </PARTNER>
        .
        .
        .
        
        <PARTNER =2> 
        
        </PARTNER>
        
        <PARTNER =3> 
        
        </PARTNER>
          
      </XML>
      so first grouped by partner, then grouped inside partner by phase, then after that by country, etc..
      any help is appreciated. thank you
        • 1. Re: xml grouping question
          IckyIckyChiMoon
          I messed with your data a little, but you start by formatting your non-aggregated info (the numbers), then work your way backwards aggregating one thing at a time:
          with
            w_data  as
              ( select  NULL  as partner
                     ,  NULL  as phase
                     ,  NULL  as country
                     ,  NULL  as state
                     ,  NULL  as county
                     ,  NULL  as number1
                     ,  NULL  as number2
                     ,  NULL  as number3
                  from  dual  where 0=1
                union select  'p1', 'f1', 'USA',    'FL', 'c1', '100', '20', '1110'  from dual
                union select  'p1', 'f1', 'USA',    'FL', 'c2', '101', '21', '1111'  from dual
                union select  'p1', 'f1', 'USA',    'ME', 'c3', '102', '22', '1112'  from dual
                union select  'p1', 'f2', 'USA',    'NY', 'c4', '103', '23', '1113'  from dual
                union select  'p2', 'f2', 'USA',    'MD', 'c5', '104', '24', '1114'  from dual
                union select  'p2', 'f1', 'China',  'NY', 'c6', '105', '25', '1115'  from dual
                union select  'p2', 'f2', 'USA',    'NY', 'c7', '106', '26', '1116'  from dual
                union select  'p2', 'f1', 'Russia', 'FL', 'c8', '107', '27', '1117'  from dual
                union select  'p3', 'f1', 'USA',    'NY', 'c9', '108', '28', '1118'  from dual
                union select  'p3', 'f2', 'USA',    'NY', 'c0', '109', '29', '1129'  from dual
                union select  'p3', 'f1', 'USA',    'CA', 'cx', '110', '30', '1130'  from dual
              ) 
          , level_1  as  /* No-Aggregates */
              ( select  partner
                     ,  phase
                     ,  country
                     ,  state
                     ,  county
                     ,  xmlelement
                          ( "county"
                          , xmlattributes ( county as "County" )
                          , xmlelement ( "n1", number1 )
                          , xmlelement ( "n2", number2 )
                          , xmlelement ( "n3", number3 )
                          ) as x1
                  from  w_data
              )
          , level_2  as
              ( select  partner, phase, country, state
                     ,  xmlelement
                          ( "state"
                          , xmlattributes ( state as "State" )
                          , xmlagg ( x1 )
                          ) as x2
                  from  level_1
                 group  by  partner, phase, country, state
              )
          , level_3  as
              ( select  partner, phase, country
                     ,  xmlelement
                          ( "country"
                          , xmlattributes ( country as "Country" )
                          , xmlagg ( x2 )
                          ) as x3
                  from  level_2
                 group  by  partner, phase, country
              )
          , level_4  as
              ( select  partner, phase
                     ,  xmlelement
                          ( "phase"
                          , xmlattributes ( phase as "Phase" )
                          , xmlagg ( x3 )
                          ) as x4
                  from  level_3
                 group  by  partner, phase
              )
          , level_5  as
              ( select  partner
                     ,  xmlelement
                          ( "partner"
                          , xmlattributes ( partner as "Partner" )
                          , xmlagg ( x4 )
                          ) as x5
                  from  level_4
                 group  by  partner
              )
          select  xmlelement
                    ( "XML-Doc"
                    , xmlagg ( x5 )
                    ) as x6
            from  level_5
          ;
           
          The XML output looks like this:
          <XML-Doc>
             <partner Partner="p1">
                <phase Phase="f1">
                   <country Country="USA">
                      <state State="FL">
                         <county County="c1">
                            <n1>100</n1>
                            <n2>20</n2>
                            <n3>1110</n3>
                         </county>
                         <county County="c2">
                            <n1>101</n1>
                            <n2>21</n2>
                            <n3>1111</n3>
                         </county>
                      </state>
                      <state State="ME">
                         <county County="c3">
                            <n1>102</n1>
                            <n2>22</n2>
                            <n3>1112</n3>
                         </county>
                      </state>
                   </country>
                </phase>
                <phase Phase="f2">
                   <country Country="USA">
                      <state State="NY">
                         <county County="c4">
                            <n1>103</n1>
                            <n2>23</n2>
                            <n3>1113</n3>
                         </county>
                      </state>
                   </country>
                </phase>
             </partner>
             <partner Partner="p2">
                <phase Phase="f1">
                   <country Country="China">
                      <state State="NY">
                         <county County="c6">
                            <n1>105</n1>
                            <n2>25</n2>
                            <n3>1115</n3>
                         </county>
                      </state>
                   </country>
                   <country Country="Russia">
                      <state State="FL">
                         <county County="c8">
                            <n1>107</n1>
                            <n2>27</n2>
                            <n3>1117</n3>
                         </county>
                      </state>
                   </country>
                </phase>
                <phase Phase="f2">
                   <country Country="USA">
                      <state State="MD">
                         <county County="c5">
                            <n1>104</n1>
                            <n2>24</n2>
                            <n3>1114</n3>
                         </county>
                      </state>
                      <state State="NY">
                         <county County="c7">
                            <n1>106</n1>
                            <n2>26</n2>
                            <n3>1116</n3>
                         </county>
                      </state>
                   </country>
                </phase>
             </partner>
             <partner Partner="p3">
                <phase Phase="f1">
                   <country Country="USA">
                      <state State="CA">
                         <county County="cx">
                            <n1>110</n1>
                            <n2>30</n2>
                            <n3>1130</n3>
                         </county>
                      </state>
                      <state State="NY">
                         <county County="c9">
                            <n1>108</n1>
                            <n2>28</n2>
                            <n3>1118</n3>
                         </county>
                      </state>
                   </country>
                </phase>
                <phase Phase="f2">
                   <country Country="USA">
                      <state State="NY">
                         <county County="c0">
                            <n1>109</n1>
                            <n2>29</n2>
                            <n3>1129</n3>
                         </county>
                      </state>
                   </country>
                </phase>
             </partner>
          </XML-Doc>
          • 2. Re: xml grouping question
            708631
            thanks so much for this.
            • 3. Re: xml grouping question
              708631
              this worked good