'Create Nested XML Document from Multiple Tables with Multiple Rows - SQL Query

I am trying to create an XML Document via SQL in an RPG program and I can't seem to find a way to make the XML look like this.

<ReplenishmentTask>
    <TaskID>MTEST20 </TaskID>
    <ContainerID> </ContainerID>
    <ReplenishmentLine>
        <PART> 2P8267 </PART>
        <QTY>2</QTY>
    </ReplenishmentLine>
    <ReplenishmentLine>
        <PART> 2P8267 </PART>
        <QTY>2</QTY>
    </ReplenishmentLine>
</ReplenishmentTask>
<ReplenishmentTask>
    <TaskID>MTEST20 </TaskID>
    <ContainerID> </ContainerID>
    <ReplenishmentLine>
        <PART> 2P8267 </PART>
        <QTY>2</QTY>
    </ReplenishmentLine>
    <ReplenishmentLine>
        <PART> 2P8267 </PART>
        <QTY>2</QTY>
    </ReplenishmentLine>
</ReplenishmentTask>

This is what my code looks like:

  SELECT XMLDOCUMENT(                                                    
        XMLELEMENT(NAME "Download",                                                         
           XMLAGG(                                                   
            XMLELEMENT(NAME "ReplenishmentTask",                     
              XMLFOREST(a.FIELD1 as "TaskID",                        
                        a.FIELD2 as "ContainerID",                   
                    ( SELECT                                         
                        XMLDOCUMENT(XMLAGG(                          
                                 XMLFOREST( b.PART as "PART",      
                                            b.ORQY   as "QTY" )      
                               ))  // end AGG and DOCUMENT  
                               FROM TABLE2 b                       
                                WHERE b.FIELD1 = A.FIELD1            
                    ) AS "ReplenishmentLine"  // end SELECT          
              )  // end FOREST - Inv                                 
            )    // end ELEMENT - ReplenishmentTask                  
           )     // end AGG                                                                  
         )       // end ELEMENT - Download            
        )        // end DOCUMENT                                                              
FROM TABLE1 a                                          
 ;

and this is the result - I need to remove the additional XML tag ReplenishmentLine but couldn't because the subselect also needs to have an XML tag too if i use XMLForest. Could there be another way?

<ReplenishmentTask>
<TaskID>MTEST20 </TaskID>
<ContainerID> </ContainerID>
<ReplenishmentLine>
    <TESTT> 
        <PART> 2P8267 </PART>
        <QTY>2</QTY>
    </TESTT>
</ReplenishmentLine>
</ReplenishmentTask>
<ReplenishmentTask>
    <TaskID>MTEST40 </TaskID>
    <ContainerID> </ContainerID>
    <ReplenishmentLine>
        <TESTT>
            <PART> 2P8267 </PART>
            <QTY>3</QTY>
        </TESTT>
        <TESTT>
            <PART> 4C3384 </PART>
            <QTY>3</QTY>    
        </TESTT>
    </ReplenishmentLine>
</ReplenishmentTask>
sqlxmlnestedxmldocumentdb2-400


Solution 1:[1]

In SQL SERVER it would be more or less like this:

SELECT a.FIELD1 as "TaskID",                        
   a.FIELD2 as "ContainerID",                                   
   ( SELECT b.PART as "PART",      
            b.ORQY   as "QTY" 
      FROM TABLE2 b                       
      WHERE b.FIELD1 = A.FIELD1            
       FOR XML PATH('ReplenishmentLine')) 
FROM TABLE1 a
FOR XML PATH('ReplenishmentTask')

It really depends on what you would need.

Solution 2:[2]

After trying other ways I have done this instead. I just closed the XML forest and put a comma before the second select.

  SELECT XMLDOCUMENT(                                                    
        XMLELEMENT(NAME "Download",                                                         
           XMLAGG(                                                   
            XMLELEMENT(NAME "ReplenishmentTask",                     
              XMLFOREST(a.FIELD1 as "TaskID",                        
                        a.FIELD2 as "ContainerID"), > added closing parenthesis so the next select will not be considered                   
                    ( SELECT                                         
                        XMLDOCUMENT(XMLAGG(                          
                                 XMLFOREST( b.PART as "PART",      
                                            b.ORQY   as "QTY" )      
                               ))  // end AGG and DOCUMENT  
                               FROM TABLE2 b                       
                                WHERE b.FIELD1 = A.FIELD1            
                    )  //removed this > AS "ReplenishmentLine"  // end SELECT          
              )  // end FOREST - Inv                                 
            )    // end ELEMENT - ReplenishmentTask                  
           )     // end AGG                                                                  
         )       // end ELEMENT - Download            
        )        // end DOCUMENT                                                              
FROM TABLE1 a

Thank you!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dharman
Solution 2 plshelp

Related Questions