Hello,
I have two simple questions regarding Nifi and inserting a bunch of data
into MySQL.
My data is coming from xml sources and I need to extract then transform to
get the data I need and then load it into MySQL database.
Now I am facing two issues which are specifically how to go on about since
I am stuck and I am not sure of what to.
Once I have extracted data from the XML files, I need to convert the XML
data into JSON format using JOLT Spec. Now this is where the issue is.
My transnsformed XML file is in the following format.
<quote>
<documents>
<document><!-- there are multiple documents in one document -->
<tableOne>
<id>
<fieldOne></fieldOne>
<fieldTwo></filedTwo>
</tableOne>
<tableTwo>
<id></id>
<valueOne></valueOne>
<valueTwo></valueTwo>
</tableTwo>
<tableThree>
<id></id>
<itemOne></itemOne>
<itemTwo></itemTwo
</tableThree>
</shop>
</document>
</documents>
</quote>
Currently, what I do is to split the above XML document at 1 and hence I
remain with element <document> then I have an XSLT transformer which
convert the <document> element into JSON document.
Working with JSON is whwere my issue starts because, I am not sure if I
understand and I am confident my solution is good although it is
recommended.
Since, I need to do multiple tables inserts then how can I go about?
Sample JSON:
<quote>
{
"document": {
"tableOne": {
"id": "id1",
"fieldOne": "fieldOne",
"fieldTwo": "fieldTwo"
},
"tableTwo": {
"id": "id2",
"valueOne": "valueOne",
"valueTwo": "valueTwo"
},
"tableThree": {
"id": "id2",
"itemOne": "valueOne",
"itemTwo": "valueTwo"
}
}
}
</quote>
Initially, when I was testing with one element it worked quite easily
because with one table I could just split the JSON into several small
chunks then call JSONtoSQL Processor, then ExecuteDatabase and that worked
out of the box.
JSON path extract:
$.document[:1].tableOne
How can I solve this issue of multiple inserts..?
Is this the right approach..?
Thanks for the advice in advance.
