How to get data from a SOAP WS with Power BI

[Manal]

Hello,

I have an application with a SOAP wb and I want to create Dashboards using Power BI Desktop.

How do I get data using the SOAP wb ? 

I know that Power BI has a REST API and it says that it helps you push data into power bi resource but in my case I have an external resource I have to connect to.

Thank you for your help.

Best regards.

Manal

[juliovidigal]

Actually, the next step of my project was this, because I'm going to publish this and I do not find it interesting to keep calling files to make the call.
So now I've put the XML inside PowerQuery.

Because I'm going to use the token return on other calls, it's unfeasible that it stays on file.

It looks like this:

let

    Source = Xml.Tables(Web.Contents("https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL"

    ,[Content=Text.ToBinary("<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https://advertising.criteo.com/API/v201305"&Character.FromNumber(34)&" xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&">

   <soapenv:Body>

      <clientLogin>

         <username>aaaaa@aaaa.com</username>

         <password>aaaaaaaa</password>

      </clientLogin>

   </soapenv:Body>

</soapenv:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))

in

    Source

[hugoberry]

Might want to consider using single quotes in your xml. Or if you need those double quoutes try using #(0022) in your query it is equivalent to Character.FromNumber(34), plus you don't need to concatenate them.

instead of

"<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https"

try

"<soapenv:Envelope xmlns=#(0022)https"

Plus, have you considered using Text.Format to keep password and username out of the XML qurey builder?

[juliovidigal]

Good evening. I used your tips, and they really worked, the formatting was simpler. Thanks for the days. Regarding using the text.format, I really did not understand how to apply this. The idea is to receive this as a parameter? So you do not get stuck in the code? Thank you

[ZappySys]

Hi check below blog post. It explains concepts of calling any Web service in Power BI. Basically, you need to craft SOAP Body for POST request and need to supply credentials / http headers (e.g. Content-Type, SOAPAction ) 

https://zappysys.com/blog/call-soap-api-power-bi-read-xml-web-service-data/

Configure ZappySys XML ODBC DSNPreview SOAP API query using ZappySys XML DriverImport SOAP API data in Power BI using ODBC connection

Sample SQL to import SOAP Web Service Data in Power BI

SELECT * FROM $
WITH(
	 src='http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx'
	,DataConnectionType='HTTP'
	,CredentialType='Basic' --OR SoapWss
	,SoapWssPasswordType='PasswordText'
	,UserName='myuser'
	,Password='pass$$w123'
	,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'
	,ElementsToTreatAsArray='HolidayCode'	
	,RequestMethod='POST'	
	,Header='Content-Type: text/xml;charset=UTF-8 || SOAPAction: "http://www.holidaywebservice.com/HolidayService_v2/GetHolidaysAvailable"'
	,RequestData='
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:hol="http://www.holidaywebservice.com/HolidayService_v2/">
   <soapenv:Header/>
   <soapenv:Body>
      <hol:GetHolidaysAvailable>
         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->
         <hol:countryCode>UnitedStates</hol:countryCode>
      </hol:GetHolidaysAvailable>
   </soapenv:Body>
</soapenv:Envelope>'
)

[hugoberry]

Text.Format would help you to store username password in some parameter and pass it to the XML builder routine. Something like this:

Text.Format("longxml

<username>#[username]</username>
<password>#[password]</password>
more xml",[username = parameter_username, password= parameter_password])

[juliovidigal]

Thank you for the tip.
I will certainly use it in other scenarios. In this scenario, I only use username and password at that moment, then I use the Token that I get from this authentication, and that email and password will not change.

Thank you

[Pubudu]

Can you please share your next SOAP request with dummy key. Just to check how should I construct. I was trying as bellow.

let
Source = Xml.Tables(Web.Contents("https://api1.xxx.com/services/MainService?wsdl"
,[Content=Text.ToBinary("<x:Envelope xmlns:x="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:ser="&Character.FromNumber(34)&"http://services"&Character.FromNumber(34)&">
<x:Header/>

<x:Body>
<ser:getHistory>
<ser:sessionId>xxxxxxxxxx</ser:sessionId>
<ser:entityTypeId>4</ser:entityTypeId>
<ser:startYYYYMMDD>2016/08/04 05:49:05</ser:startYYYYMMDD>
<ser:endYYYYMMDD>2016/08/17 07:33:46</ser:endYYYYMMDD>
<ser:showRepeatingPerDay>false</ser:showRepeatingPerDay>
<ser:simpleDateFormat>yyyy/MM/dd HH:mm:ss</ser:simpleDateFormat>
</ser:getHistory>
</x:Body>
</x:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

But this start giving following error.
DataSource.Error: Web.Contents failed to get contents from 'https://api1.innotas.com/services/MainService?wsdl' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://api1.innotas.com/services/MainService
Url=https://api1.innotas.com/services/MainService?wsdl

I was using Anonymous as credentials to get the session id which was working. Can u please guide me?

[juliovidigal]

Good morning, sorry for the delay.

Is this the first call, or do you want to use her return to populate the sessionId?

[rodolfo_sgl]

Ok,

this will probably solve my request problem:

let

Source = Xml.Tables(Web.Contents("https://myWSDL"
,[Content=File.Contents("PathToMyXmlfile.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

But the response is a json file. There's a way to parse it?

[juliovidigal]

In the visual layer of PowerBI, you have to give a parse ..

But if it is your case, I have a scenario that I call an API that returns a json file, and I treat it as follows ..

let
SourceTESTE = "http://mediamanager.voxus.tv/api/?method=detailed&token=XXXXXX&start_date=" & "20/11/2016" & "&end_date=" & "30/12/2050",
Fonte = Json.Document(Web.Contents(SourceTESTE )) 

......

[juliovidigal]

I'm going to do a test using </ web: returnData> ...

Tanks

[JasonL]

Have you tried to connecte to your web service with something else than Power Bi to see if it works?

I can test it from my office, proxy block advertisement website

Also are you sure your webservice returns XML?

[juliovidigal]

Yes, it is working, I have tested using SoapUI, and I have had the correct return in XML.
This is the return in SoapUI...!

<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>
<clientLoginResponse xmlns="https://advertising.criteo.com/API/v201305">
<clientLoginResult>XXXXXXXXXXXXX</clientLoginResult>
</clientLoginResponse>
</soap:Body>
</soap:Envelope>

[dataloreous]

Thank you for the very thorough reply. We had created a PowerShell script to call the XML and save on the local drive, which was then parsed by PowerQuery.  This was too many clicks for the user however so this approach is preferred.

[JasonL]

The advanced query editor opens the door to pretty much anything when you understand how it works and how Power Bi works (I mean by that all the mecanismes that are under the "hood")

[JasonL]

An other way to parse XML without using code : 

the data is all ine one feild as an XML stringright-click on the feildclick on "Analyse"click on "Table"click on this, always forget the name lolif you have all your column's name click ok and let the magic happen

If you still ahve data in one feild, just click on "Table" 

[Greg_Deckler]

Well, the world has kind of moved on from SOAP and to my knowledge there is not any SOAP accessor function in Power Query:

https://msdn.microsoft.com/en-us/library/mt296615.aspx

A couple thoughts I had, use an ODBC driver for SOAP such as here:

http://www.drdobbs.com/windows/odbc-driver-development/184416434

The other thought that I had was a SOAP to OData proxy but I really can't find one out there, all I could find was how to convert from SOAP to OData:

http://www.codeproject.com/Articles/590627/Converting-SOAP-based-WCF-Service-to-RESTful-Desig

On a personal note, I am not sorry to see SOAP go away, it was seemed like a terribly costly data protocol in terms of overhead and I always stuck with RESTful services. I have a general idea that my feelings were shared by the majority of people out there and hence why SOAP has largely gone away.

[TahaSolutions]

Any recommendation to parse XML query from a server? in my use case I should send date time range to the server and request back the data from the server.

[Manal]

Thank you!

After some more researches I found this : https://www.dreamfactory.com/content/turn-soap-rest

It seems like DreamFactory can turn a SOAP into REST WS. I will test it and see if it does work for me.

[Anonymous]

Hi @Manal,

I'am wondering if the DreamFactory approach worked? Cloud you share your experiences?

Greetings,

Ronald