Read XML files using RPG2SQL Integrator
From Support
| If you have a question or seek clarification, please call Technical Support. |
[edit]
Problem:
How can XML files be read without having to use some kind of XML parser?
[edit]
Solution:
It is possible to read an XML file, given a suitable OLEDB provider. I found a free one on the Internet at http://www.viksoe.dk/code/xmloledb.htm - this site is essentially the documentation for the product. There is also a download link for the Microsoft item needed to make it work.
I have used this with a few different XML files. It works better with some than with others, and there is likely to be some experimentation needed to get the results you want. The sample source below can take any XML file you want - I put the example from the above site into C:\XML\test.xml - here it is:
<AddressBook>
<Person familyName="Gates" firstName="Bob">
<Address street="Pine Rd." number="1239" state="CA"/>
<JobInfo jobDescription="Manager"/>
<Description>Family man</Description>
</Person>
<Person familyName="Brown" firstName="Robert">
<Address street="118 St." number="344" state="NY"/>
<JobInfo jobDescription="Group Leader"/>
<Description>Hacker</Description>
</Person>
<Person familyName="DePaiva" firstName="Kassie">
<Address street="Pine Rd." number="1234" state="CA"/>
<JobInfo jobDescription="Actor"/>
<Description>Actor</Description>
</Person>
</AddressBook>
The example does not display or process anything, it just reads the "rows" - you can see what the data is by looking at the debug window of the RPG2SQL Integrator PC component. Also, the example retrieves a delimited list of field types and fields names - the field types are all = 200. There are constants in copy member RPGSQLH that help identify these - 200 = adVarChar, e.g.
Example 1: Once you have created the objects (see the source below), here is a typical command string:
SOMELIB/XMLTEST FILE('C:\\XML\\TEST.XML') TABLE('Person')
(Note the double backslashes.)
The result of that is (from the debug window output)
SQL Record Select: SELECT * FROM Person Delimited Field Type: 200 ,200 ,200 ,200 ,200 ,200 , Received Delimited Field: rowid ,familyName ,firstName ,Address ,JobInfo ,Description Move to First Delim Record: IDAEA03 ~Gates ~Bob ~ ~ ~Family man ~ ~ Move to Next Delim Record: IDAOA03 ~Brown ~Robert ~ ~ ~Hacker ~ ~ Move to Next Delim Record: IDAYA03 ~DePaiva ~Kassie ~ ~ ~Actor ~ ~ Move to Next Delim Record: 999 ERR -1 End of file reached. ~
Example 2: Note that the Address and JobInfo "fields" are not returned - they are actually considered children of Person and are handled as a separate table in the XML file. But you can get their data by using what looks like a JOIN, so that in the XMLTEST command, above, the TABLE parameter is
SOMELIB/XMLTEST FILE('C:\\XML\\TEST.XML') TABLE('Person,Address')
SQL Record Select: SELECT * FROM Person,Address Delimited Field Type: 200 ,200 ,200 ,200 ,200 ,200 ,200 ,200 ,200 ,200 , Delimited Field: rowid ,familyName ,firstName ,Address ,JobInfo ,Description ,rowid ,street ,number ,state Move to First Delim Record: IDAEA03 ~Gates ~Bob ~ ~ ~Family man ~IDAHA03 ~Pine Rd. ~1239 ~CA ~ ~ Move to Next Delim Record: IDAOA03 ~Brown ~Robert ~ ~ ~Hacker ~IDARA03 ~118 St. ~344 ~NY ~ ~ Move to Next Delim Record: IDAYA03 ~DePaiva ~Kassie ~ ~ ~Actor ~IDA1A03 ~Pine Rd. ~1234 ~CA ~ ~ Move to Next Delim Record: 999 ERR -1 End of file reached. ~
Sample source Here is the sample source for a front-end command, a CLLE command-processing program, and the RPGLE program that does all the work. There are also included the commands to create the objects. Give me a call to discuss this further. I will admit that I am new enough to the XML things that I won't know everything, but I think I can help some.
XMLTEST command
/* CRTCMD CMD(SOMELIB/XMLTEST) PGM(XMLTESTC) SRCFILE(SOMELIB/QCMDSRC) PRDLIB(SOMELIB) */
CMD PROMPT('Read Data from TEST.XML')
PARM KWD(HOST) TYPE(*CHAR) LEN(255) DFT(*CURRENT) +
EXPR(*YES) CASE(*MIXED) PROMPT('RPG2SQL +
Server IP Address')
PARM KWD(FILE) TYPE(*PNAME) LEN(255) MIN(1) +
EXPR(*YES) PROMPT('XML file')
PARM KWD(TABLE) TYPE(*CHAR) LEN(255) MIN(1) +
EXPR(*YES) CASE(*MIXED) PROMPT('XML table')
XMLTESTC command processing program
/* CRTBNDCL PGM(SOMELIB/XMLTESTC) SRCFILE(SOMELIB/QCLLESRC) DBGVIEW(*ALL) */
PGM PARM(&IPADDR &FILE &TABLE)
DCL VAR(&IPADDR) TYPE(*CHAR) LEN(255)
DCL VAR(&FILE) TYPE(*CHAR) LEN(255)
DCL VAR(&TABLE) TYPE(*CHAR) LEN(255)
/********************************************************/
/* ERROR HANDLING VARIABLES */
/********************************************************/
DCL VAR(&ERRORSW) TYPE(*LGL)
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(100)
DCL VAR(&MSGF) TYPE(*CHAR) LEN(10)
DCL VAR(&MSGFLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&TXT1ST) TYPE(*CHAR) LEN(100)
DCL VAR(&TXT2ND) TYPE(*CHAR) LEN(100)
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(STDERR1))
/***************************************************************/
/* Get current PC IP address if passed in. */
/***************************************************************/
IF COND(&IPADDR *EQ *CURRENT) THEN(DO)
RSQIP IPADDR(&IPADDR)
ENDDO
/***************************************************************/
/* Call program to read XML table */
/***************************************************************/
CALL PGM(XMLTESTR) PARM(&IPADDR &FILE &TABLE)
/*******************************************************************/
/* HANDLE ERRORS */
/*******************************************************************/
STDERR1: /* Standard error handling routine */
IF &ERRORSW SNDPGMMSG MSGID(CPF9999) +
MSGF(QCPFMSG) MSGTYPE(*ESCAPE) /* Func chk */
CHGVAR &ERRORSW '1' /* Set to fail ir error occurs */
STDERR2: RCVMSG MSGTYPE(*DIAG) MSGDTA(&MSGDTA) MSGID(&MSGID) +
MSGF(&MSGF) MSGFLIB(&MSGFLIB)
IF (&MSGID *EQ ' ') GOTO STDERR3
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) MSGTYPE(*DIAG)
GOTO STDERR2 /* Loop back for addl diagnostics */
STDERR3: RCVMSG MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) +
MSGF(&MSGF) MSGFLIB(&MSGFLIB)
IF (&MSGID *EQ ' ') GOTO STDERR4
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) MSGTYPE(*ESCAPE)
GOTO STDERR3 /* Loop back for addl exceptions */
STDERR4: RCVMSG MSGTYPE(*INFO) MSG(&TXT1ST) SECLVL(&TXT2ND) +
MSGDTA(&MSGDTA) MSGID(&MSGID) MSGF(&MSGF) +
MSGFLIB(&MSGFLIB)
IF (&TXT1ST *EQ ' ') GOTO STDERR5
SNDPGMMSG MSG(&TXT1ST) MSGTYPE(*INFO)
GOTO STDERR4 /* Loop back for addl info msgs */
STDERR5: RCVMSG MSGTYPE(*COMP) MSG(&TXT1ST) SECLVL(&TXT2ND) +
MSGDTA(&MSGDTA) MSGID(&MSGID) MSGF(&MSGF) +
MSGFLIB(&MSGFLIB)
IF (&MSGID *EQ ' ') RETURN
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) MSGTYPE(*COMP)
GOTO STDERR5 /* Loop back for addl comp msgs */
ENDPGM
==============================================================
XMLTESTR main program
==============================================================
/* ADDLIBLE RJSRPGSQL */
/* CRTBNDRPG PGM(VERN/XMLTESTR) SRCFILE(VERN/QRPGLESRC) DBGVIEW(*ALL) */
H BNDDIR('RJSRPGSQL':'QC2LE') DFTACTGRP(*NO) ACTGRP(*NEW)
*********************************************************************
* Program Name: XMLTESTR
*
* Purpose:
* This sample program simple opens the XML file whose name
* is passed into this program. It reads all the records in the
* "table name" passed in. No additional processing is done
* with the data for this simple example.
*
* Processing Steps:
* 1) Connects to RPG2SQL server using specified IP address.
* 2) Opens XML file via ADO.
* 3) Runs SQL record select from the specified table.
* 4) Reads all records from the specified table.
* NOTE: Look at the results in the debug window in
* the RPG2SQL server.
* 5) Closes ADO database connection.
* 6) Closes RPG2SQL server connection.
*
* DATE: 12/15/2005
* AUTHOR: Vernon M. Hamberg
*
*********************************************************************
*-----------------------------------------------------------------------------
* RPG2SQL Integrator API Includes
*-----------------------------------------------------------------------------
/COPY SOURCE,RPGSQLH
*-----------------------------------------------------------------------------
* Main Program Processing
*-----------------------------------------------------------------------------
D DotIPAddr S 15A
C******************************************************************
C* EXTERNAL PARAMETERS:
C *ENTRY PList
C Parm IPADDR 255
C Parm FILE 255
C Parm TABLENAME 255
*-----------------------------------------------------------------------------
* ** Get dotted IP address from fully-qualified domain
* ** name - not needed if using dotted IP address
*-----------------------------------------------------------------------------
C Eval Rtn = TCP_GetIPFromHost(IPAddr : DotIPAddr)
* ** Exit with Error Return - TCP Address Conversion
C If Rtn <> 0
C Eval *INLR = *On
C Return
C EndIf
*-----------------------------------------------------------------------------
* ** Connect to RPG2SQL Server
*-----------------------------------------------------------------------------
C CallP SQL_TCPMultBuff(1)
C Eval SQL_Socket = SQL_Connect(%trim(DotIPAddr))
C* ** Exit with Error Return - TCP Server Connect
C If SQL_Socket = -999
C Eval Rtn = -1
C Eval *INLR = *On
C Return
C EndIf
*-----------------------------------------------------------------------------
* ** Open ADO SQL Database Connection
* ** for XML file
*-----------------------------------------------------------------------------
C Eval Rtn = SQL_DBOpenConn(SQL_Socket:
C 'Provider=XmlOleDb.XML; ' +
C 'Location=' + %trimr(file) + ';')
*-----------------------------------------------------------------------------
* ** Run SQL Query to Open ADO Recordset
*-----------------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLSel(SQL_Socket:
C 'SELECT * FROM ' + %trim(tableName))
*-----------------------------------------------------------------------------
* ** Run function to get data types
*-----------------------------------------------------------------------------
C Eval RtnData4096 = SQL_GetDelimFlt(SQL_Socket:
C ',':'0')
*-----------------------------------------------------------------------------
* ** Run function to get column names
*-----------------------------------------------------------------------------
C Eval RtnData4096 = SQL_GetDelimFld(SQL_Socket:
C ',':'0')
*-----------------------------------------------------------------------------
* ** Go to first record in recordset
*-----------------------------------------------------------------------------
C Eval RtnData4096 = SQL_MoveFirsBuf(SQL_Socket)
*-----------------------------------------------------------------------------
* ** Loop to read all records from recordset
*-----------------------------------------------------------------------------
C DoW RtnData4096 <> '*ERROR*'
*-----------------------------------------------------------------------------
C* ** Go to Next record in recordset
*-----------------------------------------------------------------------------
C Eval RtnData4096 = SQL_MoveNextBuf(SQL_Socket)
C EndDo
*-----------------------------------------------------------------------------
* ** Close ADO Database Connection. We're done.
*-----------------------------------------------------------------------------
C CallP SQL_DBCloseConn(SQL_Socket)
*-----------------------------------------------------------------------------
* ** Disconnect from RPG2SQL server
*-----------------------------------------------------------------------------
C CallP SQL_Disconnect(SQL_Socket)
C Eval *inlr = *on
==============================================================
Categories: RPG2SQL Integrator | RPG2SQL | XML | TBPR | Knowledge Base
