# Convert MSSQL in to XML file

**URL:** <https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464>\
**Category:** General\
**Created:** [26 November 2019 14:55 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464 "2019-11-26T14:55:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![pawelkon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pawelkon/32/87709_2.png) [@pawelkon](https://discourse.nodered.org/u/pawelkon)\
**Post date:** [26 November 2019 14:55 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/1 "2019-11-26T14:55:23Z")

</div>

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:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/a/ab736cfa6209fa1cce1c0112e1d2cbe885f405bf.png)

Results are:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/578ac08ad0b0be1509acc2ae88f2463c169db95d.png)

Expectations are:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/cdb7e0ba34ed5c2e7e5d00121fe0ea05da2e8390.png)

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [26 November 2019 22:51 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/2 "2019-11-26T22:51:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pawelkon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pawelkon/32/87709_2.png) [@pawelkon](https://discourse.nodered.org/u/pawelkon)\
**Post date:** [27 November 2019 07:49 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/3 "2019-11-27T07:49:41Z")

</div>

Thanks for your answer.

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/8827882062989896ba18ba2de4802d21328ece25.png)  
So what I have tried is, splitting after sql query and then XML node and then save to file.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/0fb79964bb3e9023759f3da0a6367c09b28db223.png)  
So it split properly to each individual payload.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/4da8a7e38c1c2c89c6f2b7c74bd716f9974a2a21.png)  
But the final output look like this as it saves in to the file just the last payload.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/7/717f0af83e1d77b761873ec826a23d36a242e537.png)  
So I need to have all the payloads in 1 file with separate root container for each payload.

Any other suggestion?

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [27 November 2019 09:03 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/4 "2019-11-27T09:03:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 November 2019 09:06 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/5 "2019-11-27T09:06:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [27 November 2019 09:15 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/6 "2019-11-27T09:15:10Z")

</div>

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

```auto
[{"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.

---

<div class="post-metadata">

**Author:** ![pawelkon](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pawelkon/32/87709_2.png) [@pawelkon](https://discourse.nodered.org/u/pawelkon)\
**Post date:** [27 November 2019 13:11 UTC](https://discourse.nodered.org/t/convert-mssql-in-to-xml-file/18464/7 "2019-11-27T13:11:14Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/35e896e484e515cf960be0ea72e5d137d5523680.png)

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