Parse XML with SQL -- RPG400-L

It has been years since I’ve been ask to parse a XML File on the IBM I.
I believe the last time I used the EXPAT Parser which was the best we had
at the time.

Now I would like to use SQL. For my current Project, I used cURL and
Richard Schoen’s QSHONI to communicate with PeopleVox
and receive the following Authorization Response XML from their test
Server:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/";
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
    xmlns:xsd="http://www.w3.org/2001/XMLSchema";>
    <soap:Body>
        <AuthenticateResponse
            xmlns="http://www.peoplevox.net/";>
            <AuthenticateResult>
                <ResponseId>0</ResponseId>
                <TotalCount>1</TotalCount>

<Detail>demo1,198b51d0-b3b4-42a9-872d-335f32c2c1f6</Detail>
                <Statuses/>
                <ImportingQueueId>0</ImportingQueueId>
                <SalesOrdersToDespatchIds/>
                <ErrorCode/>
            </AuthenticateResult>
        </AuthenticateResponse>
    </soap:Body>
</soap:Envelope>

Now I need to parse and store the ResponseId, the Total Count, Detail
information
and Error Code if it exists from the XML File.

I’ve created the following program and have confirmed the XML Response is
being written to the pvox.TESTFILE
on the IBM i using the Run SQL Script in ACS.
I never get any data retrieved from the pvox.TESTFILE using the SQL Cursor
into the Qualified Data Structure.

Any guidance would be appreciated.

**free
       ctl-opt option(*nodebugio:*srcstmt:*nounref) ;

       dcl-ds Data qualified dim(10) ;
         ResponseId char(1) ;
         TotalCount char(1) ;
         Detail     char(50) ;
         Statuses   char(1);
         ImportingQueueId char(1);
         SelectOrdersToDespatchId char(1);
         ErrorCode char(1);
       end-ds ;

      dcl-s Rows uns(5) inz(%elem(Data)) ;
      dcl-s InFile sqltype(clob_file) ;
      dcl-s Path varchar(100) inz('/pvox/auth/AUTH.xml') ;

      exec sql SET OPTION COMMIT = *NONE,
                        CLOSQLCSR = *ENDMOD,
                   DATFMT = *ISO ;
clear InFile ;
Infile_Name = %trimr(Path) ;
Infile_NL = %len(%trimr(Infile_Name)) ;
Infile_FO = SQFRD ;

exec sql DROP TABLE pvox/TESTFILE ;
exec sql CREATE TABLE pvox/TESTFILE (COL1 XML) ;
exec sql INSERT INTO pvox/TESTFILE VALUES(:InFile) ;
clear Data ;

exec sql DECLARE C0 CURSOR FOR
            SELECT A.* FROM pvox/TESTFILE B,
            XMLTABLE('$doc/AuthenticateResponse/AuthenticateResult'
              PASSING A.COL1 AS "doc"
              COLUMNS
              ResponseId CHAR(1) PATH 'ResponseId',
              TotalCount CHAR(1) PATH 'TotalCount',
              Detail     CHAR(50) PATH 'Detail',
              Statuses   char(1) path 'Statuses',
             ImportingQueueId char(1) path
'ImportingQueueId',
             SelectOrdersToDespatchId char(1) path
'SelectOrdersToDespatchId',
             ErrorCode char(1) path
'ErrorCode'
             ) AS A
;


exec sql OPEN C0
;
exec sql FETCH C0 FOR :Rows ROWS INTO :Data
;
exec sql GET DIAGNOSTICS :Rows = ROW_COUNT
;
exec sql CLOSE C0
;


Return;

Thanks,
Michael Fulmer