XMLNAMESPACES query

Ron Mascarenhas

XMLNAMESPACES query

Hi,

I am testing a Web Service through SPUFI using the DB2 UDF SOAPHTTPNV. When I test the service using just a SELECT DB2XML.SOAPHTTPNV it returns 1 row (XML SOAP result).

I now added the SELECT XMLPARSE, but it does not return the element I require, and 0 ROWS are returned. I suspect my XMLNAMESPACE declaration is not correct.

Here is the code:

 

WITH
CMDQRY (CMDRES) AS
(SELECT DB2XML.SOAPHTTPNV(
'http://my.url:45454/db2t/Oppwiban',
'',
'<soapenv:Envelope '
'xmlns:q0="http://www.OPPWIBAN.Oppwiban.Request.com" '
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" '
'xmlns:xsd="http://www.w3.org/2001/XMLSchema" '
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
'<soapenv:Body>'
'<q0:OPPWIBANOperation> '
'<q0:comm_area> '
'<q0:comm_area_security> '
'<q0:comm_dept/> '
'<q0:comm_clerk/> '
'<q0:comm_user_type/> '
'<q0:comm_ee_no/> '
'</q0:comm_area_security> '
' '
' 1234 '
'q0:comm_iban_number> '
'</q0:comm_area_received> '
' '
'<q0:comm_error_code/> '
'<q0:comm_msgno/> '
'<q0:comm_msgds/> '
'</q0:comm_area_sent_back> '
'q0:comm_area> '
'</q0:OPPWIBANOperation> '
'soapenv:Body>soapenv:Envelope> ')
FROM SYSIBM.SYSDUMMY1
)
SELECT CMDTAB.* FROM CMDQRY,
XMLTABLE(XMLNAMESPACES('http://www.OPPWIBAN.Oppwiban.Request.com'
as "pfx"),
'$d/pfx:OPPWIBANOperation/pfx:comm_area'
passing xmlparse (document cmdres) as
"d"
COLUMNS
"IBAN" CHAR(10) PATH
'pfx:comm_area_sent_back/pfx:comm_msgds'
) AS CMDTAB
Thanks
Edited By:
Ron Mascarenhas[Organization Members] @ Nov 09, 2017 - 09:08 PM (Asia/Kuwait)

Michael Hannan

RE: XMLNAMESPACES query
(in response to Ron Mascarenhas)

Ron,

I am not sure I fully understand, but why don't you put you XMLTABLE Function in the SELECT clause instead of in the FROM clause. In the FROM clause it looks like a join with no join predicates, but perhaps correlation attempted on cmdres in a situation where that is not allowed.

In Reply to Ron Mascarenhas:

Hi,

I am testing a Web Service through SPUFI using the DB2 UDF SOAPHTTPNV. When I test the service using just a SELECT DB2XML.SOAPHTTPNV it returns 1 row (XML SOAP result).

I now added the SELECT XMLPARSE, but it does not return the element I require, and 0 ROWS are returned. I suspect my XMLNAMESPACE declaration is not correct.

Here is the code:

 

WITH
CMDQRY (CMDRES) AS
(SELECT DB2XML.SOAPHTTPNV(
'http://my.url:45454/db2t/Oppwiban',
'',
'<soapenv:Envelope '
'xmlns:q0="http://www.OPPWIBAN.Oppwiban.Request.com" '
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" '
'xmlns:xsd="http://www.w3.org/2001/XMLSchema" '
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
'<soapenv:Body>'
'<q0:OPPWIBANOperation> '
'<q0:comm_area> '
'<q0:comm_area_security> '
'<q0:comm_dept/> '
'<q0:comm_clerk/> '
'<q0:comm_user_type/> '
'<q0:comm_ee_no/> '
'</q0:comm_area_security> '
' '
' 1234 '
'q0:comm_iban_number> '
'</q0:comm_area_received> '
' '
'<q0:comm_error_code/> '
'<q0:comm_msgno/> '
'<q0:comm_msgds/> '
'</q0:comm_area_sent_back> '
'q0:comm_area> '
'</q0:OPPWIBANOperation> '
'soapenv:Body>soapenv:Envelope> ')
FROM SYSIBM.SYSDUMMY1
)
SELECT CMDTAB.* FROM CMDQRY,
XMLTABLE(XMLNAMESPACES('http://www.OPPWIBAN.Oppwiban.Request.com'
as "pfx"),
'$d/pfx:OPPWIBANOperation/pfx:comm_area'
passing xmlparse (document cmdres) as
"d"
COLUMNS
"IBAN" CHAR(10) PATH
'pfx:comm_area_sent_back/pfx:comm_msgds'
) AS CMDTAB
Thanks



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Ron Mascarenhas

