Power Automate xpath

[Bsayles]

I will begin by stating this is one of my first attempts with API and PA.

I have successfully accessed a SOAP API and retrieved the xml body, but am not sure how to get the specific value of an xml element back into my flow to use as a variable in the next step.  This is a three part process to support the API call:  Login, Get Data, Log off.

Login call returns a SessionId which needs to be passed to the next step to prove authentication.  This is where I am currently stuck.  I am sure I have the syntax incorrect and would appreciate any assistance on how to write this in PA (flow).  I am currently trying to figure out how to make an xpath statement work at this step.

My returned body from the Login step is here:

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><LoginResponse xmlns="https://api.tracegains.net/v7/soap"><LoginResult><ResponseCode>200</ResponseCode><Message>Application key verified.</Message><Authenticated>true</Authenticated><CustomerId>ee50ef77-bf52-451e-ad54-8bd510dface0</CustomerId><SessionId>260e4d35-7da6-4bdc-ba56-aa335e5b3fac</SessionId><SessionExpires>2022-03-22T14:19:26.9594185Z</SessionExpires></LoginResult></LoginResponse></soap:Body></soap:Envelope>

I then need to extract the SessionID value (in green above) and use it as a variable to call the next step.  (by using xpath, I assume)

Any assistance would be appreciated.  I can supply more detail if required.

[tom_riha]

Hello @Bsayles ,

did you try the xpather.com? If you hold Ctrl and hover on the desired value it'll create the xpath to get there.

[Bsayles]

That is a great site to put into the toolkit!  That was helpful, but I am still returning [ ] as the result of my compose statement.  This what it looks like now:  I am sure I have a syntax issue, but I am not familiar enough to see it.

I am trying to get the body returned from the "Log in API call" step.  Sigh - newbie at work here.

[ManishJain]

Hi @Bsayles ,

The easier way to do this would be to use JSON function 

use JSON(xml(your xml)) to convert it to JSON object and then you can grab the data easily from JSON.

Thanks

[tom_riha]

Hello @Bsayles ,

the syntax looks fine, I'd try to remove the [1] from the xpath as that's not needed if the session id tag is there only once. You could also try to do it in multiple steps - convert it to xml(...) first in a 'Compose', then apply the xpath(...) on the 'Compose' output.

[Paulie78 · Accepted solution]

You could try extracting it out with an expression. This should work:

slice(body('Log_In_API_call'),add(indexOf(body('Log_In_API_call'), '<SessionId>'),11), indexOf(body('Log_In_API_call'), '</SessionId>'))

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[Bsayles]

Thanks, Paulie78!

This worked perfectly.  Not sure I understand it all from a syntax standpoint, but I'll try to figure out the remainder of my API call.  Can you explain what the "11" in the expression represents?  I think I understand enough of the other to get by.

Thanks again!

[Paulie78 · Accepted solution]

Happy to explain it, with the expression reformatted it is easier to comprehend:

slice
(
  body('Log_In_API_call'),
  add(indexOf(body('Log_In_API_call'), '<SessionId>'),11), 
  indexOf(body('Log_In_API_call'), '</SessionId>')
)

Slice which is a new expression to Power Automate takes the contents of a string from a given starting position to a given ending position. Example:

Slice('Tom Riha is a Legendary Power Automate Master',0,17)

Produces the output:

Tom Riha is a Leg

The starting position was 0 and it ended at character 17.

In your example we use IndexOf to find the positions within the string of the values <sessionID> and </sessionID>.

The position returned is the where the string is found. The value <sessionID> is itself 11 characters long. So the output of IndexOf has 11 added to it to move the starting position on by 11 characters so that it returns only the sessionID. Otherwise the output would be:

<SessionId>260e4d35-7da6-4bdc-ba56-aa335e5b3fac

Does that make it clearer?

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[Bsayles]

Very much!  Thank you for your assistance on this - was extremely helpful.  Now on to working my way through a "for" loop to get each data element!

[Paulie78]

It's very unlikely you need a for each to get each data element. You'd get a much faster flow with fewer steps from a select action.

[Bsayles]

Hi Paulie78 - thanks for your patience as I try to process.

I have the data from the API call in xml format.  Do I need to convert to JSON prior to using the select action?

This is part of my mess that I have going right now.  Anything you can offer to the guy mooching off of your good will would be appreciated. 🙂

[Bsayles]

Still trying to work this out.  This is what I have now:

When I test the flow, I receive an error on the last "initialize variable" step before my loop.

Error:

This error is at this point in the flow:

