Multiple Database Inserts and Strategy.

[Noel Alex Makumuli]

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.