Parsing XML and JSON

Parsing XML

In addition to reading data from an XML/JSON file via a form load, you can also use the XMLPARSE command. When the file contains several instances per tab, include the –all parameter to parse the entire file. Omit it to limit results to the first instance of each tab.

Note: XMLPARSE can read a maximum of 1023 characters in a single XML tag / JSON field.

Example:

SELECT SQL.TMPFILE INTO :OUTXMLTAB1 FROM DUMMY;
 SELECT SQL.TMPFILE INTO :OUTXMLTAB2 FROM DUMMY; 
 SELECT SQL.TMPFILE INTO :MSG FROM DUMMY; 
 LINK INTERFXMLTAGS I1 TO :OUTXMLTAB1;
 GOTO 500 WHERE :RETVAL <= 0; 
 LINK INTERFXMLTAGS I2 TO :OUTXMLTAB2;
 GOTO 500 WHERE :RETVAL <= 0;

:FILE = '../../system/load/example.xml';

EXECUTE XMLPARSE :FILE, :OUTXMLTAB1, 0, :MSG; EXECUTE XMLPARSE :FILE,
:OUTXMLTAB2, 0, :MSG, '-all';
SELECT LINE, TAG, VALUE, ATTR 
FROM INTERFXMLTAGS I1 WHERE LINE <> 0 FORMAT;
SELECT LINE, TAG, VALUE, ATTR
FROM INTERFXMLTAGS I2 WHERE LINE <> 0 FORMAT;
LABEL 500;
UNLINK INTERFXMLTAGS I1;
UNLINK INTERFXMLTAGS I2; 

When the XML file looks like this:

results for the above two EXECUTE commands (without the –all parameter and with it) are as follows:

Parsing JSON

Parsing a JSON file is almost identical, with the small addition of a ‘Y’ to the end of the EXECUTE commands.

Example:

SELECT SQL.TMPFILE INTO :OUTJSONTAB1 FROM DUMMY; 
SELECT SQL.TMPFILE INTO :OUTJSONTAB2 FROM DUMMY;
SELECT SQL.TMPFILE INTO :MSG FROM DUMMY; 
LINK INTERFXMLTAGS I1 TO :OUTJSONTAB1; 
GOTO 500 WHERE :RETVAL <= 0;
LINK INTERFXMLTAGS I2 TO :OUTJSONTAB2; 
GOTO 500 WHERE :RETVAL <= 0;
:FILE = '../../system/load/example.json';

EXECUTE XMLPARSE :FILE, :OUTJSONTAB1, 0, :MSG, '', 'Y';
EXECUTE XMLPARSE :FILE, :OUTJSONTAB2, 0, :MSG, '-all', 'Y';
SELECT LINE, TAG, VALUE, ATTR FROM INTERFXMLTAGS I1 WHERE LINE > 0 FORMAT;
SELECT LINE, TAG, VALUE, ATTR FROM INTERFXMLTAGS I2 WHERE LINE > 0 FORMAT;
LABEL 500;
UNLINK INTERFXMLTAGS I1;
UNLINK INTERFXMLTAGS I2;