Forum Discussion
Import XML response via Python
Hi everyone,
I am trying to import a SOAP XML response using Python onto the data model, but clearly I am missing something. When using "Get Data" and adding the Python script, it does not return any data however on the interpreter, I can see the XML. This is the Python code:
import requests
url='[REQUEST URL]'
headers = {'Content-Type': 'text/xml;charset=UTF-8', 'SOAPAction': 'uri:FlexForce/wsdlGetEmployees'}
body = """<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:uri="uri:FlexForce">
<soapenv:Header/>
<soapenv:Body>
<uri:wsdlGetEmployees soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<apiKey xsi:type="xsd:string">xxxx-xxxx-xxxx-xxxx</apiKey>
</uri:wsdlGetEmployees>
</soapenv:Body>
</soapenv:Envelope>"""
response = requests.post(url,data=body,headers=headers)
print (response.content)Can you please help?
Hi Anonymous and thanks for replying.
I have found a solution within my company as we have someone who knows Python in another department, and the finished code is this:import requests url='[REQUEST URL]' headers = {'Content-Type': 'text/xml;charset=UTF-8', 'SOAPAction': 'uri:FlexForce/wsdlGetEmployees'} body = """<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:uri="uri:FlexForce"> <soapenv:Header/> <soapenv:Body> <uri:wsdlGetEmployees soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <apiKey xsi:type="xsd:string">xxxx-xxxx-xxxx-xxxx</apiKey> </uri:wsdlGetEmployees> </soapenv:Body> </soapenv:Envelope>""" response = requests.post(url,data=body,headers=headers) content=response.content import pandas as pd import xml.etree.ElementTree as ET #parses the raw response into an ElementTree object etree = ET.fromstring(content) def iter_employees(xml_etree): #this selects elements in the xml until we get to the return node #which contains the items, then we iterate over the items for each in xml_etree[0][0][0].iter('item'): #for each item, we add to a dictionary the tag and text as a key:value pair #while iterating over all tags present my_dict={} for info in list(each): my_dict[info.tag]=info.text #yielding the dictionary allows this function to provide a list of dictionaries yield my_dict #we create the dataframe using the above function to pass a list of the dictionaries #to the initialising function. The keys become the column names and the values the content df=pd.DataFrame(list(iter_employees(etree)))The second part may vary according to how XML response is formatted, but I will leave this here as a reference should anyone face the same issue. Basically you need to get the response and parse it into a pandas dataframe so PBI can read it.
There are a few variations to this as you can export the XML response to a file, or a parsed CSV to a CSV file and then import it to PBI but this will completely eliminate the need for ZappySys integration for SOAP XML.
2 Replies
HI Anonymous ,
According to your description, it seems more related to python script. I'd like to suggest you to post this to python script related forum to get better support.
In addition, you can also take a look at following link about same requirement:
Python Requests package: Handling xml response
Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.
Regards,
Xiaoxin Sheng
Hi Anonymous and thanks for replying.
I have found a solution within my company as we have someone who knows Python in another department, and the finished code is this:import requests url='[REQUEST URL]' headers = {'Content-Type': 'text/xml;charset=UTF-8', 'SOAPAction': 'uri:FlexForce/wsdlGetEmployees'} body = """<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:uri="uri:FlexForce"> <soapenv:Header/> <soapenv:Body> <uri:wsdlGetEmployees soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <apiKey xsi:type="xsd:string">xxxx-xxxx-xxxx-xxxx</apiKey> </uri:wsdlGetEmployees> </soapenv:Body> </soapenv:Envelope>""" response = requests.post(url,data=body,headers=headers) content=response.content import pandas as pd import xml.etree.ElementTree as ET #parses the raw response into an ElementTree object etree = ET.fromstring(content) def iter_employees(xml_etree): #this selects elements in the xml until we get to the return node #which contains the items, then we iterate over the items for each in xml_etree[0][0][0].iter('item'): #for each item, we add to a dictionary the tag and text as a key:value pair #while iterating over all tags present my_dict={} for info in list(each): my_dict[info.tag]=info.text #yielding the dictionary allows this function to provide a list of dictionaries yield my_dict #we create the dataframe using the above function to pass a list of the dictionaries #to the initialising function. The keys become the column names and the values the content df=pd.DataFrame(list(iter_employees(etree)))The second part may vary according to how XML response is formatted, but I will leave this here as a reference should anyone face the same issue. Basically you need to get the response and parse it into a pandas dataframe so PBI can read it.
There are a few variations to this as you can export the XML response to a file, or a parsed CSV to a CSV file and then import it to PBI but this will completely eliminate the need for ZappySys integration for SOAP XML.