Guide: Extracting data from an IBM i Spooled File to XML

Using CoolSpools to Extract data from a Spooled File to XML

You may have a scenario where you need to generate an XML document from data that is only available to you as an IBM i Spooled File (e.g. a standard report generated by a third party software package), or have an existing report containing complex calculated values whose output has been verified, but now needs to be distributed as XML without duplicating complex report logic. CoolSpools allows you to convert the content of any spooled file report to XML using commands that run natively on your IBM i server. Conversion from a spooled file to an XML document can be achieved by using the following CoolSpools commands.

WRKRPTDFN – Work With Report Definition

Since the structure of the report may be complex, it is necessary to create a report definition to identify different types of lines within the report (e.g. headings, details and totals) and the position of data elements within those lines. The report definition allows you to convert a simple transaction listing with header and detail lines, or a complex report with multiple sub-headings and details presented over multiple lines. Once defined, the report definition can also be used for conversion of your spooled file content to other output types, such as MS Excel or an IBM i database table.

Run command WRKRPTDFN, and press F6 to add a new report definition or select option 2 to change an existing one. You will be presented with a list of spooled files – select the spooled file that you wish to use as a template for the conversion (or press F14 to change the spooled file selection criteria if the required spooled file does not appear).
The content of the selected spooled file will be presented on screen – you can use F7 and F8 to move backwards and forwards through the report pages.

Position the cursor on the line that you wish to define and press F10 to define the line type. Give the line type a name (e.g. “Detail”) and specify the page and/or line numbers on which it can appear. Optionally you can apply additional criteria in the form of rules (e.g. this line type always contains specific characters at a fixed position on the line). When you have defined your line type you can press F9 to save it. You will see that the colour of the defined lines will change – if too many lines have changed colour then you may need to apply stricter rules. You can position the cursor on the line and press F10 again to modify the definition of the line type.

Once you are happy that the line type is correctly identified, you can then define the position and type of the items (data elements) within that line. Place the cursor on any line of the required type and press F10 to open the line definition – the content of the selected line will be displayed near the top of the screen. Position the cursor to the start of an item within that line and press F10 to define it. Enter a name, length and type for the item and press F9 to save the definition. Repeat this process until all items are defined. Keep a note of the name that you assign to each line and item, as you will need these for the next step.

Creating the report definition is a one-off process, and it should only need to be revised if the layout of the spooled file report changes.
See the CoolSpools documentation for a full description of the commands and parameters relating to Report Definitions.

WRKRPTXML – Work With Report to XML Map

In order to map the data elements from your report definition to the elements and attributes of an XML document, a report to XML map definition is required. Run command WRKRPTXML and press F6 to add, or option 2 to change, a map definition. Enter the name of the report to XML map, and the name of the report definition defined above. You can now create the XML schema by using option 8 to drill down into the elements, creating a root element at the top level, and then using option 8 to drill down again into a nested element structure.

If the schema element is a parent that contains other element tags, but does not have a data value itself, then specify the data source as *NONE. If the element should contain data from your spooled file then specify a data source of *ITEM, and specify the line type and name of the data element. You can also use *CONSTANT to populate an XML element with a static data value, or to use a CoolSpools variable or function, as described in the documentation.

If an element contains attributes then these can be defined by entering option 9 against the relevant element and then F6 to add an attribute. This can also be populated from a report definition item or a constant value.

Creating the report to XML map should be a one-off process, and it should only need to be revised if the content of the spooled file report and/or the XML schema changes.
See the CoolSpools documentation for a full description of the commands and parameters relating to Report to XML maps.

CVTSPLXML – Convert Spooled File to XML

To run the conversion from spooled file to XML execute the command CVTSPLXML, specifying the spooled file to convert and the report to XML map name. Use the TOSTMF() parameter to specify the name of the XML file to be generated on your IFS, or *FTP in order to send the file to a remote FTP server. Optionally you can specify the parameter EMAIL(*YES) in order to deliver the converted XML document by email – associated parameters allow you to enter the sender and recipient email addresses, the message subject and body.

Note that you can split a single spooled file into multiple XML documents by using the SPLIT() parameter. You can split the output after a fixed number of spooled file pages using SPLIT(*PAGE), on a change of data value in a defined position on the page using SPLIT(*POS), or on a change of data value in a position relative to a specified text value (such as a field label) using SPLIT(*KEY). When using SPLIT() with EMAIL() you can use data values from within the spooled file to lookup the relevant email address for distribution each of the output XML documents.

See the CoolSpools documentation for a full description of the CVTSPLXML command and its parameters.