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

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
Super User

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 V
Helper V

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!

Vstar19
Helper V
Helper V

Thank you @CFernandes . That worked

Hey Mate, 

 

I am glad I could provide you with a solution.

 

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,240)