xml API call not working

[simonliucan]

Hi folks,

I was trying to make API call using xml in Power Query but it's not working. It either gives error message "Internal Server Error 500", or returns HTTP code 200, but with Content Length = -1. I use the same xml in SoapUI and it returns data successfully. Is it because format issue (quotation marks?), or incorrect functions I use? 

Thanks,

[v-jingzhang]

Hi @simonliucan 

Did you manage to solve this problem? If not, maybe you can share how you set up the request in SoapUI with screenshots (removing sensitive info). We can help check whether some small details have been neglected. If you have solved it, can you share the final solution here to help the community?

Best Regards,
Community Support Team _ Jing

[simonliucan · Accepted solution]

Yes problem solved, I just separate xml.tables(web.comtents)) into 2 steps:

Source = web.comtents(SourceURL, content=..., header = options)

XMLTable = Xml.Tables(Source)

......

and it worked.

thanks for all your reply.

[PhilipTreacy]

@simonliucan 

Odd, splitting 1 step into 2 should not fix it.

[PhilipTreacy]

Hi @simonliucan 

Like this

Web.Contents( "https://www.website.com", 
                  
                     [ 

                         RelativePath = "OrderService.cfc",

                         Query = [wsdl = YourText]

                     ] 
)

regards

Phil

[simonliucan]

Thanks Phil.

But I still don't understand, I have already put the xml in the "Body" (see my screenshot), which is the wsdl I believe? What should I put in the query section?

[PhilipTreacy]

Hi @simonliucan 

The point I'm trying to make is that if you are supplying the request in the Body then do you need to also supply an empty query parameter called wsdl ?

If the request Body contains everything required by the API for a successful request, then why are you also supplying wsdl in the query parameters?

Try removing ?wsdl from the SourceURL.

Another thing you could try : all the URL's in your body are HTTP.  They actually redirect to HTTPS if you try to access them in your browser.  So try changing them all to HTTPS e.g. https://www.w3.org/2001/XMLSchema

Please bear in mind that I have no access to your API to test it and I have no idea how it works so I am offering advice based on what looks like it might be causing issues.

Regards

Phil

[PhilipTreacy]

Hi @simonliucan 

Your SourceURL contains a named query parameter wsdl but you aren't provinding a value for this.  You can try either removing the wsdl part or supplying a value for.

Also, try just setting the SoureURL as the website domain e.g. 

SourceURL = "htps://www.website.com"

Then specify the subfolder using the RelativePath e.g.

Web.Contents( "https://www.website.com", [ RelativePath = "OrderService.cfc" ] )

Query Parameters like wsdl can also be specified using the Query field of the Options record, see the documentation:

Web.Contents - PowerQuery M | Microsoft Docs

Regards

Phil

[simonliucan]

Thanks Phil, can you give me an exmaple of how to specify wsdl using querying field?