cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Nintex Newbie

Query XML from Excel Rest API and parse with XPATH

Jump to solution

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?

Labels: (1)
0 Kudos
Reply
1 Reply
Highlighted
Nintex Newbie

Re: Query XML from Excel Rest API and parse with XPATH

Jump to solution

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

Regex Replace ActionRegex Replace Action

View solution in original post

0 Kudos
Reply