Search code examples
xmlibm-midrangerpgle

Using XML Functions(XMLAGG) in RPGLE

I have a table(TableA) from which I am creating a single XML from 2 of its fields (Fld1 and Fld2)

 select  
   XMLSERIALIZE(
     XMLAGG(
       XmlElement(Name "Tag", 
       XmlElement(Name "PARTNUMBER",  RTrim(Fld1)), 
       XmlElement(Name "PART_DESCRIPTION", RTrim(Fld2)))
     )
  AS varchar(2000)  )  
   From TableA

The Output is as below:

<Tag><PARTNUMBER>401791</PARTNUMBER><PART_DESCRIPTION>ECM-NO START,ME17</PART_DESCRIPTION></Tag><Tag><PARTNUMBER>401792</PARTNUMBER><PART_DESCRIPTION>CALIBRATION-111,IND,18,NA</PART_DESCRIPTION></Tag>

Now my requirement is that the should only come once. It should be binding the entire XML and not for each row.

The required Output should be as below:

 <Tag><PARTNUMBER>401791</PARTNUMBER><PART_DESCRIPTION>ECM-NO> START,ME17</PART_DESCRIPTION><PARTNUMBER>401792</PARTNUMBER><PART_DESCRIPTION>CALIBRATION-111,IND,18,NA</PART_DESCRIPTION></Tag>

Please Help!

Solution

  • For the output you are requesting, this should do the trick...

        SELECT 
        XMLELEMENT(NAME "TAG",
            XMLAGG(
                XMLFOREST(RTRIM(Fld1) AS "PARTNUMBER",
                          RTRIM(Fld2) AS "PART_DESCRIPTION"
                )
            )
        )

        AS XML_EXAMPLE 
     FROM TableA

    Now after you have generated that, you can throw this inside of another select statement and use XMLSERIALIZE to add in whatever you need at the beginning.

    Also, here is the IBM documentation on the three XML functions used above.

    XMLELEMENT: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzscaxmlelem.htm

    XMLAGG: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcolxmlagg.htm

    XMLFOREST: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzscaxmlforest.htm