Query in XMLtable used in DB2

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
gyt3
Member
Posts: 7
Joined: Sun Dec 16, 2012 9:46 am

Query in XMLtable used in DB2

Post by gyt3 » Thu Feb 27, 2020 10:58 am

Hi All,

My requirement is to read the xml file and fetch the values stored in XML tag. For which I had used XMLTABLE.
I am getting SQL code - 100 during fetch, I tried several things, but, everything returns only SQL - 100 during Fetch.
Can someone please look at this and correct the code and help me on this ?

Input XML in the form of dataset :

<?xml version="1.0" encoding="utf-8"?>
<Report Name="PaymentEventDetailReport_Daily_Classic" Type="ProcessorEventsDetail"
Version="1.11"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="https://api.source.com/reporting/v3/xsd ... tailReport"
OrganizationID="wfghps01"
ReportStartDate="2020-02-06T08:00:00Z"
ReportEndDate="2020-02-07T08:00:00Z">
<Requests>
<Request RequestID="11111181951606480504281" MerchantID="wfghps01" MerchantReferenceNumber="0AA098100002C923K87EFT214195580" RequestDate="2019-12-31T07:43:15Z">

Cursor declaration in my DB2 pgm:


DECLARE PYMTEVENTS-CSR CURSOR WITH HOLD FOR
SELECT A.REQUEST_ID
,A.MERCHANT_ID
,A.MERCH_REF_NUM
,A.TRANS_DATETIME
,A.PAYMENT_TYPE_DESC
,A.TRANS_REF_NUM
,A.TRANSACTION_ID
,A.EVENT
,A.EVENT_DT
,A.FEE_CURR_CD
,A.FEE_AMT
,A.CONSUMER_CURR_CD
,A.CONSUMER_AMT
,A.PROCESSOR_MSG
,A.DEBIT_INDICATOR
FROM
XMLTABLE(XMLNAMESPACES(DEFAULT
'http://www.w3.org/2001/XMLSchema-instance'),
'$I/Report/Requests/Request'
PASSING :DOC-AS-CLOB-FILE AS "I"
COLUMNS
REQUEST_ID CHAR(26) PATH '../@RequestID'
,MERCHANT_ID CHAR(30) PATH '../@MerchantID'
,MERCH_REF_NUM CHAR(50) PATH
'../@MerchantReferenceNumber'
,TRANS_DATETIME CHAR(25) PATH '../@RequestDate'
,PAYMENT_TYPE_DESC CHAR(50) PATH 'TypeDescription'

Regards,
Gyt3

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others