SOAPHTTPNV on Db2 9.1 zos

Question

I'm calling a webservice from Db2 9.1 on zOS using function SOAPHTTPNV. The result comes back ok, but the webservice is called multiple times, once for every row in the resultset (or occurrence of element ROW in the response). Why is that ?

The SQL

SELECT T.NR_KAT, T.PNR_F
FROM
XMLTABLE(
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS
"soap",
'http://schemas/SERVICE/100921' AS "p"),
'$d/soap:Envelope/soap:Body/p:SERVICE_RESPONSE/p:document/p:result/p:ROW'
PASSING XMLPARSE(
DOCUMENT DB2XML.SOAPHTTPNV(
'http://serviceurl',
VARCHAR(''),
VARCHAR('<soap:Envelope
    request_simplified
</soap:Envelope>'
))) AS "d"
COLUMNS
NR_KAT VARCHAR(2) PATH 'p:NR_KAT',
PNR_F  VARCHAR(12) PATH 'p:PNR_F'
) AS T
;

The result of the SQL in SPUFI

NR_KAT  PNR_F
---------+---------+---------+---------+------
09      194513051834
08      194515042978
19      194515300398

The xml response from webservice

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
        <Body>
    <SERVICE_RESPONSE xmlns="http://schemas/SERVICE/100921">
         <document>
             <result>
                 <ROW>
                    <NR_KAT>09</NR_KAT>
                    <PNR_F>194513051834</PNR_F>
                </ROW>
                 <ROW>
                    <NR_KAT>08</NR_KAT>
                    <PNR_F>194515042978</PNR_F>
                </ROW>
                 <ROW>
                    <NR_KAT>19</NR_KAT>
                        <PNR_F>194515300398</PNR_F>
                    </ROW>    
                    </result>
        </document>
    </SERVICE_RESPONSE>
    </Body>
    </Envelope>

score 0 · Answer 1 · answered Jan 08 '13 at 14:47

0

Try using the BY REF clause of the XMLTABLE function:

SELECT T.NR_KAT, T.PNR_F
FROM XMLTABLE(xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
                             'http://schemas/SERVICE/100921'             AS "p"),
              '$d/soap:Envelope/soap:Body/p:SERVICE_RESPONSE/p:document/p:result/p:ROW'
              PASSING BY REF XMLPARSE(...))) AS D
              COLUMNS NR_KAT VARCHAR(2)  PATH 'p:NR_KAT',
                      PNR_F  VARCHAR(12) PATH 'p:PNR_F') AS T
;

answered Jan 08 '13 at 14:47

Ulrich Seelbach

31
1

Thanks, but same result with BY REF. Actually I noticed now that the service is called once + 1 for every row returned by the service. I also tried getting the result from the service as one row, on httpEnvelope-level, I then see that the service is called twice. – user1943261 Jan 09 '13 at 13:50

score 0 · Answer 2 · answered Jan 12 '13 at 00:22

The symptom matches with the one described in APAR PM52237. APAR PM52237 fixed the problem that the arguments of the XMLTABLE function were called for each row returned by the XMLTABLE. Please make sure the PTF UK77921(for DB2 9) has been installed on your z/OS system.

SOAPHTTPNV on Db2 9.1 zos

2 Answers2