Products Resources Support About Us

Output of SQL TOXML statement to a file

Hi,
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.

command:

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?

Thanks,
Greg

Try the TO clause.

i.e.
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 myfile;

It will create the XML file in &XML& or XML depending on the database.

The “TO” option fails when I add it to a SQL TOXML statement. Works with a LIST statement but not working on SQL