Convert MSSQL in to XML file

[pawelkon]

Hello,

I am working on project to query MS SQL and save as XML file, the XML file will be used by another software.
I have manage to complete first task, but vendor who will be using XML file asked me to add additional level into XML file.
So please see flow below:

Results are:

Expectations are:

Not sure where to start. I tried to use function node to add fixed line_item line but I don't know how to do that.

Thanks for all your help

Regards
Pawel

[TotallyInformation]

Stick a debug on the output of the SQL query so you can see what you get as an object. You need to split on each record.

[pawelkon]

Thanks for your answer.

I am getting array after sql qry. Please see below:

So what I have tried is, splitting after sql query and then XML node and then save to file.

So it split properly to each individual payload.

But the final output look like this as it saves in to the file just the last payload.

So I need to have all the payloads in 1 file with separate root container for each payload.

Any other suggestion?

[dceejay]

I often fine it easiest to start with the xml I want - then feed it through the xml node - that will generate the json structure I need - as the process is two way. You can then manipulate the output of your query into the correct json format before sending to xml node.

[TotallyInformation]

Hmm, not entirely sure how the xml node converts as I've not used it that way round. You might try moving the payload to payload.Line_Item and see if that works.

You can always build it manually of course if you need to. Use a function node to build the string.

[dceejay]

You probably just need to replace the split by something like this function

[{"id":"595cd2c3.26a77c","type":"function","z":"fb2d52d1.30eaa","name":"","func":"msg.payload = msg.payload.map(\n    function(x) {\n        return {list_item:x};\n    })\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":520,"wires":[["78dbd7cf.7c0e98","2d3842a1.12d8ae"]]}]

which just adds a layer to each element of the array.

[pawelkon]

Hi All,

I finally make it work taking different approach.
I generate SQL qry with XML output, the output was in array format, so I have added function to convert it to string and then save as xml file.

So, thanks to all for your support.
Best regards
Pawel