cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenVdP
Helper I
Helper I

Retrieve values - body HTTP request

Hi all,

I need help retrieving values when submitting a HTTP-request to SharePoint. When the HTTP-request is successful its output is in XML and I'm interested in only a few properties see the code snippet below:

 

 

 

<d:UserProfileProperties>
                <d:element m:type="SP.KeyValue">
                    <d:Key>COMPANYPeopleUnitId</d:Key>
                    <d:Value>PeopleUnitID</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
                <d:element m:type="SP.KeyValue">
                    <d:Key>BusinessSectorName</d:Key>
                    <d:Value>BusinessSectorName</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
                <d:element m:type="SP.KeyValue">
                    <d:Key>BusinessUnitName</d:Key>
                    <d:Value>BusinessUnitName</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
                <d:element m:type="SP.KeyValue">
                    <d:Key>DepartmentName</d:Key>
                    <d:Value>DepartmentName</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
            </d:UserProfileProperties>

 

 

 

How do I retrieve these values in string output so I can use these as variables throughout the flow? @Jay-Encodian 

 

The full output:

 

 

<?xml version="1.0" encoding="utf-8"?>
<entry xml:base="https://COMPANY.sharepoint.com/teams/SPSITE/_api/"
    xmlns="http://www.w3.org/2005/Atom"
    xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
    xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
    xmlns:georss="http://www.georss.org/georss"
    xmlns:gml="http://www.opengis.net/gml">
    <id>https://COMPANY.sharepoint.com/teams/SPSITE/_api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)</id>
    <category term="SP.UserProfiles.PersonProperties" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link rel="edit" href="SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)" />
    <title />
    <updated>2020-11-06T15:29:56Z</updated>
    <author>
        <name />
    </author>
    <content type="application/xml">
        <m:properties>
            <d:AccountName>i:0#.f|membership|USERID@COMPANY.com</d:AccountName>
            <d:DirectReports m:type="Collection(Edm.String)">
                <d:element>i:0#.f|membership|USERID@COMPANY.com</d:element>
            </d:DirectReports>
            <d:DisplayName>DISPLAYNAME</d:DisplayName>
            <d:Email>EMAIL@COMPANY.com</d:Email>
            <d:ExtendedManagers m:type="Collection(Edm.String)">
                <d:element>i:0#.f|membership|USERID@COMPANY.com</d:element>
            </d:ExtendedManagers>
            <d:ExtendedReports m:type="Collection(Edm.String)">
                <d:element>i:0#.f|membership|USERID@COMPANY.com</d:element>
            </d:ExtendedReports>
            <d:IsFollowed m:type="Edm.Boolean">false</d:IsFollowed>
            <d:LatestPost m:null="true" />
            <d:Peers m:type="Collection(Edm.String)">
                <d:element>i:0#.f|membership|USERID@COMPANY.com</d:element>
            </d:Peers>
            <d:PersonalSiteHostUrl>https://COMPANY-my.sharepoint.com:443/</d:PersonalSiteHostUrl>
            <d:PersonalUrl>https://COMPANY-my.sharepoint.com/personal/USERID_COMPANY_com/</d:PersonalUrl>
            <d:PictureUrl>https://COMPANY-my.sharepoint.com:443/User%20Photos/Profile%20Pictures/USERID_COMPANY_com_MThumb.jpg?t=63628926724</d:PictureUrl>
            <d:Title>FUNCTION TITLE</d:Title>
            <d:UserProfileProperties>
                <d:element m:type="SP.KeyValue">
                    <d:Key>COMPANYPeopleUnitId</d:Key>
                    <d:Value>PeopleUnitID</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
                <d:element m:type="SP.KeyValue">
                    <d:Key>BusinessSectorName</d:Key>
                    <d:Value>BusinessSectorName</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
                <d:element m:type="SP.KeyValue">
                    <d:Key>BusinessUnitName</d:Key>
                    <d:Value>BusinessUnitName</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
                <d:element m:type="SP.KeyValue">
                    <d:Key>DepartmentName</d:Key>
                    <d:Value>DepartmentName</d:Value>
                    <d:ValueType>Edm.String</d:ValueType>
                </d:element>
            </d:UserProfileProperties>
            <d:UserUrl>https://COMPANY-my.sharepoint.com:443/Person.aspx?accountname=i%3A0%23%2Ef%7Cmembership%7CUSERID%40COMPANY%2Ecom</d:UserUrl>
        </m:properties>
    </content>
</entry>

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jay-Encodian
Community Champion
Community Champion

Hey @BenVdP 

You need to use the XPath expression to obtain values from the XML... here is a reference post: https://powerusers.microsoft.com/t5/Building-Flows/Iterate-XML-with-flow/td-p/163734

For the XML nodes which use namespaces (i.e. <d:AccountName> and not just <AccountName>), you'll have to create your XPath using 'local-name()', for example:

//*[local-name()="properties"]/*[local-name()="AccountName"]/text()

as opposed to:

//properties/AccountName

 

The full XPath expression I used in the example flow below:

xpath(xml(variables('XML')),'//*[local-name()="properties"]/*[local-name()="AccountName"]/text()')

The example flow:

1.jpg

The output:

2.jpg

 

This is a good reference for XPath: https://lazyadmin.nl/office-365/power-automate-using-xpath/

