Generate XML and JSON Data for a Db2 Table or View or based on an SELECT-Statement

This tool includes:

Birgitta Hauser is Software and Database Engineer, focusing on RPG, SQL and Web development on IBM i at Toolmaker Advanced Efficiency GmbH in Germany. She also works in consulting with regard to modernizaing legacy IBM i applications IBM i as well as in education as a trainer for RPG and SQL developers.

Since 2002 she has frequently spoken at the COMMON User Groups and other IBM i and Power Conferences in Germany, other European Countries, USA and Canada.

In addition, she is co-author of two IBM Redbooks and also the author of several articles and papers focusing on RPG and SQL for a German publisher, IBM DeveloperWorks and IT Jungle.

Minimum IBM i Release: 7.2 TR7 (or 7.3 TR3)

The SQL Scripts containing the source code for the stored procedures, can be run with the RUNSQLSTM command:

RUNSQLSTM SRCFILE(YOURSCHEMA/QSQLSRC)   
               SRCMBR(TABLE2JSON)          
               COMMIT(*NONE)               
               NAMING(*SYS)                
               MARGINS(132)                
               DFTRDBCOL(YOURSCHEMA)

It is also possible to run the SQL scripts from the b>RUN SQL SCRIPTING facility in Client Access or (even better) ACS (Access Client Solution).

For the passed table a list of all columns separated by a comma is generated with the LIST_AGG Aggregate function from the SYSCOLUMS view. With this information and the passed parameter information a XMLGROUP Statement is performed that returns the XML data.

The structure of the resulting XML looks as follows:

<rowset>
       <row><COLUMN1>Value1</COLUMN1><COLUMN2>Value2</COLUMN2>
             ... more columns...<COLUMNN>ValueN</COLUMNN><row>
       ... more rows
</rowset>   

If the ParAsAttributes parameter is passed with 'Y' the following structure is returned:

<rowset>
    <row COLUMN1="Value1" COLUMN2="Value2" ... more columns ... COLUMNN="ValueN" />
         ... more rows
<rowset>
 

Values(Table2XML('ADDRESSX', 'HSCOMMON10'));

Values(Table2XML('ADDRESSX', 'HSCOMMON10',
                 ParWhere       => 'ZipCode between ''70000'' and ''80000''',
                 ParOrderBy     => 'ZipCode, CustNo'));

Call WrtXML2IFS_Create(Table2XML('SALES', 'HSCOMMON10', 
                                 ParWhere        => 'Year(SalesDate) = 2018', 
                                 ParOrderBy      => 'SalesDate, CustNo Desc',
                                 ParRoot         => '"Sales"',
                                 ParRow          => '"SalesRow"',
                                 ParAsAttributes => 'Y'),
                        '/home/Hauser/Umsatz20180224.xml'); 

For the passed table a list containing with columns separated by a comma is generated with the ListAgg Aggregate function from the SYSCOLUMS view. With this information and the passed parameter information and a composition of the JSON_ArrayAgg and JSON_Object functions the JSON Data is created.

The structure of the resulting JSON data looks as follows:

 {
	"success": true,
	"errmsg": "",
	"Table": "TABLENAME",
	"Schema": "TBLSCHEMA",
	"Data": [{
		  "COLUMN1": "Value1",
		  "COLUMN2": 123.45,
                  ... More Columns
		 },
                 ... more rows
          ]
  }         
  

Values(Table2JSON('ADDRESSX', 'HSCOMMON10'));

Values(Table2JSON('ADDRESSX', 'HSCOMMON10',
                  ParWhere     => 'ZipCode between ''70000'' and ''80000''',
                  ParOrderBy   => 'ZipCode, CustNo'));

Values(Table2JSON('ADDRESSX', 'HSCOMMON10',
                  ParWhere       => 'ZipCode between ''70000'' and ''80000''',
                  ParOrderBy     => 'ZipCode, CustNo'),
		  ParDataName    => 'AddressInfo',
		  ParInclSuccess => '1',
		  ParNamesLower  => '1');

Call WrtJSON2IFS_Create(Table2JSON('SALES', 'HSCOMMON10', 
                                   ParWhere    => 'Year(SalesDate) = 2017', 
                                   ParOrderBy  => 'SalesDate, CustNo Desc',
                                   ParRoot     => '"Sales"'),         
                        '/home/Hauser/Umsatz20180224.json');

Almost any SELECT-Statement including those SQL statements that include Common Table Expressions or Nested Sub-Selects can be converted.

The structure of the resulting XML document is the same as the structure of the XML document returned by the Table2XML UDF.

Values(Select2XML('Select * from HSCOMMON10.Sales Where Year(SalesDate) = 2017'));

Select Statement with Group By and Order By Clauses. Generated columns Year(SalesDate) and Sum(Amount) are named, i.e. Year(SalesDate) --> SalesYear and Sum(Amount)

Values(Select2XML('Select Year(SalesDate) as SalesYear, CustNo, Sum(Amount) Total 
                     From Sales
                     Where CustNo in (''10001'', ''10003'')
                     Group By Year(SalesDate), CustNo
                     Order By SalesYear',
                     '"SalesCustYear"', '"CustYear"', 'Y'));

Select Statement with Common Table Expression and multiple joins within the final select.

Values(Select2XML('With Pos as (Select Company, OrderNo, Count(*) NbrOfPositions
                                   from OrderDetX
                                   Group By Company, OrderNo)
                   Select H.Company, H.OrderNo, H.CustNo, CustName1, 
                          Trim(ZipCode) concat '' - '' concat Trim(City) as ZipCodeCity,
                          NbrOfPositions  
                      from OrderHdrx h join Addressx a on a.CustNo = h.CustNo
                      join Pos p on     h.Company = p.Company
                                    and h.OrderNo = p.OrderNo'));             

Almost any SELECT-Statement including those with Common Table Expressions or Nested Sub-Selects can be converted

The structure of the resulting JSON data is the same as the structure of the JSON data returned by the Table2JSON UDF.

Values(Select2JSON('Select * from HSCOMMON10.Sales Where Year(SalesDate) = 2018'));

Select Statement with Group By and Order By Clauses. Generated columns Year(SalesDate) and Sum(Amount) are named, i.e. Year(SalesDate) --> SalesYear and Sum(Amount)

Values(Select2JSON('Select Year(SalesDate) as SalesYear, CustNo, Sum(Amount) Total 
                     From Sales
                     Where CustNo in (''10001'', ''10003'')
                     Group By Year(SalesDate), CustNo
                     Order By SalesYear'));

Values(Select2JSON('Select Year(SalesDate) as SalesYear, CustNo, Sum(Amount) Total 
                     From Sales
                     Where CustNo in (''10001'', ''10003'')
                     Group By Year(SalesDate), CustNo
                     Order By SalesYear'),
		     ParDataName    => "SalesCustYear",
		     ParInclSuccess => '1',
		     ParNamesLower  => '1');

Select Statement with Group By and Order By Clauses. Generated columns Year(SalesDate) and Sum(Amount) are named, i.e. Year(SalesDate) --> SalesYear and Sum(Amount)

Values(Select2JSON('With Pos as (Select Company, OrderNo, Count(*) NbrOfPositions
                                   from OrderDetX
                                   Group By Company, OrderNo)
                   Select H.Company, H.OrderNo, H.CustNo, CustName1, 
                          Trim(ZipCode) concat '' - '' concat Trim(City) as ZipCodeCity,
                          NbrOfPositions  
                      from OrderHdrx h join Addressx a on a.CustNo = h.CustNo
                      join Pos p on     h.Company = p.Company
                                    and h.OrderNo = p.OrderNo'));