I am attempting to bring the output body of the previous HTTP call into an array so that I can get the multiple record listing from inside the returned xml.  The following step converts to JSON for easier mapping into an Excel table for later consumption to Power BI (at least it will when it is working).  All steps are working as expected up to and including retrieving the record count from the returned HTTP call.

Any insight or direction would be appreciated!

[Bsayles]

This is an example of what my returned xml looks like from the HTTP call.  I want to take each of these data elements and append to an existing Excel sheet to use as data source for Power BI analysis.

<Control>

                        <ID>949742</ID>

                        <ControlID>CMC/CTR/QCP/031</ControlID>

                        <ControlName>30min Quality Check - M2</ControlName>

                        <PreshipSignoffID>603473</PreshipSignoffID>

                        <VerifyValidateID>949402</VerifyValidateID>

                        <Action>Accept</Action>

                        <Status>None</Status>

                        <Attributes>

                            <AttributeValue>

                                <ID>DC Updated Date</ID>

                                <Value>3/11/2022 5:32:00 AM</Value>

                            </AttributeValue>

                        </Attributes>

                    </Control>

                    <Control>

                        <ID>949743</ID>

                        <ControlID>CMC/CRT/QCP/002</ControlID>

                        <ControlName>PT Line 30min Check</ControlName>

                        <PreshipSignoffID>603474</PreshipSignoffID>

                        <VerifyValidateID>949403</VerifyValidateID>

                        <Action>Accept</Action>

                        <Status>Warning</Status>

                        <Attributes>

                            <AttributeValue>

                                <ID>DC Updated Date</ID>

                                <Value>3/11/2022 6:57:10 AM</Value>

                            </AttributeValue>

                        </Attributes>

                    </Control>

                    <Control>

                        <ID>949744</ID>

                        <ControlID>CMC/CTR/QCP/033</ControlID>

                        <ControlName>30min Quality Check - M4</ControlName>

                        <PreshipSignoffID>603475</PreshipSignoffID>

                        <VerifyValidateID>949404</VerifyValidateID>

                        <Action>Accept</Action>

                        <Status>None</Status>

                        <Attributes>

                            <AttributeValue>

                                <ID>DC Updated Date</ID>

                                <Value>3/11/2022 5:32:14 AM</Value>

                            </AttributeValue>

                        </Attributes>

                    </Control>

Converted to JSON:

{
"ID": "949742",
"ControlID": "CMC/CTR/QCP/031",
"ControlName": "30min Quality Check - M2",
"PreshipSignoffID": "603473",
"VerifyValidateID": "949402",
"Action": "Accept",
"Status": "None",
"Attributes": {
"AttributeValue": {
"ID": "DC Updated Date",
"Value": "3/11/2022 5:32:00 AM"
}
}
},
{
"ID": "949743",
"ControlID": "CMC/CRT/QCP/002",
"ControlName": "PT Line 30min Check",
"PreshipSignoffID": "603474",
"VerifyValidateID": "949403",
"Action": "Accept",
"Status": "Warning",
"Attributes": {
"AttributeValue": {
"ID": "DC Updated Date",
"Value": "3/11/2022 6:57:10 AM"
}
}
},

[RafaelQuintana]

I have tried the suggestion seen here to no avail so maybe you can point me in the right direction.

Right now the response i on my Compose3 action is as follows: 

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<AuthorizeTransactionResponse xmlns="https://ssl.selectpayment.com/PV">
<AuthorizeTransactionResult>
<ReferenceNumber>T:43V6GWWNLA2
</ReferenceNumber>
<Success>false
</Success>
<Error>false
</Error>
<ResponseCode>Duplicate_Transaction
</ResponseCode>
<ActualDate>9999-12-31T23:59:59.9999999
</ActualDate>
<ResponseMessage>This transaction is a duplicate of a previously run transaction. This transaction was ignored.
</ResponseMessage>
<OriginatedAs>None
</OriginatedAs>
</AuthorizeTransactionResult>
</AuthorizeTransactionResponse>
</soap:Body>
</soap:Envelope>

I'm wanting to extract the ResponseCode as an example but eventually all the values individually.  This is the expression I'm currently using on the next compose action:

xpath(xml(outputs('Compose3')),'string(//ResponseCode)')

I also attempted using  xpath(xml(outputs('Compose3')),'string(AuthorizeTransactionResponse/AuthorizeTransactionResult/ResponseCode)')
 

Lastly I went to xpather and tried this as well //ResponseCode[1]/text()[1]. This is all giving me empty values. 

See below:

[RafaelQuintana]

This actually resolved my issue! I was killing myself with xpath and this was so straight forward.