I’m building a summary report of errors - so I can analyze in excel . The datasource is an xml file that I created using the TOXML. I’ve created a SQL command that summarizes my output using the GROUP option which give me a total of errors per error type. This all works perfectly but it looks like the SQL TOXML doesn’t support TO “xmlfile” option so I can’t write the output to a file.
SELECT REPORT_DATE, CURR_DATE_DAY, ERR_CODE, COUNT(ERR_CODE) TOT FROM LOG.ERR WHERE CURR_DATE >= CURRENT_DATE-14 GROUP BY REPORT_DATE,CURR_DATE_DAY,ERR_CODE ORDER BY REPORT_DATE, ERR_CODE TOXML ;
To get around the output constraint, I add LPTR to the command and used the SETPR with the output directed to the &HOLD& file which works. SETPTR 0,80,44,0,0,3,AS ERRORS.XML, BRIEF
The issue I now have is that the first 2 fields in my output are always blank for the XML.
>ED &HOLD& ERRORS.XML 7732 lines long. ----: 0001: ----: 0002: ----: 0003: <?xml version="1.0" encoding="UTF-8"?> ----: 0004: <ROOT>
Excel doesn’t see the file as a valid XML file with these blanks. Any ideas of how to prevent the blank fields, without writing code?