Return to list of articles

This article provides some tips that you may find helpful when making XML files from mainframe data. This is a great way to create an XML file from VSAM or DB2 files.

Making XML files from Mainframe Data

You can easily use Spectrum Writer to change a mainframe "report" into an XML output file. We simply add a few formatting options that are appropriate for producing output files. And we surround the report data with the necessary XML tags. The result is an XML file that can be used to export your mainframe data to a wide variety of other platforms.

Example of Making an XML File

For example, assume that we want to create an XML file containing the data from the following Spectrum Writer report:

Now We Turn the Report Above into an XML File

We use the same INPUT and SORT control statements to select and sort records from the input file. We then change the BREAK and COLUMNS statements, and added some Options, to specify the new formatting desired for our new "report." That is, a format that includes all of the necessary XML tags in their proper location around the actual sales data. Here are the control statements that we use.

OPTION: OUTPUT NOGRANDSPACE COLSPACE(0)
OPTION: PRESCRIPT('<?XML VERSION="1.0" ENCODING="ISO-8859-15"?>')
INPUT: SALES-FILE
SORT: REGION EMPL-NAME SALES-DATE
BREAK: #GRAND NOTOTALS
       HEADING('<REPORT REPORTID="RECENT SALES BY REGION">')
       FOOTING('</REPORT>')
BREAK: REGION NOTOTALS SPACE(0)
       HEADING('   <REGION NAME="' 0 REGION 0 '">')
       FOOTING('   </REGION>')
COLUMN: '      <SALE>'
COLUMN: '         <EMPL-NAME>'  EMPL-NAME  '</EMPL-NAME>'
COLUMN: '         <SALES-DATE>' SALES-DATE '</SALES-DATE>'
COLUMN: '         <SALES-TIME>' SALES-TIME '</SALES-TIME>'
COLUMN: '         <CUSTOMER>'   CUSTOMER   '</CUSTOMER>'
COLUMN: '         <AMOUNT>'     AMOUNT     '</AMOUNT>'
COLUMN: '         <TAX>'        TAX        '</TAX>'
COLUMN: '      </SALE>'

Here is a detailed explanation of The changes made in the control statements above.

The OUTPUT option specifies that Spectrum Writer will produce an output file rather than a report. This option suppresses the default titles and column headings, among other things.
The PRESCRIPT option writes a line of text at the very beginning of the output file. We use this option to specify the one-time XML declaration at the beginning of our XML file.
Report titles are not wanted for an XML output file, so we drop the TITLE statements.
We use HEADING and FOOTING parms in the REGION BREAK statement to supply the opening and closing XML tags for the REGION element of our XML file. We also added an XML attribute (NAME) to the REGION element.
We added a #GRAND "pseudo-break" to produce similar surrounding tags for the report as a whole (the "REPORT" XML element).
In place of the report title, we added a REPORTID XML attribute to the REPORT element's opening XML tag.
Both BREAK statements have the NOTOTALS parm to suppress totals at the control break and the Grand Totals.
The SPACE(0) parm (on the BREAK statement for REGION) suppresses the blank lines that normally appear in a report at a control break.
The NOGRANDSPACE option similarly suppresses the blank lines before the Grand Totals.
We use multiple COLUMNS statements to put each output field onto its own line, and to add the XML opening and closing tags (as literals) around the data.
The COLSPACE(0) option (on the OPTIONS statement) sets the inter-column spacing to 0, eliminating extra blanks between the XML tags and the data itself, on the detail output lines.

And here is the XML file that Spectrum Writer produces from the above control statements.

