Creating SOAP Queries Using SoapUI and the XML/JSON Query Builder

Data Virtuality does not provide a WSDL parser at the moment, so, if you don't want to read the XML by hand, you will need an external tool.

We recommend to use SoapUI.

Let's use the folng example, the weatherendpoint http://www.webservicex.com/globalweather.asmx?WSDL

First of all create a webservice datasource let say soap

open a new project in SoapUI

select a service

test it with test values

copy the xml request

GetCitiesByCountry Request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.webserviceX.NET">
   <soapenv:Header/>
   <soapenv:Body>
      <web:GetCitiesByCountry>
         <!--Optional:-->
         <web:CountryName>Australia</web:CountryName>
      </web:GetCitiesByCountry>
   </soapenv:Body>
</soapenv:Envelope>

remove body and envelope and copy the namespaces to the root element

GetCitiesByCountry Request

<web:GetCitiesByCountry xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.webserviceX.NET">
<web:CountryName>Australia</web:CountryName>
</web:GetCitiesByCountry>

create a file datasource, e.g. xmlfiles

use your webservice datasource and save the returned xml data in a file GetCitiesByCountry.xml in the datasource xmlfiles

call "xmlfiles.saveFile"(
    "filePath" => 'GetCitiesByCountry.xml',
    "file" => ( select * from table( call "soap.invoke"(
    "binding" => 'SOAP12',
    "action" => 'GetCitiesByCountry',
    "request" =>
    '<web:GetCitiesByCountry xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.webserviceX.NET">
         <web:CountryName>Australia</web:CountryName>
      </web:GetCitiesByCountry>',
    "endpoint" => 'http://www.webservicex.com/globalweather.asmx'
) ) "xml" )
);;

open the XML/JSON Query builder and open the xml file

choose the xml you wan to get.

In this case the retuned xml contains a single CDATA element, which contains a encapsulated XML:

soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <GetCitiesByCountryResponse xmlns="http://www.webserviceX.NET">
         <GetCitiesByCountryResult><![CDATA[<NewDataSet>
  <Table>
    <Country>Australia</Country>
    <City>Archerfield Aerodrome</City>
  </Table>
  <Table>
    <Country>Australia</Country>
    <City>Amberley Aerodrome</City>
  </Table>
  <Table>
    <Country>Australia</Country>
  ...

The query will then look like

SELECT XMLPARSE ( DOCUMENT ( "xmlTable.GetCitiesByCountryResult" ) )
FROM
(call "xmlfiles".getFiles('/GetCitiesByCountry.xml')) f,
XMLTABLE(XMLNAMESPACES( DEFAULT 'http://www.webserviceX.NET' ),'/GetCitiesByCountryResponse' PASSING XMLPARSE(DOCUMENT to_chars(f.file,'UTF-8')  WELLFORMED)
    COLUMNS
    "GetCitiesByCountryResult" STRING  PATH 'GetCitiesByCountryResult'
) "xmlTable"

save the response in a additional file GetCitiesByCountryResult.xml

call  "xmlfiles.saveFile"(
    "filePath" => 'GetCitiesByCountryResult.xml',
    "file" =>  ( SELECT XMLPARSE ( DOCUMENT ( "xmlTable.GetCitiesByCountryResult" ) )
FROM
(call "xmlfiles".getFiles('/GetCitiesByCountry.xml')) f,
XMLTABLE(XMLNAMESPACES( DEFAULT 'http://www.webserviceX.NET' ),'/GetCitiesByCountryResponse' PASSING XMLPARSE(DOCUMENT to_chars(f.file,'UTF-8')  WELLFORMED)
    COLUMNS
    "GetCitiesByCountryResult" STRING  PATH 'GetCitiesByCountryResult'
) "xmlTable" )
);;

use again the XML/JSON Query builder to parse the GetCitiesByCountryResult.xml

this will lead to

SELECT 
"xmlTable.Country",
"xmlTable.City"
FROM 
(call "xmlfiles".getFiles('/GetCitiesByCountryResult.xml')) f,
XMLTABLE('/NewDataSet/Table' PASSING XMLPARSE(DOCUMENT to_chars(f.file,'UTF-8')  WELLFORMED)
 COLUMNS 
 "Country" STRING  PATH 'Country',
 "City" STRING  PATH 'City'
) "xmlTable"

and return the wanted result

Finally we can combine everything in

SELECT
        "xmlTable2".*
    FROM
        TABLE ( call "soap.invoke" (
            "binding" => 'SOAP12',
            "action" => 'GetCitiesByCountry',
            "request" => '<web:GetCitiesByCountry xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.webserviceX.NET">
<web:CountryName>Australia</web:CountryName>
</web:GetCitiesByCountry>',
            "endpoint" => 'http://www.webservicex.com/globalweather.asmx' ) ) "soapxml"
            , XMLTABLE ( XMLNAMESPACES ( DEFAULT 'http://www.webserviceX.NET' ), '/GetCitiesByCountryResponse' PASSING "soapxml"."result" COLUMNS "GetCitiesByCountryResult" STRING PATH 'GetCitiesByCountryResult' ) "xmlTable"
            , XMLTABLE ( '/NewDataSet/Table' PASSING XMLPARSE ( DOCUMENT ( "xmlTable.GetCitiesByCountryResult" ) ) COLUMNS "Country" STRING PATH 'Country', "City" STRING PATH 'City' ) "xmlTable2";;

Articles in this section

Creating SOAP Queries Using SoapUI and the XML/JSON Query Builder

Comments

Articles in this section

Related articles