How to get sharepoint list items as xml ?

(0) Share

Report

Posted on by bouillons

302

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

Categories:

General topics

All responses (12)

Answers (1)

Sort by

Verified answer

mahoneypat 1,720 on at

Like
a
(0)

Report
Copy link

Link copied!

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 302 on at

Like
a
(0)

Report
Copy link

Link copied!

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 302 on at

Like
a
(0)

Report
Copy link

Link copied!

The expression I'm using is
xpath(outputs('Compose_2'),'string(//*[name()="Tel"][1])')
and I'm getting back a blank string
bouillons 302 on at

Like
a
(0)

Report
Copy link

Link copied!

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 1,720 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Pat
bouillons 302 on at

Like
a
(0)

Report
Copy link

Link copied!

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 1,720 on at

Like
a
(0)

Report
Copy link

Link copied!

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 302 on at

Like
a
(0)

Report
Copy link

Link copied!

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 1,720 on at

Like
a
(0)

Report
Copy link

Link copied!

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 19 on at

Like
a
(0)

Report
Copy link

Link copied!

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