I am starting with Power BI and now I am faced with the challenge of connecting to a data source from a WebService made on asmx, the WebService address is:
http://www.testws.com:65/com_InfoListAll.asmx
When entering the above address from the browser, the information contained in my Web Service is as follows:
Com_InfoListAll
The following operations are supported. For a formal definition, please review the Service Description.
- InfoTravel
- ShowList
This web service is using http://tempuri.org/ as its default namespace.
Recommendation: Change the default namespace before the XML Web service is made public.
Each XML Web service needs a unique namespace in order for client applications to distinguish it from other services on the Web. http://tempuri.org/ is available for XML Web services that are under development, but published XML Web services should use a more permanent namespace.
Your XML Web service should be identified by a namespace that you control. For example, you can use your company's Internet domain name as part of the namespace. Although many XML Web service namespaces look like URLs, they need not point to actual resources on the Web. (XML Web service namespaces are URIs.)
For XML Web services creating using ASP.NET, the default namespace can be changed using the WebService attribute's Namespace property. The WebService attribute is an attribute applied to the class that contains the XML Web service methods. Below is a code example that sets the namespace to "http://microsoft.com/webservices/":
C#
[WebService(Namespace="http://microsoft.com/webservices/")]
public class MyWebService {
// implementation
}
Visual Basic
<WebService(Namespace:="http://microsoft.com/webservices/")> Public Class MyWebService
' implementation
End Class
C++
[WebService(Namespace="http://microsoft.com/webservices/")]
public ref class MyWebService {
// implementation
};
For more details on XML namespaces, see the W3C recommendation on Namespaces in XML.
For more details on WSDL, see the WSDL Specification.
For more details on URIs, see RFC 2396.
It is clearly evident that I use two methods, which are InfoTravelandShowList
The address of the method InfoTravelis:
http://www.testws.com:65/com_InfoListAll.asmx?op=InfoTravel
The address of the method ShowListis:
http://www.testws.com:65/com_InfoListAll.asmx?op=ShowList
The content of the method InfoTravelis as follows:
Com_InfoListAll
Click here for a complete list of operations.
InfoTravel
Test
To test the operation using the HTTP POST protocol, click the 'Invoke' button.
Parameter Value
username:
password:
Type:
ID:
SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.
POST /com_InfoListAll.asmx HTTP/1.1
Host: www.testws.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/InfoTravel"
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<InfoTravel xmlns="http://tempuri.org/">
<username>string</username>
<password>string</password>
<Type>int</Type>
<ID>string</ID>
</InfoTravel>
</soap:Body>
</soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<InfoTravelResponse xmlns="http://tempuri.org/">
<InfoTravelResult />
</InfoTravelResponse>
</soap:Body>
</soap:Envelope>
SOAP 1.2
The following is a sample SOAP 1.2 request and response. The placeholders shown need to be replaced with actual values.
POST /com_InfoListAll.asmx HTTP/1.1
Host: www.testws.com
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<InfoTravel xmlns="http://tempuri.org/">
<username>string</username>
<password>string</password>
<Type>int</Type>
<ID>string</ID>
</InfoTravel>
</soap12:Body>
</soap12:Envelope>
HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<InfoTravelResponse xmlns="http://tempuri.org/">
<InfoTravelResult />
</InfoTravelResponse>
</soap12:Body>
</soap12:Envelope>
HTTP GET
The following is a sample HTTP GET request and response. The placeholders shown need to be replaced with actual values.
GET /com_InfoListAll.asmx/InfoTravel?username=string&password=string&Type=string&ID=string HTTP/1.1
Host: www.testws.com
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<anyType xmlns="http://tempuri.org/" />
HTTP POST
The following is a sample HTTP POST request and response. The placeholders shown need to be replaced with actual values.
POST /com_InfoListAll.asmx/InfoTravel HTTP/1.1
Host: www.testws.com
Content-Type: application/x-www-form-urlencoded
Content-Length: length
username=string&password=string&Type=string&ID=string
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<anyType xmlns="http://tempuri.org/" />
The content of the method ShowListis:
Com_InfoListAll
Click here for a complete list of operations.
ShowList
Test
To test the operation using the HTTP POST protocol, click the 'Invoke' button.
Parameter Value
username:
password:
SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.
POST /com_InfoListAll.asmx HTTP/1.1
Host: www.testws.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/ShowList"
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ShowList xmlns="http://tempuri.org/">
<username>string</username>
<password>string</password>
</ShowList>
</soap:Body>
</soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ShowListResponse xmlns="http://tempuri.org/">
<ShowListResult />
</ShowListResponse>
</soap:Body>
</soap:Envelope>
SOAP 1.2
The following is a sample SOAP 1.2 request and response. The placeholders shown need to be replaced with actual values.
POST /com_InfoListAll.asmx HTTP/1.1
Host: www.testws.com
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<ShowList xmlns="http://tempuri.org/">
<username>string</username>
<password>string</password>
</ShowList>
</soap12:Body>
</soap12:Envelope>
HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<ShowListResponse xmlns="http://tempuri.org/">
<ShowListResult />
</ShowListResponse>
</soap12:Body>
</soap12:Envelope>
HTTP GET
The following is a sample HTTP GET request and response. The placeholders shown need to be replaced with actual values.
GET /com_InfoListAll.asmx/ShowList?username=string&password=string HTTP/1.1
Host: www.testws.com
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<anyType xmlns="http://tempuri.org/" />
HTTP POST
The following is a sample HTTP POST request and response. The placeholders shown need to be replaced with actual values.
POST /com_InfoListAll.asmx/ShowList HTTP/1.1
Host: www.testws.com
Content-Type: application/x-www-form-urlencoded
Content-Length: length
username=string&password=string
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<anyType xmlns="http://tempuri.org/" />
As you can see, each method requests some parameters
The data source in Power BI to which I am connecting is Web and I do it as follows, adding the main URL of my Web Service in the basic section:
When trying to make the connection in this way I get the following error message:
Also try from the advanced section adding the URL in parts with each of the methods as follows:
Here with this option if the connection is made but the data obtained is the same parameters and what is found in the HTML.
I don't know if I have to make any previous configuration in the advanced section regarding the parameters, I hope someone can give me a little guidance on how to do it.
Update 1:
I have tried connecting via the blank query data source as follows:
And I have added the following query:
let
Source = Web.Contents(
"http://www.testws.com:84/com_InfoListAll.asmx",
[
RelativePath = "InfoTravel",
Query=
[
username = "username",
password = "password",
Type = "99",
ID = "1001"
]
])
in
Source
Connecting to this data source I get the binary file as follows:
Luego presiono sobre el y me carga toda la información en un solo campo de la tabla de la siguiente manera:
Quisiera saber si hay un paso previo a cargar los datos o los estoy cargando incorrectamente.
Primero, lo que deberías realizar son conexiones separadas por cada método de los cuales vas a recuperar información para obtener dos conjuntos de datos por separado. (Al especificarlo con un "?" le estas diciendo que es un parametro, no un método de tu servicio)
Posterior a esto, debes especificar los parámetros de cada método en las opciones avanzadas del conector web, de manera de ir conformando el GET/POST de cada URL.
Si tu parametro debe ser variable, debes ir a esta sección en Power BI e ir definiendo cada uno de ellos, según cada tipo de dato.
The following link details what was previously explained Consuming XML web service data in Power BI
Cheers,