Solved

Query XML from Excel Rest API and parse with XPATH

  • 23 April 2019
  • 1 reply
  • 94 views

Badge +3

I've got a workflow that uses the Excel Rest API get range to pull data from a workbook in a document library. The call is working and I can see the resulting XML response but I have not been able to get the data into a collection. The collection always comes out empty.

 

I used an XPath Tester and was able to get just the values back with this expression but it does not return anything when used in the workflow.

//GetRangeResult/anyType/anyType/text()

Here is a sample of the XML that is returned by the Excel Rest API.

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetRangeResponse xmlns="http://schemas.microsoft.com/office/excel/server/webservices"><GetRangeResult><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$10.56</anyType><anyType xsi:type="xsd:string">$12.41</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$10.55</anyType><anyType xsi:type="xsd:string">$12.40</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$10.55</anyType><anyType xsi:type="xsd:string">$12.40</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$8.97</anyType><anyType xsi:type="xsd:string">$10.54</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$9.00</anyType><anyType xsi:type="xsd:string">$10.58</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$9.01</anyType><anyType xsi:type="xsd:string">$10.59</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$10.47</anyType><anyType xsi:type="xsd:string">$13.20</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$10.53</anyType><anyType xsi:type="xsd:string">$13.27</anyType></anyType><anyType xsi:type="ArrayOfAnyType"><anyType xsi:type="xsd:string">$10.50</anyType><anyType xsi:type="xsd:string">$13.24</anyType></anyType></GetRangeResult></GetRangeResponse></soap:Body></soap:Envelope>

Any idea what I am doing wrong?

icon

Best answer by stantond 24 April 2019, 16:45

View original

1 reply

Badge +3

I was able to solve the issue.

 

The name space link

xmlns="http://schemas.microsoft.com/office/excel/server/webservices"

used by Excel Rest API points to a page that is no longer there.

 

I removed it with a Regex replace action and was able to get the result I was looking for with this Xpath expression.

//GetRangeResult/anyType/anyType

Reply