How to get data from a SOAP WS with Power BI

dataloreous
Advocate III
9 years ago
Were you able to make this approach work? We are also trying to call and load the a SOAP response from PowerBI without sucess. I read through the other messages stating that SOAP is a dying technology - however we do not have control over the software providing the data.
- JasonL
  Advocate II
  9 years ago
  Hello dataloreous,
  
  Yes we got it to work. Yes soapy SOAP is old and we are also stuck in the past... the joys of working for the governement...
  
  This being said :
  Our SOAP web service return an XML file and need a user and a PW and some other info to get the data we want.
  You need to start somewhere and get data : either with an XML file or with the web service's URL. (its doesnt has any importance because we just want to get to the query editor (Power Query)
  So we started to get data from an XML file because we already tested our web service with an other app and we got the XML it returns, so we loaded it. And drilled down till we get something to load/modify :
  As I said, you can simply load the web service URL and use "WEB" to get data. The goal is to load something then access the query editor and advanced editor.
  In the query editor, you accesse the advanced editor (sorry for my french):
  in the advanced editor you change your source to :
  let
  Source = Xml.Tables(Web.Contents("URLofTheWebService",[Content=File.Contents("D:\download\post.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]])),
  We use Xml.Tables because as I said our web service return an Xml and web.contents because we connect to a web service
  "D:\download\post.xml" is the path to the XML file where we have the credentials to connect to the web service, you have to call it in your query, here's an example of the file (made with SOAP UI open source) :
  The rest of the query code depends on the structur of your XML, here's what we have :
  Table = Source{0}[Table],
  Table1 = Table{0}[Table],
  Table2 = Table1{0}[Table],
  Table3 = Table2{0}[Table],
  Table4 = Table3{0}[Table],
  #"Changed Type" = Table.TransformColumnTypes(Table4,{{"chaineXml", type text}, {"codeRetour", Int64.Type}, {"codeSysteme", type text}, {"messageRetour", type text}, {"nbrErreurs", Int64.Type}, {"noFormulaire", Int64.Type}, {"nomSysteme", type text}, {"version", Int64.Type}}),
  chaineXml1 = #"Changed Type"{0}[chaineXml],
  #"Parsed XML" = Xml.Tables(chaineXml1),
  Table5 = #"Parsed XML"{0}[Table],
  Table6 = Table5{0}[Table],
  #"Expanded Namespace:" = Table.ExpandTableColumn(Table6, "Namespace:", {"column1", "column2", "columnXX"}, {"Namespace:.column1", "Namespace:.column2", "Namespace:.columnXX"}),
  #"Expanded Namespace:.column1" = Table.ExpandTableColumn(#"Expanded Namespace:", "Namespace:.column1", {"column2", "column3", "columnXX""}, {"Namespace:.column1.column2", "Namespace:.column1.column3", "Namespace:.column1.columnXX"})
  in
  #"Expanded Namespace:.column1"
  Our code is structured like that because the XML file we get has all the data as XML text in one feild so we have to parse de data and transform the XML string in a table
  
  Hope this help!
  
  Jason
  - JasonL
    Advocate II
    9 years ago
    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"
Anonymous
Not applicable
8 years ago
Hi all,

I am getting the following error.

DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: The 'link' start tag on line 31 position 6 does not match the end tag of 'head'. Line 80, position 3.)
Details:
Binary

Here is my Advanced Editor code:

let Source = Xml.Tables(Web.Contents("https://sandbox......./", [Content=File.Contents("C:\Users\....\get_default.xml"),Headers=[Authorization="Bearer 34653fdsgfdsffd", #"Content-Type"="text/xml", SOAPAction="http://....../get.........Details"]])) in Source

Here is my Xml code:

<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:ins='http://beans....._v3'> <soapenv:Header/> <soapenv:Body> <ins:removedforprivacy> <startDate>2012-10-17</startDate> <endDate>2012-10-22</endDate> <pagingInfo> <pageNumber>0</pageNumber> <pageSize>5</pageSize> </pagingInfo> </ins:removedforprivacy>
</soapenv:Body>
</soapenv:Envelope>
This code works in R using RCurl, I have also tried it with the addition of <?xml version="1.0"?> at the start. I also tried it using the Content=Text.ToBinary(".....").

Could the issue be with the file being returned?

Thank you
:)

Edit 1: grammar etc.

Edit 2: The issue was the the main url - the inititial url used to set up the web access and a secondary used in the advanced editor. This was followed up by another issue where the data would not be loaded out of the query editor... This was solved by making sure the page size fit the data limit.
- juliovidigal
  Advocate I
  8 years ago
  Apparently it's a problem with the return, even by the mention of lines 31 and 80, and your call does not have that size.
  - Anonymous
    Not applicable
    8 years ago
    thank you for responding, I have figured out the problem.. I will edit my post to relect it.