How to get sharepoint list items as xml ?

[bouillons]

Hi,

When I use get items from a sharepoint list, it returns the data in json format. Is there a way to get the data in xml instead, or an easy and fast way to convert the json result to an xml string ?

Stephane

[mahoneypat · Accepted solution]

The SharePoint REST API returns data as XML by default.  Below is an example.  Choose your site from the picklist and then enter the text shown and substitute in your list name (in place of ButtonList).  Also put the shown header for Accept to get an XML response.  Then use a Compose to extract the $content from the response and convert it to a string (see expression in Note of Compose step).

Working with lists and list items with REST | Microsoft Docs

Pat

[bouillons]

Thanks Pat !

I'm able to get the xml of the list, but my ultimate goal is to extract an item using xpath.

So, the first thing I did was convert the output of base64tostring function using xml(base64...)

Next I'm trying to get an item from the xml using an xpath, but it doesn't seem to work. I'm trying to extract the value of the Tel field for the first item.

[bouillons]

The expression I'm using is 

xpath(outputs('Compose_2'),'string(//*[name()="Tel"][1])')

and I'm getting back a blank string

[bouillons]

Using the below expression in the xpath tester at http://xpather.com gives me the correct result:

//m:properties[starts-with(d:Title,'0002')]/d:Tel

but entering this expression in the xpath(outputs('Compose_2'),'//m:properties[starts-with(d:Title,"0002")]/d:Tel')) gives an error

[mahoneypat]

Why not just return the data as JSON instead of XML to greatly simplify things?

Pat

[bouillons]

I prefer to work with xml as it is soooo much faster than using apply for each. Unfortunately, xpath seems to balk at xml containing namespaces, at least, that's what I suspect. When I use a namespaceless xml, the xpath queries work as expected.

[mahoneypat]

Since you are pulling out a specific field, have you tried to use a Select action instead of an Apply to each.  Would be much faster.

Pat

[bouillons]

Thanks, my scenario is that I want to combine the data of two or more lists that share a common field. I am already using select, but I can only give one array as input and I need to pull in a field from the other array where list1.CommonField is equal to list2.CommonField.

I have not found how to do that in a standard select, but I achieved the desired result using xpath, so my performance has gone from minutes to seconds.

If I could just natively use the xml returned by the sharepoint API, that would be even faster than using the Get Items first and converting that to Xml. There, I'm still stuck with the xpath syntax that seems to balk on the namespaces. I opened another community thread for that specifically.

Thanks for your time and effort Pat, greatly appreciated !

[mahoneypat]

Ok. Please see this solution on how to filter one array for those that exist (or not) in another.

Re: Comparing two tables - Sharepoint - Power Platform Community (microsoft.com)

Pat

[WG3]

Questions @mahoneypat, @bouillons, how do you return JSON instead of XML?

How should the example URL for instead be be changed? https://X.sharepoint.com/sites/A/_api/web/lists/getbytitle('B')/items

[thepenguin]

I'm not really a fan of xml, but I find that powerautomate is much happier working with local names, although it makes it a bit more cumbersome:

xpath(outputs('Compose'),'//*[local-name()="properties"]/*[local-name()="Title" and starts-with(text(), "0002")]/../*[local-name()="Tel"]/text()')

[dperez13]

I have this:

 xpath(outputs('XML'),'//root/value/*[ends-with(text(),"XX")]')

and im getting its not valid xpath parameter, im thinking my xml statement up above needs to be tweaked for this to work?

heres the xml output that the statement above composes from

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))