cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vstar19
Helper III
Helper III

Reading XML data stored in SharePoint.

Hello. 

I need to be able to extract some data from a XML file stored in SharePoint. 

I am using the SharePoint Get File content action. It successfully reads the file. Here is a sample of the data

 

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>User, Test</displayName>
<ID>123456</ID>
</data>
<data>
<co>United States</co>
<department>HR</department>
<division>Purchasing</division>
<displayName>Smith, John</displayName>
<ID>7891011</ID>
</data>
<data>
<co>United States</co>
<department>IT</department>
<division>Marketing</division>
<displayName>Bloggs, Joe</displayName>
<ID>13141516</ID
</data>

 

This is just a sample, this XML file has approx 60K records. I need to get the ID of each record and then compare that with another ID I have in another database. (i was hoping i could use a Condition to do this).

 

I have tried to use the following Expression body('XMLFile')?['DocumentElement']?['data']?['ID'] - but it throws back an error ".....cannot be evaluated because property 'data' cannot be selected". 

I also tried using the xpath expression, like  xpath(xml(triggerBody()?['XMLFile']),'string(//ID)') , this also throws back an error. 

 

I have not used XML data before within PowerAutomate. 

 

Can anyone please assist me?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Hey @Vstar19 ,,

 

Sorry Mate, was busy so could not get back to you.

 

Your XML

 

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>User, Test</displayName>
<ID>123456</ID>
</data>
<data>
<co>United States</co>
<department>HR</department>
<division>Purchasing</division>
<displayName>Smith, John</displayName>
<ID>7891011</ID>
</data>
<data>
<co>United States</co>
<department>IT</department>
<division>Marketing</division>
<displayName>Bloggs, Joe</displayName>
<ID>13141516</ID>
</data>
</DocumentElement>

 

Add Compose - 

 

xpath(xml(outputs('Compose_2')),'//*[local-name()="ID"]/text()')
 
CFernandes_0-1618327869560.png

 

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

11 REPLIES 11
CFernandes
Super User III
Super User III

Hey @Vstar19 ,

 

Firstly the XML you pasted is not valid - I tested it in online validator and found three problems 

> bracket missing 

>additional element  

> and multiple Root node.

 

You need to first work around the above.

 

Now as a Test I did copy one node -

 

<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>User, Test</displayName>
<ID>123456</ID>
</data>

 

Converted to XML and then extract ID using Formula below and it worked.

 

xpath(xml(outputs('Compose')),'//*[local-name()="ID"]/text()')
 
CFernandes_1-1618230262618.png

 

 
 
CFernandes_0-1618230237445.png

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Hi. Thanks for the reply. Ok, fixed the XML.

I entered your Xpath into a compose and this is the error i got : 

 

The provided value cannot be converted to XML: 'JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document.

 

So i put it in an Apply to Each Action.

 

'Apply_to_each_2' failed: the result of the evaluation of 'foreach' expression '@outputs('XMLFile')?['body']' is of type 'String'. The result must be a valid array.

 

It needs to be able to show the ID for every item (if that makes sense)

The error still tells - The provided value cannot be converted to XML: 'JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document.

 

Please break the XML into three separate parts e.g. 

 

<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>User, Test</displayName>
<ID>123456</ID>
</data>

 

and then try my formula.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Vstar19
Helper III
Helper III

OK, so this is the XML that i have input, i have copied and pasted the output. 

 

<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>1User, Test</displayName>
<ID>123456</ID>
</data>
<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>2User, Test</displayName>
<ID>123456</ID>
</data>
<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>3User, Test</displayName>
<ID>123456</ID>
</data>

 

It then throws back this error : 

 

'Compose' inputs at line '1' and column '6354': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document. 

 

I put it into an apply to each and then it gives the following error : 

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('XMLFile')?['body']' is of type 'String'. The result must be a valid array.

@Vstar19 

 

Step 1- Copy one note to compose action.

 

<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>User, Test</displayName>
<ID>123456</ID>
</data>

 

Step 2 - Add a compose action and paste the Formula below -

 

 xpath(xml(outputs('Compose')),'//*[local-name()="ID"]/text()')

 

Let me know if that works.

 

CFernandes_0-1618234234251.png

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Yes that works for a single value. 

If i enter the following data (i.e. multiple IDs) into the Compose 

 

<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>1User, Test</displayName>
<ID>123456</ID>
</data>
<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>2User, Test</displayName>
<ID>123456</ID>
</data>
<?xml version="1.0" standalone="yes"?>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>3User, Test</displayName>
<ID>123456</ID>
</data>

e.g.  where Compose is the code above . The Apply to Each  is showing the output from Compose above

xml.PNG

And i use the same formulae above, i get the below error : 

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Compose')' is of type 'String'. The result must be a valid array.

 

OK, so if i paste in some sample data from the origianal XML file into a compose, i have got this working as i want. 🙂 

 

The issue i have now is when i use  SharePoint connector, get file content and i change my For Each to read from the Get File Content SharePoint Action. I am thrown back with this error 

 

Unable to process template language expressions for action 'Apply_to_each' at line '1' and column '6356': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document. Consider specifying a DeserializeRootElementName. Path 'outputs.headers'.

 

When i click on the output of the SharePoint Get File content, i noticed that there is a header information. 

 

{"statusCode":200,"headers":{"Transfer-Encoding":"chunked","Vary":"Origin,Accept-Encoding","X-SharePointHealthScore":"3","X-MS-SPConnector":"1","X-SP-SERVERSTATE":"ReadOnly=0","DATASERVICEVERSION":"3.0","SPClientServiceRequestDuration":"99","SPRequestGuid":"95989bdd-db66-48f8-a058-f82ff5aef99d","request-id":"63909cdg-db66-65f2-a158-f82fe5aef29d","MS-CV":"3Z+Qh2bb+EGfWXgv5a7yvQ.7","Strict-Transport-Security":"max-age=31536000","X-FRAME-OPTIONS":"SAMEORIGIN","Content-Security-Policy":"frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com *.powerapps.com *.yammer.com *.officeapps.live.com *.office.com *.stream.azure-test.net *.microsoftstream.com;","MicrosoftSharePointTeamServices":"16.0.0.22151","X-Content-Type-Options":"nosniff","X-MS-InvokeApp":"1; RequireReadOnly","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Cache-Control":"max-age=0, private","Date":"Tue, 13 Apr 2021 08:36:35 BST","P3P":"CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Type":"text/xml","Expires":"Mon, 29 Mar 2021 08:36:36 GMT","Last-Modified":"Tue, 13 Apr 2021 08:36:36 GMT","Content-Length":"10590789"}

 

 

Could this be causing the issue?

 

 

Hey @Vstar19 ,,

 

Sorry Mate, was busy so could not get back to you.

 

Your XML

 

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<data>
<co>United States</co>
<department>Finance</department>
<division>Division Sample</division>
<displayName>User, Test</displayName>
<ID>123456</ID>
</data>
<data>
<co>United States</co>
<department>HR</department>
<division>Purchasing</division>
<displayName>Smith, John</displayName>
<ID>7891011</ID>
</data>
<data>
<co>United States</co>
<department>IT</department>
<division>Marketing</division>
<displayName>Bloggs, Joe</displayName>
<ID>13141516</ID>
</data>
</DocumentElement>

 

Add Compose - 

 

xpath(xml(outputs('Compose_2')),'//*[local-name()="ID"]/text()')
 
CFernandes_0-1618327869560.png

 

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Vstar19
Helper III
Helper III

Thank you @CFernandes . That worked

Hey Mate, 

 

I am glad I could provide you with a solution.

 

 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (17,586)