Hi Michael,
A few things first:
1. It seems the declaration is not correct or not allowed. Once I removed it, I could copy the XML Document into an XML column of a SQL Table.
2. The XML includes name spaces which must be defined and considered (XMLNAMESPACES)
3. You have to start at the beginning, i.e. soap:Envelope/soap:Body/AuthenticateResponse/AuthenticateResult
Assumed the XML document does not include a declaration and is located in the table MYXML and the column MYXML (Data Type XML) and the Id column is 1, then the following SELECT-Statement will work.
Select *
from XMLTable(
XMLNamespaces(Default '
http://www.peoplevox.net/',
'
http://schemas.xmlsoap.org/soap/envelope/' as "soap",
'
http://www.w3.org/2001/XMLSchema-instance' as "xsi",
'
http://www.w3.org/2001/XMLSchema' as "xsd"),
'soap:Envelope/soap:Body/AuthenticateResponse/AuthenticateResult'
Passing (Select MyXML from MyXML
Where Id = 1)
Columns "ResponseId" Char(1),
"TotalCount" Char(1),
"Detail" Char(50),
"Statuses" Char(1),
"ImportingQueueId" Char(1),
"SelectOrdersToDespatchId" Char(1),
"ErrorCode" Char(1));
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Michael Fulmer via RPG400-L
Sent: Monday, 18 November 2024 20:32
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: Michael Fulmer <mfulmer@xxxxxxx>
Subject: Parse XML with SQL
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
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