<?XML VERSION="1.0" ENCODING="ISO-8859-15"?>
<REPORT REPORTID="RECENT SALES BY REGION">
   <REGION NAME="EAST ">
      <SALE>
         <EMPL-NAME>MORRISON  </EMPL-NAME>
         <SALES-DATE>03/29/95</SALES-DATE>
         <SALES-TIME>15:30:22</SALES-TIME>
         <CUSTOMER>STAR MARKET    </CUSTOMER>
         <AMOUNT>         44.35</AMOUNT>
         <TAX>       2.66</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>MORRISON  </EMPL-NAME>
         <SALES-DATE>03/30/95</SALES-DATE>
         <SALES-TIME>19:05:41</SALES-TIME>
         <CUSTOMER>A1 PHOTOGRAPHY </CUSTOMER>
         <AMOUNT>         29.65</AMOUNT>
         <TAX>       1.78</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>SIMPSON   </EMPL-NAME>
         <SALES-DATE>04/01/95</SALES-DATE>
         <SALES-TIME>08:17:57</SALES-TIME>
         <CUSTOMER>EUROPEAN DELI  </CUSTOMER>
         <AMOUNT>         14.99</AMOUNT>
         <TAX>       0.90</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>SIMPSON   </EMPL-NAME>
         <SALES-DATE>04/30/95</SALES-DATE>
         <SALES-TIME>15:30:21</SALES-TIME>
         <CUSTOMER>J & S LUMBER   </CUSTOMER>
         <AMOUNT>         23.87</AMOUNT>
         <TAX>       1.43</TAX>
      </SALE>
   </REGION>
   <REGION NAME="NORTH">
      <SALE>
         <EMPL-NAME>JOHNSON   </EMPL-NAME>
         <SALES-DATE>04/01/95</SALES-DATE>
         <SALES-TIME>17:02:47</SALES-TIME>
         <CUSTOMER>VILLA HOTEL    </CUSTOMER>
         <AMOUNT>        234.45</AMOUNT>
         <TAX>      14.07</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>JOHNSON   </EMPL-NAME>
         <SALES-DATE>04/05/95</SALES-DATE>
         <SALES-TIME>14:33:10</SALES-TIME>
         <CUSTOMER>MARYS ANTIQUES </CUSTOMER>
         <AMOUNT>          9.98</AMOUNT>
         <TAX>       0.60</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>JONES     </EMPL-NAME>
         <SALES-DATE>04/15/95</SALES-DATE>
         <SALES-TIME>13:52:41</SALES-TIME>
         <CUSTOMER>TOY TOWN       </CUSTOMER>
         <AMOUNT>         10.25</AMOUNT>
         <TAX>       0.62</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>JONES     </EMPL-NAME>
         <SALES-DATE>04/15/95</SALES-DATE>
         <SALES-TIME>08:01:59</SALES-TIME>
         <CUSTOMER>TOY TOWN       </CUSTOMER>
         <AMOUNT>        121.76</AMOUNT>
         <TAX>       7.31</TAX>
      </SALE>
      <SALE>
         <EMPL-NAME>JONES     </EMPL-NAME>
         <SALES-DATE>04/15/95</SALES-DATE>
         <SALES-TIME>07:58:32</SALES-TIME>
         <CUSTOMER>EZ GROCERY     </CUSTOMER>
         <AMOUNT>         10.25</AMOUNT>
         <TAX>       0.62</TAX>
      </SALE>
   </REGION>
additional output lines not shown)
</REPORT>

Fine Points

If desired (for esthetic reasons), you can remove the embedded blanks within the elemental data tags. Use Spectrum Writer's #COMPRESS built-in function. For example, instead of this:

COLUMN: '         <EMPL-NAME>' 0 EMPL-NAME 0 '</EMPL-NAME>'

you could use these statements:

COMPUTE: EMPL-NAME-XML = #COMPRESS('<EMPL-NAME>' 0 EMPL-NAME 0 '</EMPL-NAME>')
COLUMN: '        ' EMPL-NAME-XML

Now, rather than this in the XML file:

         <EMPL-NAME>JONES     </EMPL-NAME>

you will get this:

         <EMPL-NAME>JONES</EMPL-NAME>