Reading XML data stored in SharePoint.

[Vstar19]

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

[CFernandes]

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()')

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]

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)

[CFernandes]

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]

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.

[CFernandes]

@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.

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]

Yes that works for a single value. 

[Vstar19]

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

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.

[Vstar19]

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?

[CFernandes · Accepted solution]

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()')

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]

Thank you @CFernandes . That worked

[CFernandes]

Hey Mate, 

I am glad I could provide you with a solution.