Parsing XML with wierd structure into JSON object
Hello:
I have been working on parsing this XML using the xpath() and xml() functions. Everything is working great except for this bit of XML structure, see the bottom. Looking for some advice.
Each 3 fields (Name, Type, Value) are a matched set (there are actually 36 of these, shorted in this message). I only care about the name and value. I am wanting to make a JSON look like the following:
{
"dcname": "Bottom Cleat",
"name": "Bottom Cleat",
"wallname": "Wall B"
}
I have this working, but it is very time expensive. What I did is use xpath on the label name and create an array using an incremental variable to run through the Apply to Each for each Name value. I then repeated the process for the xml value. I then stitched them together.
[
{
"LoopNo": 1,
"LabelName": "dcname"
},
{
"LoopNo": 2,
"LabelName": "name"
},
{
"LoopNo": 3,
"LabelName": "wallname"
}
]
The repeated the process for the “Value” field
[
{
"LoopNo": 1,
"LabelValue": " Bottom Cleat "
},
{
"LoopNo": 2,
" LabelValue ": " Bottom Cleat "
},
{
"LoopNo": 3,
" LabelValue ": " Wall B "
}
]
I then stitched them together by running through the name array and then filtering for the value array matching the LoopNo and using the addproperty() to created the following JSON
{
"dcname": "Bottom Cleat",
"name": "Bottom Cleat",
"wallname": "Wall B"
}
End Result Took 48 seconds to run, I have a potential of 100s of these to run = Hours of time.
Here is the source XML (shorted down from 36 rows)
<Label ID="IDL-0" JobID="IDJ-0" PartID="IDP-0">
<Name>dcname</Name>
<Type>text</Type>
<Value>Bottom Cleat</Value>
<Name>name</Name>
<Type>text</Type>
<Value>Bottom Cleat</Value>
<Name>wallname</Name>
<Type>text</Type>
<Value>Wall B</Value>
</Label>
Comments
-
Parsing XML with wierd structure into JSON object
I included a zipped flow of this process. Need to get to run faster. 1 hour for 86 (36 row XML) is way too slow. I cannot run concurrency because the increment integer variable because the way it is built I have to match the record location.
*This post is locked for comments