json - Generating XML with SSIS - What are best practices

Question

So I'm trying to generate an XML from data that I receive in a JSON file.
What I've done so far is that I had stored each field in the JSON as a keypair. So a row would be FieldName, FieldValue, and JSON_PK.
I have a second table that I created in order to create the XML. It has the JSON FieldName, equivalent XML FieldName, and indentation. The plan was to create a loop in SSIS to manually create the XML.

It was suggested to be that I use instead the FOR XML in my query.
However I've run an issue that every field is named FieldName. It's complicated by fields that hold their values like this <Form submittedDate="2020-01-01"/>

So before I go back to creating a loop to create my XML, I'm wondering what are best practices? I can't be the first one to run into this issue.
Thanks!


A quick followup because it was requested:
This is the approximate for that the JSON comes in as, except is far longer:
{
"name": "Mylist",
"id": "9e8-19c9-e5",
"templateName": "VDashboard - Plus",
"categories": [
""
],
"attributes": [
{
"name": "Division ID",
"value": "ABCD",
"Id": "123",
"units": null,
"timestamp": "1970-01-01T00:00:00.0000000Z",
"errors": null
},
{
"name": "ETA ",
"value": null,
"Id": "123",
"units": null,
"timestamp": "2021-01-25T21:24:36.2514056Z",
"errors": null
},
{
"name": "ETA Destination - Estimated Time",
"value": "1/11/2021 4:15:34 PM",
"Id": "123",
"units": null,
"timestamp": "1970-01-01T00:00:00.0000000Z",
"errors": null
}
]
}


And I need to output it as an XML File.
I need to import it into the DB because I do transformation of certain fields.
Output should look at bit like this:

2020-12-03T08:00:00-05:00
0011

My table structure looks like this. It's done so that I won't have a different table for every report:
Name VARCHAR(4) NOT NULL,
ID VARCHAR(50),
TemplateName VARCHAR(50),
AttributeName VARCHAR(50),
AttributeSubName VARCHAR(50),
AttributeValue VARCHAR(50),
AttributeID VARCHAR(50),
AttributeUnits VARCHAR(50),
AttributeTimestamp DateTime,
AttributeErrors VARCHAR(50),

question from:https://stackoverflow.com/questions/65921187/generating-xml-with-ssis-what-are-best-practices

Answer 1

Waitting for answers