Import XML response via Python

[Anonymous]

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?

[v-shex-msft]

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.

Python _ Forums

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

[Anonymous · Accepted solution]

Hi @v-shex-msft  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.