CODEWITHSUNDEEP
xmlsoapdb2-400
Anson
Anson

Reputation: 37

Db2 for i XMLTABLE with namespaces issue

I'm trying to call a webservice that returns an xml, and then output the result into a table.

Note the example below is just a call to simpler webservice I'm calling, but if I can get this one working, I'm sure I can get the more complicated one working...

SELECT WebServiceResult.*

FROM XMLTABLE(XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",  'http://blah.com' as "bb"),
         '$doc/soap:Envelope'
      PASSING
      XMLPARSE(DOCUMENT systools.HTTPPOSTCLOB('http://zz01:10000/admin.asmx',
                              CAST ('<httpHeader>
                                       <header name ="content-type" value ="application/soap+xml"/>
                                     </httpHeader>' AS char(1024)),
                              CAST('<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" 
                                     xmlns:bb="http://blah.com">
                                    <soap:Header/>
                                    <soap:Body>
                                    <bb:Login>
                                       <bb:applicationNameSpace>something</bb:applicationNameSpace>
                                       <bb:loginId>loginId</bb:loginId>
                                       <bb:password>password</bb:password>
                                   </bb:Login>
                                   </soap:Body>
                                   </soap:Envelope>' AS char(1024)))) AS "doc"
 COLUMNS
 LoginResult varchar(256) PATH '/soap:Body/LoginResponse/LoginResult'
) AS WebServiceResult;

The webservice call is ok and returns something like:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <LoginResponse xmlns="http://blah.com">
      <LoginResult>something.cdd3a6cc-8104-37bd-7514-035b50f5b5c6</LoginResult>
    </LoginResponse>
  </soap:Body>
</soap:Envelope>

I just want to display the LoginResult in a table/column, but I keep getting NULL.

If I comment out

COLUMNS
 LoginResult varchar(256) PATH '/soap:Body/LoginResponse/LoginResult'

then I get the xml I pasted above, so I figure its just something wrong with my PATH, but I've tried a lot of different things and can't get it working.

Any help would be much appreciated.

Upvotes: 0

Views: 552

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

Based on your two namespace declarations:

XMLTABLE(XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",  'http://blah.com' as "bb")

And XML

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <LoginResponse xmlns="http://blah.com">
      <LoginResult>something.cdd3a6cc-8104-37bd-7514-035b50f5b5c6</LoginResult>
    </LoginResponse>
  </soap:Body>
</soap:Envelope>

Please try the following XPath expression:

/soap:Envelope/soap:Body/bb:LoginResponse/bb:LoginResult

Or by using a wildcard namespace

/soap:Envelope/soap:Body/*:LoginResponse/*:LoginResult

Upvotes: 1

Related Questions