Select from a table as xml grouped by more than one level

Question

I'm using DB2 for z/OS V10.

I need to select some data from a DB2 table direcly into XML with grouping the data on more then 1 level like the following example

<actionCodeGrp>
   <actionCode>A</actionCode>
   <ISINGrp>
      <ISIN>DE0000000001</ISIN>
      <NAME>ISIN-DE-1</NAME>
      <ISSUER>AAA</ISSUER>
   </ISINGrp>
   <ISINGrp>
      <ISIN>DE0000000002</ISIN>
      <NAME>ISIN-DE-2</NAME>
      <ISSUER>BBB</ISSUER>
   </ISINGrp>
</actionCodeGrp>
<actionCodeGrp>
   <actionCode>B</actionCode>
   <ISINGrp>
      <ISIN>DE0000000003</ISIN>
      <NAME>ISIN-DE-3</NAME>
      <ISSUER>CCC</ISSUER>
   </ISINGrp>
   <ISINGrp>
      <ISIN>DE0000000004</ISIN>
      <NAME>ISIN-DE-4</NAME>
      <ISSUER>DDD</ISSUER>
   </ISINGrp>
</actionCodeGrp>

The columns named above are all part of the same relational table. All examples I found were only with max. one group by clause and I have no idea how to write a more complex query. Can someone help?

Do you think posting the table structure with some sample data might be helpful? — mustaccio, Jul 13 '16 at 11:59
we the example table and what you tried and how that did not match your expected result. There is no way to tell what you want from this question. — Hogan, Jul 13 '16 at 17:48

score 0 · Answer 1 · answered Jul 14 '16 at 08:50

To group your data over several levels into an XML-document does not seem to be too well supported, but I made it work using nested subselects:

SELECT XMLSERIALIZE(CONTENT                                  
       XMLELEMENT(NAME "FIELD1",                             
                  XMLATTRIBUTES(F1),                      
                  XMLAGG(SUBELEMENT))                        
       AS CLOB)                                              
FROM(                                                        
SELECT F1,                                                
       XMLELEMENT(NAME "FIELD2",                             
                  XMLATTRIBUTES(F2),               
                  XMLAGG(XMLELEMENT(NAME "FIELD3",           
                         XMLATTRIBUTES(F3))))    
        AS SUBELEMENT                                        
FROM DBTM42VW.KFZT525                                        
WHERE F1 IN ('VAL1','VAL2')                                 
  AND F3 IN('SEF','AUS')                         
  GROUP BY F1, F2) MYSUBQUERY                     
GROUP BY F1                                               
;

So basically the inner SELECT generates a list of raw XML-elements containing the inner groups plus the columns to be used in the surrounding elements. Then the outer SELECT uses this data to create groups from these groups. So the result will be something like this:

<FIELD1 F1="VAL1">
    <FIELD2 F2="FM124">
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
    </FIELD2>
    <FIELD2 F2="FM132">
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
    </FIELD2>
</FIELD1>
<FIELD1 F1="VAL2">
    <FIELD2 F2="FM124">
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
    </FIELD2>
    <FIELD2 F2="FM132">
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="SEF"/>
        <FIELD3 F3="AUS"/>
        <FIELD3 F3="SEF"/>
    </FIELD2>
</FIELD1>

Select from a table as xml grouped by more than one level

1 Answers1