RE: XMLNAMESPACES query
(in response to Michael Hannan)



In Reply to Michael Hannan:

Thanks Michael for the tip.

As I am new to the UDFs, I was following an example in the Redbook "Deploying SOA Solutions. My  example is similar to that in the book, except that I decided to add the XMLNAMESPACES.  i will try to restructure my query, but is my XMLNAMESPACES declaration correct, the way I have specified it for the q0 namespace? 

Ron,

I am not sure I fully understand, but why don't you put you XMLTABLE Function in the SELECT clause instead of in the FROM clause. In the FROM clause it looks like a join with no join predicates, but perhaps correlation attempted on cmdres in a situation where that is not allowed.

In Reply to Ron Mascarenhas:

Hi,

I am testing a Web Service through SPUFI using the DB2 UDF SOAPHTTPNV. When I test the service using just a SELECT DB2XML.SOAPHTTPNV it returns 1 row (XML SOAP result).

I now added the SELECT XMLPARSE, but it does not return the element I require, and 0 ROWS are returned. I suspect my XMLNAMESPACE declaration is not correct.

Here is the code:

 

WITH
CMDQRY (CMDRES) AS
(SELECT DB2XML.SOAPHTTPNV(
'http://my.url:45454/db2t/Oppwiban',
'',
'<soapenv:Envelope '
'xmlns:q0="http://www.OPPWIBAN.Oppwiban.Request.com" '
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" '
'xmlns:xsd="http://www.w3.org/2001/XMLSchema" '
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
'<soapenv:Body>'
'<q0:OPPWIBANOperation> '
'<q0:comm_area> '
'<q0:comm_area_security> '
'<q0:comm_dept/> '
'<q0:comm_clerk/> '
'<q0:comm_user_type/> '
'<q0:comm_ee_no/> '
'</q0:comm_area_security> '
' '
' 1234 '
'q0:comm_iban_number> '
'</q0:comm_area_received> '
' '
'<q0:comm_error_code/> '
'<q0:comm_msgno/> '
'<q0:comm_msgds/> '
'</q0:comm_area_sent_back> '
'q0:comm_area> '
'</q0:OPPWIBANOperation> '
'soapenv:Body>soapenv:Envelope> ')
FROM SYSIBM.SYSDUMMY1
)
SELECT CMDTAB.* FROM CMDQRY,
XMLTABLE(XMLNAMESPACES('http://www.OPPWIBAN.Oppwiban.Request.com'
as "pfx"),
'$d/pfx:OPPWIBANOperation/pfx:comm_area'
passing xmlparse (document cmdres) as
"d"
COLUMNS
"IBAN" CHAR(10) PATH
'pfx:comm_area_sent_back/pfx:comm_msgds'
) AS CMDTAB
Thanks


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Michael Hannan

RE: XMLNAMESPACES query
(in response to Ron Mascarenhas)



In Reply to Ron Mascarenhas:
 

Thanks Michael for the tip.

As I am new to the UDFs, I was following an example in the Redbook "Deploying SOA Solutions. My  example is similar to that in the book, except that I decided to add the XMLNAMESPACES.  i will try to restructure my query, but is my XMLNAMESPACES declaration correct, the way I have specified it for the q0 namespace? 

Ron,

I am not going to try to check the syntax of every part of your query and I am not that expert about these SOAP functions. However the structure of your query looks wrong, from a basic SQL point of view. See Figure 8-36 in the Deploying SOA Solutions Redbook. It used a Common Table Expression, but has no join. If refers to the CTE result in a later SELECT. You can do that too. However the referring part cannot be a Join in the form clause in the way you coded it. Normally joined tables do not contain correlated references to each other, exception being using the TABLE keyword in the FROM clause. So when referring to the results of your CTE, you don't seem to need to make a join. I am guessing the CTE has 1 row, but you join to something else containing zero rows. So don't join! You did not really want a join at all, I think. Your FROM clause has two things separated by a comma. That implies a join ("Cross Join") even though there are no join predicates.

I am not in a position to test it myself right at this moment.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd