Hi all,
I've a flow where I'm trying to convert some XML data from an old system to JSON. That because I want to use the dynamic data later in my flow.
But I'm unable to convert the XML into a usable JSON. The dynamic data is empty.
For example, I build the follow flow:
XML data:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="https://start.exactonline.nl/api/v1/2483029/financialtransaction/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
<title type="text">TransactionLines</title>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines</id>
<updated>2021-05-18T19:14:25Z</updated>
<link rel="self" title="TransactionLines" href="TransactionLines" />
<entry>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines(guid'8d7c9014-9092-4108-b8c5-0012c8710676')</id>
<title type="text"></title>
<updated>2021-05-18T19:14:25Z</updated>
<author>
<name />
</author>
<link rel="edit" title="TransactionLine" href="TransactionLines(guid'8d7c9014-9092-4108-b8c5-0012c8710676')" />
<category term="Exact.Web.Api.Models.Financial.TransactionLine" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ID m:type="Edm.Guid">8d7c9014-9092-4108-b8c5-0012c8710676</d:ID>
<d:Account m:type="Edm.Guid">2a0495a1-bad4-4745-a42a-962fe175d48f</d:Account>
<d:AccountCode xml:space="preserve"> 22</d:AccountCode>
<d:AccountName>Business1</d:AccountName>
</m:properties>
</content>
</entry>
<entry>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines(guid'd300672e-8ce8-49e1-99d5-003bbe4bb842')</id>
<title type="text"></title>
<updated>2021-05-18T19:14:25Z</updated>
<author>
<name />
</author>
<link rel="edit" title="TransactionLine" href="TransactionLines(guid'd300672e-8ce8-49e1-99d5-003bbe4bb842')" />
<category term="Exact.Web.Api.Models.Financial.TransactionLine" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ID m:type="Edm.Guid">d300672e-8ce8-49e1-99d5-003bbe4bb842</d:ID>
<d:Account m:type="Edm.Guid">88ab1d4e-0e5a-45c5-bafd-66833d2d14f0</d:Account>
<d:AccountCode xml:space="preserve"> 17</d:AccountCode>
<d:AccountName>Business</d:AccountName>
</m:properties>
</content>
</entry>
<entry>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines(guid'75cdde9d-3440-43ad-957f-00430c57a488')</id>
<title type="text"></title>
<updated>2021-05-18T19:14:25Z</updated>
<author>
<name />
</author>
<link rel="edit" title="TransactionLine" href="TransactionLines(guid'75cdde9d-3440-43ad-957f-00430c57a488')" />
<category term="Exact.Web.Api.Models.Financial.TransactionLine" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ID m:type="Edm.Guid">75cdde9d-3440-43ad-957f-00430c57a488</d:ID>
<d:Account m:type="Edm.Guid">54fad0b9-36f6-4231-8093-ce2f8aae43af</d:Account>
<d:AccountCode xml:space="preserve"> 24</d:AccountCode>
<d:AccountName>Business3</d:AccountName>
</m:properties>
</content>
</entry>
<entry>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines(guid'31ea68e6-82e0-465e-ab18-0073be1188e3')</id>
<title type="text"></title>
<updated>2021-05-18T19:14:25Z</updated>
<author>
<name />
</author>
<link rel="edit" title="TransactionLine" href="TransactionLines(guid'31ea68e6-82e0-465e-ab18-0073be1188e3')" />
<category term="Exact.Web.Api.Models.Financial.TransactionLine" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ID m:type="Edm.Guid">31ea68e6-82e0-465e-ab18-0073be1188e3</d:ID>
<d:Account m:type="Edm.Guid" m:null="true" />
<d:AccountCode m:null="true" />
<d:AccountName m:null="true" />
</m:properties>
</content>
</entry>
<entry>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines(guid'a2f26664-f7b0-4f84-b15e-0082a2622211')</id>
<title type="text"></title>
<updated>2021-05-18T19:14:25Z</updated>
<author>
<name />
</author>
<link rel="edit" title="TransactionLine" href="TransactionLines(guid'a2f26664-f7b0-4f84-b15e-0082a2622211')" />
<category term="Exact.Web.Api.Models.Financial.TransactionLine" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ID m:type="Edm.Guid">a2f26664-f7b0-4f84-b15e-0082a2622211</d:ID>
<d:Account m:type="Edm.Guid">54fad0b9-36f6-4231-8093-ce2f8aae43af</d:Account>
<d:AccountCode xml:space="preserve"> 24</d:AccountCode>
<d:AccountName>Business4</d:AccountName>
</m:properties>
</content>
</entry>
<entry>
<id>https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines(guid'ac125d7f-d6c7-4720-9453-0091bb5e8ac0')</id>
<title type="text"></title>
<updated>2021-05-18T19:14:25Z</updated>
<author>
<name />
</author>
<link rel="edit" title="TransactionLine" href="TransactionLines(guid'ac125d7f-d6c7-4720-9453-0091bb5e8ac0')" />
<category term="Exact.Web.Api.Models.Financial.TransactionLine" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ID m:type="Edm.Guid">ac125d7f-d6c7-4720-9453-0091bb5e8ac0</d:ID>
<d:Account m:type="Edm.Guid">4343daa3-ce6b-4efc-b1fd-cfc8f6c2144e</d:Account>
<d:AccountCode xml:space="preserve"> 19</d:AccountCode>
<d:AccountName>Business5</d:AccountName>
</m:properties>
</content>
</entry>
<link rel="next" href="https://start.exactonline.nl/api/v1/2483029/financialtransaction/TransactionLines?$select=ID,Account,AccountCode,AccountName&$skiptoken=guid'2dac7e64-eb0a-4953-856c-09826d44acb9'" />
</feed>
Other than changing the output from the original application that generates the XML the only suggestion I can make is to keep trying with the search and replace. You'll probably need to replace more than the m:null = "true" to keep the XML valid. You'll probably have to replace the whole XML element.
Hi @Pstork1 ,
Thank you for your reply.
I've try to do the job with xpath, but that gives me not any result. (I used below xpath):
xpath(
xml(
outputs('XML_Data')
),
'//AccountName'
)
The other suggestion, search and replace, give me some more hope.
Now I have:
replace(outputs('XML_data'),'m:null = "true"','')
But when I try to convert it to JSON it's ending with the error:
(Change the variables to objects is not helping.)
Any idea how to fix this?
I hope when I fix this last part, I'm able to use the dynamic content of the different values (for example: the 'd:AccountName' values.)
Thanks in advance.
Since the XML starts out as a string you should be able to do a search and replace on the specific values if you can identify them. I'm not an expert, but you may also be able to fix them in the XML using XPath. Both of those approaches should be possible inside the flow or manually before the flow starts. The key is to search for values where m:null = "true"
Hi @Pstork1 ,
Thank you for your reply.
Is there anyway how to I can do that in Power Automate?
Or is it a manual job to clean it up?
The problem is that some of the Account names have XML structures that don't convert. Look for entries like this
<d:AccountName m:null="true" />
That converts to an object in JSON, not a null value. You'll need to find those occurences in the XML and clean them up before converting it to JSON.
Tomac
986
Moderator
stampcoin
699
Super User 2025 Season 2
Riyaz_riz11
577
Super User 2025 Season 2