HTH

Jay

View solution in original post

9 REPLIES 9
Jay-Encodian
Community Champion
Community Champion

Hey @BenVdP 

You need to use the XPath expression to obtain values from the XML... here is a reference post: https://powerusers.microsoft.com/t5/Building-Flows/Iterate-XML-with-flow/td-p/163734

For the XML nodes which use namespaces (i.e. <d:AccountName> and not just <AccountName>), you'll have to create your XPath using 'local-name()', for example:

//*[local-name()="properties"]/*[local-name()="AccountName"]/text()

as opposed to:

//properties/AccountName

 

The full XPath expression I used in the example flow below:

xpath(xml(variables('XML')),'//*[local-name()="properties"]/*[local-name()="AccountName"]/text()')

The example flow:

1.jpg

The output:

2.jpg

 

This is a good reference for XPath: https://lazyadmin.nl/office-365/power-automate-using-xpath/

HTH

Jay

View solution in original post

BenVdP
Helper I
Helper I

@Jay-Encodian , I get an error when running the flow... 

 

config.jpgERR.jpg

Jay-Encodian
Community Champion
Community Champion

@BenVdP 

You don't need to use the variable, just pass the SharePoint body value direct to the Xpath expression.

 

BenVdP
Helper I
Helper I

Doing that gives me an empty value.

 

Full xpath I used, where the orange part is the 'body' output of the HTTP request.

 

xpath(xml(outputs('Send_an_HTTP_request_to_SharePoint')?['body']),'//*[local-name()="properties"]/*[local-name()="BusinessUnitName"]/text()')

 

xpath(xml(outputs('Send_an_HTTP_request_to_SharePoint')?['body']),'//*[local-name()="properties"]/*[local-name()="BusinessSectorName"]/text()')

 

xpath(xml(outputs('Send_an_HTTP_request_to_SharePoint')?['body']),'//*[local-name()="properties"]/*[local-name()="BusinessDepartmentName"]/text()')

 

@Jay-Encodian 

Jay-Encodian
Community Champion
Community Champion

@BenVdP 

Can you confirm which HTTP request you are sending to SharePoint? I'll check for you

Hi @Jay-Encodian 

 

I'm sending 'api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)?@v='i:0%23.f|membership|prinicpalname@company.com'

 

Output-HTTP.jpgSP-HTTP.jpg

 

Jay-Encodian
Community Champion
Community Champion

Hey @BenVdP 

Ok 🙂 - my first response is absolutely correct and we should mark as a solution as it answers the question on how to obtain data from XML for others researching the community.

I did wonder how you were obtaining an XML response from an SP REST API as I thought all the data comes back as JSON... and you can see in your body and the example below, it is JSON and not XML which is why the XPath expression is failing.

1.jpg

You simply need to parse the response using the 'Parse JSON' action.

HTH

Jay

@Jay-Encodian , woeps my bad.

 

I'm having difficulties generating the JSON schema. When I generate a sample schema like this, it seems that I cannot access the values under the "UserProfileProperties", so can you shed your light on it, again? 🙂

{
    "type": "object",
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "__metadata": {
                    "type": "object",
                    "properties": {
                        "id": {
                            "type": "string"
                        },
                        "uri": {
                            "type": "string"
                        },
                        "type": {
                            "type": "string"
                        }
                    }
                },
                "AccountName": {
                    "type": "string"
                },
                "DirectReports": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "results": {
                            "type": "array",
                            "items": {
                                "type": "string"
                            }
                        }
                    }
                },
                "DisplayName": {
                    "type": "string"
                },
                "Email": {
                    "type": "string"
                },
                "ExtendedManagers": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "results": {
                            "type": "array"
                        }
                    }
                },
                "ExtendedReports": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "results": {
                            "type": "array",
                            "items": {
                                "type": "string"
                            }
                        }
                    }
                },
                "IsFollowed": {
                    "type": "boolean"
                },
                "LatestPost": {},
                "Peers": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "results": {
                            "type": "array"
                        }
                    }
                },
                "PersonalSiteHostUrl": {
                    "type": "string"
                },
                "PersonalUrl": {
                    "type": "string"
                },
                "PictureUrl": {},
                "Title": {},
                "UserProfileProperties": {
                    "type": "object",
                    "properties": {
                        "results": {
                            "type": "array",
                            "items": {
                                "type": "object",
                                "properties": {
                                    "__metadata": {
                                        "type": "object",
                                        "properties": {
                                            "type": {
                                                "type": "string"
                                            }
                                        }
                                    },
                                    "Key": {
                                        "type": "string"
                                    },
                                    "Value": {
                                        "type": "string"
                                    },
                                    "ValueType": {
                                        "type": "string"
                                    }
                                },
                                "required": [
                                    "__metadata",
                                    "Key",
                                    "Value",
                                    "ValueType"
                                ]
                            }
                        }
                    }
                },
                "UserUrl": {
                    "type": "string"
                }
            }
        }
    }
}

I only have access to these:

 

2020-11-13_14-13-45.jpg

 

Jay-Encodian
Community Champion
Community Champion

Hi @BenVdP 

With nested JSON you need to parse the layers, for example: https://powerusers.microsoft.com/t5/Building-Flows/Help-with-nested-JSON/td-p/349199

HTH

Jay

 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,314)