camelo365,How to get data from a SOAP WS with Power BI

[camelo365]

let
Source = Xml.Tables(Web.Contents("http://www.ahgora.com.br/ws/pontoweb.php?wsdl"
,[Content=Text.ToBinary("<soapenv:Envelope xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:ws="&Character.FromNumber(34)&"http://www.ahgora.com.br/ws"&Character.FromNumber(34)&">
soapenv:Header/
soapenv:Body
ws:obterFuncionarios
15151531351313135131351313531531
230
</ws:obterFuncionarios>
</soapenv:Body>
</soapenv:Envelope>",
Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]])))
in
Source

---

let
Source = Xml.Tables(Web.Contents("https://xxx.xxxxx.com/wsapi/services/authentication?WSDL",[Content=File.Contents("xxxx\xxxxx\xxxxx.xml"),
Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sec=http://.net/security">
soapenv:Header/
soapenv:Body
sec:LoginRequest
sec:userapiuser</sec:user>
sec:passwordapipassword</sec:password>
</sec:LoginRequest>
</soapenv:Body>
</soapenv:Envelope>

---

This is my code and it returns data and works.
let
SourceURL = "url of wsdl",

options = [ #"Authorization" ="Basic XXXX",
#"Accept-Encoding"= "gzip,deflate",
// SOAPAction="",
#"Content-Type"="text/xml;charset=UTF-8",
#"Connection"="Keep-Alive"
],
WebContent = Web.Contents(SourceURL,
[Content=Text.ToBinary("

<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) 

xmlns:v1=#(0022)Host URL#(0022)>
soapenv:Header/
soapenv:Body
v1:getAll

2018-10-10T00:00:00Z

2018-10-10T00:00:00Z
</v1:getAll>
</soapenv:Body>
</soapenv:Envelope>

"),
Headers=options]) ,
XmlContent = Xml.Tables(WebContent),
Table = XmlContent{0}[Table],
Table1 = Table{0}[Table],
Table2 = Table1{0}[Table],
Table3 = Table2{0}[Table],
Table4 = Table3{0}[Table],
in

---

Then I add this code before I call the service
let
//parameters
Date2 = Date.AddDays(Date.From(#"StartingDate"), #"Days"),
Year = Text.From(Date.Year(#"StartingDate")),
Month= Text.From(
if Text.Length(Number.ToText(Date.Month(#"StartingDate"))) = 1
then Text.Combine({"0",Number.ToText(Date.Month(#"StartingDate"))}, "")
else Date.Month(#"StartingDate")
),
Day = Text.From(
if Text.Length(Number.ToText(Date.Day(#"StartingDate"))) = 1
then Text.Combine({"0",Number.ToText(Date.Day(#"StartingDate"))}, "")
else Date.Day(#"StartingDate")
),

Year2 = Text.From(Date.Year(Date2)),
Month2= Text.From(
        if Text.Length(Number.ToText(Date.Month(Date2))) = 1 
            then Text.Combine({"0",Number.ToText(Date.Month(Date2))}, "") 
            else Date.Month(Date.AddDays(Date.From(Date2)))
            ),
Day2 =  Text.From(
        if Text.Length(Number.ToText(Date.Day(Date2))) = 1 
            then Text.Combine({"0",Number.ToText(Date.Day(Date2))}, "") 
            else Date.Day(Date2)
            ),

//Create Date filters

DateFrom = Text.Combine({Year,Month,Day}, "-") ,
DateTo = Text.Combine({Year2,Month2,Day2}, "-") ,

---

And then when it gets to the XML i simply do this
cWebContent = Web.Contents(SourceURL,
[Content=Text.ToBinary("

<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:v1=#(0022)XXXXXXXXXX#(0022)>

soapenv:Header/
soapenv:Body
v1:getAllLandings

     <from>"&DateFrom&"</from>
     <!--Optional:-->
     <to>"&DateTo&"</to>
  </v1:getAllLandings>

</soapenv:Body>
</soapenv:Envelope>

"),

---

This is what I came up with in the end to keep generating requests with the correct mark untill the more value is false:, may it help someone
ca = List.Generate( () => [Page = GetTraces(mark, customer, creds)],
each [Page]{0}[more] <> "false",
each [Page = GetTraces([Page]{0}[mark], customer, creds)]

---

I use this query:

let
Source = Xml.Tables(Web.Contents("xxxxxxxxxxxx"
,[Content=File.Contents("xxxxxxx\post.xml"),Headers=[Authorization="Basic xxxxxxxxxxx" ,#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

The Authorization is OK. My post.xml is:

<soap:Envelope xmlns:urn="urn:sap-com:document:xxxxxxxx" xmlns:soap-envelope="http://www.w3.org/2003/05/soap-envelope" >
soap:Header/
soap:Body
urn:ZimStockProl
xx
xx
</urn:ZimStockProl>
</soap:Body>
</soap:Envelope>

I try it in SOAPUI and is OK, but the result in Power BI is different:
I think It must be the direccion "urn"

---

I ve got the same issue
with the query:
let
body="<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:csv=#(0022)http://www.csv.org#(0022)>
soapenv:Header/
soapenv:Body
csv:ReportTransactions
csv:DateFrom2020-01-01</csv:DateFrom>
csv:DateBy2020-01-04</csv:DateBy>
csv:UNP691759953</csv:UNP>
</csv:ReportTransactions>
</soapenv:Body>
</soapenv:Envelope>",

Source = Xml.Tables(Web.Contents("http://XXXXX.1cws?wsdl",
[Content=Text.ToBinary(body), 
Headers =[
    #"Authorization" = "basic XXXX",
    #"Accept-Encoding"= "gzip,deflate",
    #"User-Agent"= "Apache-HttpClient/4.1.1 (java 1.5)",
    //#"Content-Length"="360",
    //SOAPAction="#(0022)http://www.csv.org#JI_WScsv:ReportTransactions_DD#(0022)",
    #"Content-Type"="application/soap+xml;charset=UTF-8;action=#(0022)http://www.csv.org#JI_WScsv:ReportTransactions_DD#(0022)",
    #"Connection"="Keep-Alive"]]))

in
Source

---

it works in SOAP UI

but it returns result like no parameters are posted to service. Expanding of these tables does not give needed result

webservice_not_posted_parameters.jpg fiddler's header after SOAP UI (when SOAP gives needed result)
Request Headers
POST/ws_csws HTTP/1.1
Client
Accept-Encoding:gzip,deflate
User-Agent:Apache-HttpClient/4.1.1(java 1.5)
Entity
Content-Length:360
Content-Type:application/soap+xml;charset=UTF-8,action="http://www.csv.org#JI_WScsv:ReportTransactions_DD"
Security
Authorization:Basic
Transport
Connect:Keep-Alive
Host:srv-1c-02

---

I needed in URL of WSDL - remove in the end "?wsdl"

let
body="<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:csv=#(0022)http://www.csv.org#(0022)>
soapenv:Header/
soapenv:Body
csv:ReportTransactions
csv:DateFrom2020-01-01</csv:DateFrom>
csv:DateBy2020-01-04</csv:DateBy>
csv:UNP691759953</csv:UNP>
</csv:ReportTransactions>
</soapenv:Body>
</soapenv:Envelope>",
Source = Xml.Tables(Web.Contents("http://XXXXXXXXXXX/ws/ws_csv.1cws",

// NOT http://XXXXXXXXXXX/ws/ws_csv.1cws?wsdl -
// this ending ?WSDL stopped the recieving of the response!
[Content=Text.ToBinary(body),
Headers =[
#"Authorization" = "basic XXXXXXXXXX",
#"Accept-Encoding"= "gzip,deflate",
#"Content-Type"="text/xml;charset=UTF-8"
]])),
Table = Source{1}[Table],
Table1 = Table{0}[Table],
Table2 = Table1{0}[Table]
in

---