Topic
4 replies Latest Post - ‏2013-01-09T04:29:03Z by SystemAdmin
SystemAdmin
SystemAdmin
3105 Posts
ACCEPTED ANSWER

Pinned topic SOAPHTTPNV on Db2 9.1 zos

‏2013-01-02T16:00:42Z |
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 resulset (or occurrence of element ROW in the response).
Why is that ?

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
<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>
Updated on 2013-01-09T04:29:03Z at 2013-01-09T04:29:03Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3105 Posts
    ACCEPTED ANSWER

    Re: SOAPHTTPNV on Db2 9.1 zos

    ‏2013-01-05T02:01:25Z  in response to SystemAdmin
    since this is a sql function, that is the way it is implemented
    if you want it to only be called once, you will need to have all your data in a single row, and that row will be passed to the service.
    you have several ways to approach this:
    1) write a stored procedure that creates that row
    2) write a user defined table function that return a single row table
    3) use recursive sql to generate that row
  • SystemAdmin
    SystemAdmin
    3105 Posts
    ACCEPTED ANSWER

    Re: SOAPHTTPNV on Db2 9.1 zos

    ‏2013-01-05T02:01:26Z  in response to SystemAdmin
    since this is a sql function, that is the way it is implemented
    if you want it to only be called once, you will need to have all your data in a single row, and that row will be passed to the service.
    you have several ways to approach this:
    1) write a stored procedure that creates that row
    2) write a user defined table function that return a single row table
    3) use recursive sql to generate that row
    • SystemAdmin
      SystemAdmin
      3105 Posts
      ACCEPTED ANSWER

      Re: SOAPHTTPNV on Db2 9.1 zos

      ‏2013-01-07T09:38:48Z  in response to SystemAdmin
      Thanks, but when getting the result from the service as one row, on http-envelope level, strangely enough, the service is called twice. Is there a rational explanation for that ?

      SELECT T.RESULT
      FROM
      XMLTABLE(
      xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS
      "soap",
      'http://schemas/SERVICE/100921' AS "p"),
      '$d/soap:Envelope'
      PASSING XMLPARSE(
      DOCUMENT DB2XML.SOAPHTTPNV(
      'http://serviceurl',
      VARCHAR(''),
      VARCHAR('<soap:Envelope
      request_simplified
      </soap:Envelope>'
      ))) AS "d"
      COLUMNS
      RESULT XML PATH '.'
      ) AS T
      ;

      RESULT
      <?xml version="1.0" encoding="IBM278"?><Envelope xmlns="http://schemas.xmlsoap.o......
      • SystemAdmin
        SystemAdmin
        3105 Posts
        ACCEPTED ANSWER

        Re: SOAPHTTPNV on Db2 9.1 zos

        ‏2013-01-09T04:29:03Z  in response to SystemAdmin
        i have no idea why the service will be called twice for a single row
        maybe you should contact IBM support