Generate XML and JSON Data for a Db2 Table or View or based on an SELECT-Statement
This tool includes:
| SQL User Defined Function | TABLE2XML | for generating the XML data for a complete Db2 table |
| SQL User Defined Function | TABLE2JSON | for generating the JSON data for a complete Db2 table |
| SQL User Defined Function | SELECT2XML | for generating the XML data for an SQL Select Statement |
| SQL User Defined Function | SELECT2JSON | for generating the JSON data for an SQL Select Statement |
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).
| Attention: | The database objects are not qualified in the SQL script, so you need to add YOURSCHEMA to the script by yourself. |
| Parameter Name | Data Type/Length | Description |
|---|---|---|
| ParTable | VarChar(128) | Table (SQL Name) to be converted into XML |
| ParSchema | VarChar(128) | Schema (SQL Name) of the table to be converted into XML |
| ParWhere | VarChar(4096) | Additional Where Conditions (without leading WHERE) for reducing the data (Optional --> Default = ‘’) |
| ParOrderBy | VarChar(1024) | Order by clause (without leading ORDER BY) for sorting the result (Optional --> Default = ‘’) |
| ParRoot | VarChar(128) | Name of the Root Element (Optional --> Default = ‘”rowset”’) |
| ParRow | VarChar(128) | Name of the Row Element (Optional --> Default = ‘”row”’) |
| ParAsAttributes | VarChar(1) | Y = single empty element per row, all column data are passed as attributes (Optional --> Default = ‘’) |
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');
| Note: | The WrtXML2IFS_Create stored procedure is open source. The WrtXML2IFS_Create stored procedure will write the result from the TABLE2XML function into the IFS. |
| Parameter Name | Data Type/Length | Description |
|---|---|---|
| ParTable | VarChar(128) | Table (SQL Name) to be converted into JSON |
| ParSchema | VarChar(128) | Schema (SQL Name) of the table to be converted into JSON |
| ParWhere | VarChar(4096) | Additional Where Conditions (without leading WHERE) for reducing the data (Optional => Default = ‘’) |
| ParOrderBy | VarChar(1024) | Order by clause (without leading ORDER BY) for sorting the result (Optional => Default = ‘’) |
| ParInclTableInfo | VarChar(1) | Including Table and Schema information Any value except blank --> Table/Schema information is included |
| ParDataName | VarChar(128) | Name of the data Array --> Default = "Data" |
| ParInclSuccess | VarChar(1) | Includes "success":true and "errmsg":"" at the beginning of the data Any value except blank --> success/errmsg information is included |
| ParNamesLower | VarChar(1) | Converts the keynames into lower case Any value except blank --> keynames are converted into lower case |
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');
| Parameter Name | Data Type/Length | Description |
|---|---|---|
| ParSelect | VarChar(32700) | SQL Select-Statement to be converted into XML |
| ParRoot | VarChar(128) | Name of the Root Element (Optional --> Default = ‘”rowset”’) |
| ParRow | VarChar(128) | Name of the Row Element (Optional --> Default = ‘”row”’) |
| ParAsAttributes | VarChar(1) | Y = single empty element per row, all column data are passed as attributes (Optional --> Default = ‘’) |
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.
| Attention: | All Columns returned by the SELECT statement need to be named. All Column names are converted into uppercase Column names embedded in double quotes are currently not supported |
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'));
)
| Parameter Name | Data Type/Length | Description |
|---|---|---|
| ParSelect | VarChar(32700) | SQL Select-Statement to be converted into XML |
| ParDataName | VarChar(128) | Name of the Data Array --> Default = "Data" |
| PARInclSuccess | VarChar(1) | Includes "success"=true, "errmsg"=""
at the beginning of the data Any value except blank --> success/errmsg is included |
| ParNamesLower | VarChar(1) | Converts the keynames into lower case Any value except blank --> keynames are converted into lowercase |
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.
| Attention: | All Columns returned by the SELECT statement need to be named. All Column names are converted into uppercase Column names embedded in double quotes are currently not supported |
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'));