Related about XML

[traineeTKH]

Hi everyone, I would like to ask

1.) How to change the XML format like “root” become “Staff”.
2.) How to change the inside value like “Kelvin” become “ken”.
3.) How to group the “year”, “month” and “day” together using “current” name like second pictures.

My expected output is like third pictures.

I’m using node red to retrieve the mssql database data and convert to xml format. I’m tried to search google but did not get the results.

If anyone know please guide me.
If got any related links, please share to me, TQVM.

[philip]

You can use the Change node to find and replace <root> and </root>. If that is the only XML node name you need to change, that would be simplest, as long as you don’t have any edge cases where <root> is expected somewhere else.

You can then pass that into the XML node to turn it into a javascript object and modify the values as you see fit, then pass it back to the XML node to turn it back into XML.

[torchard]

Great answer philip, thanks!

You can also use that first method to search for “Kelvin” and replace with “Ken” so that only one staff name is changed, and not all of them.

Automatically grouping the year, month, and day under one “Current” object is a little bit more difficult – you will need to insert <Currently> before the string <year> and insert </Currently> after every </day>.
Here is a flow that should do the trick:

[{"id":"8c8578e2.d23478","type":"function","z":"e02f10b4.235de","name":"insert <Current>","func":"/* Program to add <Current> before every <year> and </Current> after every </day> in an XML string: */\n// loop through all the data, which is the msg.payload string:\nvar data = msg.payload;\nfor(i = 0; i < data.length; i++) {\n // make note of where the date information starts with \"<year>\":\n year = data.indexOf(\"<year>\", i);\n // if this is not found, break out of the loop:\n if (year == -1) break;\n // insert \"<Current>\" BEFORE the \"<year>\" tag:\n data = data.slice(0, year) + \"<Current>\" + data.slice(year, data.length);\n \n // make note of where the date information ends with \"</day>\" + 6 offset:\n day = data.indexOf(\"</day>\", i);\n // if this is not found, break out of the loop:\n if (day == -1) break;\n // insert \"</Current>\" AFTER the \"</day>\" tag:\n data = data.slice(0, day+6) + \"</Current>\" + data.slice(day+6, data.length); \n // start looking for the next date *after* this one has ended:\n i = day+6;\n}\n// return the new data:\nreturn { payload : data };","outputs":1,"noerr":0,"x":340,"y":1140,"wires":[["1016b060.1559d"]]},{"id":"89f0e88b.3509f8","type":"inject","z":"e02f10b4.235de","name":"","topic":"","payload":"<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?> <root> <ID>1</ID> <Name>Kelvin</Name> <Location>New York</Location> <year>2019</year> <month>11</month> <day>01</day> <ID>2</ID> <Name>William</Name> <Location>London</Location> <year>2019</year> <month>02</month> <day>02</day> <ID>3</ID> <Name>Bryon</Name> <Location>New Zeland</Location> <year>2019</year> <month>23</month> <day>03</day> </root>","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":1100,"wires":[["8c8578e2.d23478","fd2fc60e.3e98a8"]]},{"id":"1016b060.1559d","type":"change","z":"e02f10b4.235de","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"root","fromt":"str","to":"Staff","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"Kelvin","fromt":"str","to":"Ken","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":1140,"wires":[["3be05016.6a7c4"]]},{"id":"3be05016.6a7c4","type":"xml","z":"e02f10b4.235de","name":"","property":"payload","attr":"","chr":"","x":670,"y":1140,"wires":[["1190c675.bad6ca"]]},{"id":"1190c675.bad6ca","type":"debug","z":"e02f10b4.235de","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":810,"y":1140,"wires":[]},{"id":"fd2fc60e.3e98a8","type":"debug","z":"e02f10b4.235de","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":330,"y":1100,"wires":[]}]

[traineeTKH]

Thanks a lot philip and torchard. You saved my day.

Now I got another questions which is how to write a function for looping?

Example: Every 5 minutes I can get the current date and time using node red because I would like to make my project loop infinite.

I know got one function is “looptimer” but cannot do infinite.

If anyone know please guide me.TQVM.
If I late reply I’m so sorry.

[Beno]

I am not 100% sure of what you are asking, so if I have this wrong, just keep asking questions and we will try and get you sorted…

You can do a task in Node-RED forever by telling the inject node how often to inject.
You mention 5 minutes, so here is how you would set that up;

This will repeat infinitely.

[traineeTKH]

Sorry for my poor English. I want to write a function is every 5 minute can get the current date and time.

Example: If the current time is 16:55:00, after 5 minute I would like to updated become 17:00:00.

Thanks for your repeat infinitely.

[Beno]

Put the follow in a function block that you call every 1 second;
The code should get you started, I don’t think it will work exactly as you want, but feel free to start here and tinker.
Lets know how you get on.

flowVar = 'lastMin';        // context variable name for the last minute

d = new Date();
min = d.getMinutes();           // current minute

if(min % 5 === 0) {            // if 0, 5, 10, 15 etc
    last = context.get('last')||0;     // last minute for RBE
    
    if(min != last) {           // if this minute is new
        context.set('last', min);
        return { payload : min };
    }
    else return null;           // block repeat minutes
}
else return null;               // block non 0, 15, 30, 45

[traineeTKH]

HI, If I got one function called (return msg) and I want updated using edit change node called (change min) how to apply?

Because now my output is “msg” not “35”.
I want my output is 35…

[Beno]

My best guess is…

Change your function node to this;
msg.min = 35;
return msg;

Then change your change node to set;
msg.payload[0].minute
to
msg.min

(NOTE. Be sure and select msg. from the drop down list, DONT just type this in to the ‘az’ or you will just have a message that contains ‘msg.min’.)

Right now you are changing your payload[0].minute to a literal when you want a variable.

[traineeTKH]

Thanks Beno.

I have some problems which is why I just can display what I change only?
I would like to display all the data like data 1 to 4 not only date and time.

How to set the month ‘4’ become ‘04’ also using function to wrote?

I tried to change the output become msg.payload or complete msg object also cannot.

[torchard]

You are only seeing the data you change because you are overwriting the rest of the data, you need to move all the data into new message properties if you want to save it.

The data in msg.payload.data1 gets overwritten when you set msg.payload[0].minute because adding [0] makes a very big change. This converts the structure of payload from an object into a list or “array”, which is created and referenced using [ ]. These two types, arrays and objects, are structured differently and so changing payload from one type to the other must overwrite the old payload.

To avoid losing the rest of your data you could set msg.payload[1] to msg.payload.data1, for example, or you can also set msg.backup to msg.payload to back it up before you set msg.payload[0] and it will save it on that msg.backup property that will be unaffected.

Regarding the difference between 4 and "04", note that 4 is blue without quotes and "04" is red with quotes, because of those quotes 4 is a number and "04" is a string. 04 and 4 are equal to each other as numbers, so the simpler form 4 is used. So, in this case, if you want a leading zero like "04" you must use strings to represent your number.

Can you describe the data you have? It could be complicated to process because data3 and data4 are twice the length of data1 and data2, is there a reason for this? And is the format consistent?

[traineeTKH]

This is the correct output.

The data4 is current date and time, data3 is minus 5 minute data2 and data1 is minus the current time 10 minute.

This is the function node.

Can you demo the month is “04” not “4”. TQVM

[traineeTKH]

My flow is retrieve the data from database , then set the date time function in data 1 to 4, convert the data to XML Format.

Hi I want to ask about :
1.) How to change the date and time and did not affect logging_type, vender, station_ref?
2.) Have any format about the month is “04”? I want change the number “4” to “04”.

The output is like this.

[{“id”:“f9f3aae3.a397b8”,“type”:“inject”,“z”:“e05f74c5.5fadc8”,“name”:"",“topic”:"",“payload”:"",“payloadType”:“date”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:131,“y”:97,“wires”:[[“e0adb5a9.6dbdc8”]]},{“id”:“cdf930b9.03214”,“type”:“debug”,“z”:“e05f74c5.5fadc8”,“name”:"",“active”:true,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“payload”,“x”:530,“y”:100,“wires”:[]},{“id”:“e0adb5a9.6dbdc8”,“type”:“function”,“z”:“e05f74c5.5fadc8”,“name”:“return msg”,“func”:“d = new Date();\nhour = d.getHours();\nminute = d.getMinutes(); \nyear = d.getFullYear();\nmonth = d.getMonth() +1;\nday = d.getDate();\n\n//if(minute % 5 === 0) { \n last = context.get(‘last’)||0; \n \n// if(minute != last) { \n context.set(‘last’, minute);\n \n if(minute === 15||20||25||30||35||40||45||50||55){\n minuteFive = minute-5;\n minuteTen = minute-10;\n minuteFifteen = minute-15;\n }\n if(minute === 10){\n minuteFive = minute -5;\n minuteTen = “00”;\n minuteFifteen = “00”;\n }\n if (minute ===5){\n minuteFive = “00”;\n minuteTen = “00”;\n minuteFifteen = “00”;\n }\n if (minute === 0){\n minuteFive = minute;\n minuteTen = minute;\n minuteFifteen = minute;\n }\n// }\n// else return null; \n//}\n//else return null; \n\nvar msg = {\n h : hour.toString(),\n min : minute.toString(),\n s : ‘00’,\n y : year.toString(),\n m : month.toString(),\n d : day.toString(),\n data1 : year +”-"+“0”+month+"-"+day+","+hour+":"+minuteFifteen+":"+‘00’+",0.9999995,3.48,0,2.9,3.7,1.4",\n data2 : year +"-"+“0”+month+"-"+day+","+hour+":"+minuteTen+":"+‘00’+",0.9999995,3.48,0,2.9,3.7,1.4",\n data3 : year +"-"+“0”+month+"-"+day+","+hour+":"+minuteFive+":"+‘00’+",0.9999995,3.48,0,2.9,3.7,1.4",\n data4 : year +"-"+“0”+month+"-"+day+","+hour+":"+minute+":"+‘00’+",0.9999995,3.48,0,2.9,3.7,1.4",\n}\n\nreturn msg;\n",“outputs”:1,“noerr”:0,“x”:207,“y”:176,“wires”:[[“b515bd30.cd47a”]]},{“id”:“b515bd30.cd47a”,“type”:“change”,“z”:“e05f74c5.5fadc8”,“name”:“change min”,“rules”:[{“t”:“set”,“p”:“payload[0].logging_type”,“pt”:“msg”,“to”:“logging_type”,“tot”:“str”},{“t”:“set”,“p”:“payload[0].vender”,“pt”:“msg”,“to”:“vender”,“tot”:“str”},{“t”:“set”,“p”:“payload[0].station_ref”,“pt”:“msg”,“to”:“station_ref”,“tot”:“str”},{“t”:“set”,“p”:“payload[0].year”,“pt”:“msg”,“to”:“y”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].month”,“pt”:“msg”,“to”:“m”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].day”,“pt”:“msg”,“to”:“d”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].hour”,“pt”:“msg”,“to”:“h”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].minute”,“pt”:“msg”,“to”:“min”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].second”,“pt”:“msg”,“to”:“s”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].total_data”,“pt”:“msg”,“to”:“4”,“tot”:“str”},{“t”:“set”,“p”:“payload[0].data1”,“pt”:“msg”,“to”:“data1”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].data2”,“pt”:“msg”,“to”:“data2”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].data3”,“pt”:“msg”,“to”:“data3”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].data4”,“pt”:“msg”,“to”:“data4”,“tot”:“msg”},{“t”:“set”,“p”:“payload[0].message”,“pt”:“msg”,“to”:“Complete”,“tot”:“msg”}],“action”:"",“property”:"",“from”:"",“to”:"",“reg”:false,“x”:325,“y”:102,“wires”:[[“1a5014a0.b61dcb”]]},{“id”:“1a5014a0.b61dcb”,“type”:“xml”,“z”:“e05f74c5.5fadc8”,“name”:"",“property”:“payload”,“attr”:"",“chr”:"",“x”:439,“y”:183,“wires”:[[“cdf930b9.03214”]]},{“id”:“4bef6242.a53bac”,“type”:“inject”,“z”:“e05f74c5.5fadc8”,“name”:“xml string”,“topic”:"",“payload”:“payload”,“payloadType”:“str”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:132,“y”:248,“wires”:[[“505b8806.f322e8”]]},{“id”:“505b8806.f322e8”,“type”:“change”,“z”:“e05f74c5.5fadc8”,“name”:“Trend Format”,“rules”:[{“t”:“set”,“p”:“payload”,“pt”:“msg”,“to”:"{“LOGGING”:{“logging_type”:[“logging_type”],“vender”:[“vender”],“station_ref”:[“station_ref”],“send_date”:{“year”:[“2019”],“month”:[“04”],“day”:[“04”]},“send_time”:{“hour”:[“16”],“minute”:[“55”],“second”:[“00”]},“total_data”:[“4”],“logging”:{“data1”:[“2019-04-04,16:40:00,0.9999995,3.48,0,2.9,3.7,1.4”],“data2”:[“2019-04-04,16:45:00,0.9999995,3.48,0,2.9,3.7,1.4”],“data3”:[“2019-04-04,16:50:00,0.9999995,3.48,0,2.9,3.7,1.4”],“data4”:[“2019-04-04,16:55:00,0.9999995,3.48,0,2.9,3.7,1.4”]},“status”:[“complete”]}}",“tot”:“json”}],“action”:"",“property”:"",“from”:"",“to”:"",“reg”:false,“x”:305,“y”:247,“wires”:[[“3d1d1e4e.16d662”]]},{“id”:“3d1d1e4e.16d662”,“type”:“xml”,“z”:“e05f74c5.5fadc8”,“name”:"",“property”:“payload”,“attr”:"",“chr”:"",“x”:463,“y”:247,“wires”:[[“6d55206b.04071”]]},{“id”:“6d55206b.04071”,“type”:“debug”,“z”:“e05f74c5.5fadc8”,“name”:"",“active”:true,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“false”,“x”:610,“y”:247,“wires”:[]}]

[Beno]

Thanks for posting your code, unfortunately we can NOT use it since it is the wrong format.

You MUST put you code in the Preformatted text form.

To do this, click on the </> button and then past your exported flow between the two ticks.

[traineeTKH]

`[{"id":"f47194e4.69d0f8","type":"inject","z":"b166da4.4f2dc28","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":125,"y":117,"wires":[["94cadb7.9334d28"]]},{"id":"70ab6deb.0342e4","type":"debug","z":"b166da4.4f2dc28","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":649,"y":182,"wires":[]},{"id":"3cb35f71.9714b","type":"change","z":"b166da4.4f2dc28","name":"change","rules":[{"t":"set","p":"payload[0].logging_type","pt":"msg","to":"logging_type","tot":"str"},{"t":"set","p":"payload[0].vender","pt":"msg","to":"vender","tot":"str"},{"t":"set","p":"payload[0].station_ref","pt":"msg","to":"station_ref","tot":"str"},{"t":"set","p":"payload[0].station_id","pt":"msg","to":"station_id","tot":"str"},{"t":"set","p":"payload[0].station_name","pt":"msg","to":"station_name","tot":"str"},{"t":"set","p":"payload[0].year","pt":"msg","to":"y","tot":"msg"},{"t":"set","p":"payload[0].month","pt":"msg","to":"m","tot":"msg"},{"t":"set","p":"payload[0].day","pt":"msg","to":"d","tot":"msg"},{"t":"set","p":"payload[0].hour","pt":"msg","to":"h","tot":"msg"},{"t":"set","p":"payload[0].minute","pt":"msg","to":"min","tot":"msg"},{"t":"set","p":"payload[0].second","pt":"msg","to":"s","tot":"msg"},{"t":"set","p":"payload[0].total_data","pt":"msg","to":"4","tot":"str"},{"t":"set","p":"payload[0].data1","pt":"msg","to":"data1","tot":"msg"},{"t":"set","p":"payload[0].data2","pt":"msg","to":"data2","tot":"msg"},{"t":"set","p":"payload[0].data3","pt":"msg","to":"data3","tot":"msg"},{"t":"set","p":"payload[0].data4","pt":"msg","to":"data4","tot":"msg"},{"t":"set","p":"payload[0].message","pt":"msg","to":"Complete","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":442,"y":106,"wires":[["5621ca35.1d9b84"]]},{"id":"5621ca35.1d9b84","type":"xml","z":"b166da4.4f2dc28","name":"","property":"payload","attr":"","chr":"","x":426,"y":149,"wires":[["9bd6e5e2.ecb508"]]},{"id":"9bd6e5e2.ecb508","type":"change","z":"b166da4.4f2dc28","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"<0>","fromt":"str","to":"<LOGGING>","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"</0>","fromt":"str","to":"</LOGGING>","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":405,"y":192,"wires":[["64777c6f.6d80f4"]]},{"id":"64777c6f.6d80f4","type":"function","z":"b166da4.4f2dc28","name":"insert <send_date><send_time>","func":"/* Program to add <Current> before every <year> and </Current> after every </day> in an XML string: */\n// loop through all the data, which is the msg.payload string:\nvar data = msg.payload;\nfor(i = 0; i < data.length; i++) {\n // make note of where the date information starts with \"<year>\":\n year = data.indexOf(\"<year>\", i);\n // if this is not found, break out of the loop:\n if (year == -1) break;\n // insert \"<Current>\" BEFORE the \"<year>\" tag:\n data = data.slice(0,year) + \"<send_date>\" + data.slice(year, data.length);\n \n // make note of where the date information ends with \"</day>\" + 6 offset:\n day = data.indexOf(\"</day>\", i);\n // if this is not found, break out of the loop:\n if (day == -1) break;\n // insert \"</Current>\" AFTER the \"</day>\" tag:\n data = data.slice(0,day+6) + \"</send_date>\" + data.slice(day+6, data.length); \n // start looking for the next date *after* this one has ended:\n i = day+6;\n \n hour = data.indexOf(\"<hour>\", i);\n // if this is not found, break out of the loop:\n if (hour == -1) break;\n // insert \"<Current>\" BEFORE the \"<year>\" tag:\n data = data.slice(0,hour) + \"<send_time>\" + data.slice(hour, data.length);\n \n // make note of where the date information ends with \"</day>\" + 6 offset:\n second = data.indexOf(\"</second>\", i);\n // if this is not found, break out of the loop:\n if (second == -1) break;\n // insert \"</Current>\" AFTER the \"</day>\" tag:\n data = data.slice(0,second+9) + \"</send_time>\" + data.slice(second+9, data.length); \n // start looking for the next date *after* this one has ended:\n i = second+9;\n \n data1 = data.indexOf(\"<data1>\", i);\n // if this is not found, break out of the loop:\n if (data1 == -1) break;\n // insert \"<Current>\" BEFORE the \"<year>\" tag:\n data = data.slice(0,data1) + \"<logging>\" + data.slice(data1, data.length);\n \n // make note of where the date information ends with \"</day>\" + 6 offset:\n data4 = data.indexOf(\"</data4>\", i);\n // if this is not found, break out of the loop:\n if (data4 == -1) break;\n // insert \"</Current>\" AFTER the \"</day>\" tag:\n data = data.slice(0,data4+8) + \"</logging>\" + data.slice(data4+8, data.length); \n // start looking for the next date *after* this one has ended:\n i = data4+8;\n \n \n message = data.indexOf(\"<message>\", i);\n // if this is not found, break out of the loop:\n if (message == -1) break;\n // insert \"<Current>\" BEFORE the \"<year>\" tag:\n data = data.slice(0,message) + \"<status>\" + data.slice(message, data.length);\n \n \n // make note of where the date information ends with \"</day>\" + 6 offset:\n message = data.indexOf(\"</message>\", i);\n // if this is not found, break out of the loop:\n if (message == -1) break;\n // insert \"</Current>\" AFTER the \"</day>\" tag:\n data = data.slice(0,message+10) + \"</status>\" + data.slice(message+10, data.length); \n // start looking for the next date *after* this one has ended:\n i = message+10;\n}\n// return the new data:\nreturn { payload : data };","outputs":1,"noerr":0,"x":401,"y":233,"wires":[["70ab6deb.0342e4"]]},{"id":"b675f835.e0c628","type":"MSSQL","z":"b166da4.4f2dc28","mssqlCN":"24efa47f.17bfec","name":"trend_data","query":"","outField":"payload","x":113,"y":206,"wires":[["ba1fc60a.937858"]]},{"id":"94cadb7.9334d28","type":"change","z":"b166da4.4f2dc28","name":"select from database","rules":[{"t":"set","p":"payload","pt":"msg","to":"SELECT[logging_type],[vender_id],[vender],[station_ref],[station_id],[station_name],[year],[month],[day],[hour],[minute],[second],[total_data],[data1],[data2],[data3],[data4],[message]FROM[trainee].[dbo].[trend]","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":143,"y":159,"wires":[["b675f835.e0c628"]]},{"id":"ba1fc60a.937858","type":"function","z":"b166da4.4f2dc28","name":"Function","func":"d = new Date();\nhour = d.getHours();\nminute = d.getMinutes();           // current minute\nyear = d.getFullYear();\nmonth = d.getMonth() +1;\nday = d.getDate();\n\nif(minute % 5 === 0) {            // if 0, 5, 10, 15 etc\n    last = context.get('last')||0;     // last minute for RBE\n    \n    if(minute != last) {           // if this minute is new\n        context.set('last', minute);\n        \n        \n        if(minute === 15||20||25||30||35||40||45||50||55){\n            minuteFive = minute-5;\n            minuteTen = minute-10;\n            minuteFifteen = minute-15;\n        } \n        if(minute === 10){\n            minuteFive = \"0\"+minute -5;\n            minuteTen = \"00\";\n            minuteFifteen = \"00\";\n        } \n        if (minute ===5){\n            minuteFive = \"00\";\n            minuteTen = \"00\";\n            minuteFifteen = \"00\";\n        }\n        if(minute ===0){\n            minuteFive = minute;\n            minuteTen = minute;\n            minuteFifteen = minute;\n        }\n        \n    }\n    else return null;           // block repeat minutes\n}\nelse return null;               // block non 0, 15, 30, 45\n\n\nvar msg = {\n    h : hour.toString(),\n    min : minute.toString(),\n    s : '00',\n    y : year.toString(),\n    m : '0'+month.toString(),\n    d : day.toString(),\n    data1 : year +\"-\"+\"0\"+month+\"-\"+day+\",\"+hour+\":\"+minuteFifteen+\":\"+'00'+\",0.9999995,3.48,0,2.9,3.7,1.4,90000,90000,93110,90000,93300,90000,-0.92,-0.92,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,90000,90000,90000\",\n    data2 : year +\"-\"+\"0\"+month+\"-\"+day+\",\"+hour+\":\"+minuteTen+\":\"+'00'+\",0.9999995,3.48,0,2.9,3.7,1.4,90000,90000,93110,90000,93300,90000,-0.92,-0.92,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,90000,90000,90000\",\n    data3 : year +\"-\"+\"0\"+month+\"-\"+day+\",\"+hour+\":\"+minuteFive+\":\"+'00'+\",0.9999995,3.48,0,2.9,3.7,1.4,90000,90000,93110,90000,93300,90000,-0.92,-0.92,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,90000,90000,90000\",\n    data4 : year +\"-\"+\"0\"+month+\"-\"+day+\",\"+hour+\":\"+minute+\":\"+'00'+\",0.9999995,3.48,0,2.9,3.7,1.4,90000,90000,93110,90000,93300,90000,-0.92,-0.92,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,90000,90000,90000\",\n}\n\nreturn msg;\n\n//min=35;\n//mins = 35 -5;\n\n//msg.send= min +\"/\"+mins+\"/\"+ \"32134\";\n//return msg;","outputs":1,"noerr":0,"x":114,"y":248,"wires":[["3cb35f71.9714b"]]},{"id":"24efa47f.17bfec","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"trend","server":"192.168.1.102","port":"1433","encyption":true,"database":"trainee","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]`

HI, please ignore the previous what I’m asking. (because I’m confusing already)
This flow have some problems about :

1.) The month how to change to “04” because I’m hardcode and the minute for 5 how to change become “05”? Because the minute I want store in the MSSQL database.

2.) How to convert the data to XML. I think is somethings wrong with payload. The code as below :

`[{"id":"e3b0310c.77154","type":"debug","z":"b166da4.4f2dc28","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":664,"y":588,"wires":[]},{"id":"42863197.15903","type":"MSSQL","z":"b166da4.4f2dc28","mssqlCN":"24efa47f.17bfec","name":"TableTrendData","query":"with a as( SELECT TOP 4 * FROM [trainee].[dbo].[TableTrendData] ORDER BY ID DESC )  select * from a ORDER BY ID ASC","outField":"payload","x":260,"y":588,"wires":[["d22233aa.d0528"]]},{"id":"45f53bca.40e274","type":"inject","z":"b166da4.4f2dc28","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":101,"y":587,"wires":[["42863197.15903"]]},{"id":"d22233aa.d0528","type":"function","z":"b166da4.4f2dc28","name":"","func":"a = msg.payload[0]\nb = msg.payload[1]\nc = msg.payload[2]\nd = msg.payload[3]\n\nvar msg = {\n\"LOGGING\": {\n\"logging_type\":\"Trend\",\n\"vender_id\": \"Dave\",\n\"vender\":\"Reminder\",\n\"station_ref\":\"Update the website\",\n\"station_id\":\"Update the website\",\n\"station_name\":\"Update the website\",\n\"send_date\": {\n\"year\": d.Year,\n\"month\":d.Month,\n\"day\":  d.Day\n},\n\"send_time\": {\n\"hour\": d.Hour,\n\"minute\":d.Minute,\n\"secound\":d.Second\n},\n\"total_data\":4,\n\"logging\": {\ndata1 : a.Year +\"-\"+ a.Month +\"-\"+ a.Day +\",\"+ a.Hour+\":\"+a.Minute+\":\"+a.Second+\",\"+a.Upstream+\",\"+a.Downstream+\",\"+a.Tidal+\",\"+a.Danger_Set_Point+\",\"+a.Alert_Set_Point+\",\"+a.Control_Level_Set_Point+\",\"+a.Gate_ConditionOnetoFour+\",\"+a.Gate_ConditionFivetoEight+\",\"+a.Gate_ModeOnetoFour+\",\"+a.Gate_ModeFivetoEight+\",\"+a.Gate_StatusOnetoFour+\",\"+a.Gate_StatusFivetoEight+\",\"+a.GateOnePosition+\",\"+a.GateTwoPosition+\",\"+a.GateThreePosition+\",\"+a.GateFourPosition+\",\"+a.GateFivePosition+\",\"+a.GateSixPosition+\",\"+a.GateSevenPosition+\",\"+a.GateEightPosition+\",\"+a.GateNinePosition+\",\"+a.GateTenPosition+\",\"+a.Red_Phase+\",\"+a.Yellow_Phase+\",\"+a.Blue_Phase+\",0,0,0,0,0,0,\"+a.Gate_ConditionNinetoTen+\",\"+a.Gate_ModeNinetoTen+\",\"+a.Gate_StatusNinetoTen,\ndata2 : b.Year +\"-\"+ b.Month +\"-\"+ b.Day+\",\"+ b.Hour+\":\"+b.Minute+\":\"+b.Second+\",\"+b.Upstream+\",\"+a.Downstream+\",\"+b.Tidal+\",\"+b.Danger_Set_Point+\",\"+b.Alert_Set_Point+\",\"+b.Control_Level_Set_Point+\",\"+b.Gate_ConditionOnetoFour+\",\"+b.Gate_ConditionFivetoEight+\",\"+b.Gate_ModeOnetoFour+\",\"+b.Gate_ModeFivetoEight+\",\"+b.Gate_StatusOnetoFour+\",\"+b.Gate_StatusFivetoEight+\",\"+b.GateOnePosition+\",\"+b.GateTwoPosition+\",\"+b.GateThreePosition+\",\"+b.GateFourPosition+\",\"+b.GateFivePosition+\",\"+b.GateSixPosition+\",\"+b.GateSevenPosition+\",\"+b.GateEightPosition+\",\"+b.GateNinePosition+\",\"+b.GateTenPosition+\",\"+b.Red_Phase+\",\"+b.Yellow_Phase+\",\"+b.Blue_Phase+\",0,0,0,0,0,0,\"+b.Gate_ConditionNinetoTen+\",\"+b.Gate_ModeNinetoTen+\",\"+b.Gate_StatusNinetoTen,\ndata3 : c.Year +\"-\"+ c.Month +\"-\"+ c.Day+\",\"+ c.Hour+\":\"+c.Minute+\":\"+c.Second+\",\"+c.Upstream+\",\"+a.Downstream+\",\"+c.Tidal+\",\"+c.Danger_Set_Point+\",\"+c.Alert_Set_Point+\",\"+c.Control_Level_Set_Point+\",\"+c.Gate_ConditionOnetoFour+\",\"+c.Gate_ConditionFivetoEight+\",\"+c.Gate_ModeOnetoFour+\",\"+c.Gate_ModeFivetoEight+\",\"+c.Gate_StatusOnetoFour+\",\"+c.Gate_StatusFivetoEight+\",\"+c.GateOnePosition+\",\"+c.GateTwoPosition+\",\"+c.GateThreePosition+\",\"+c.GateFourPosition+\",\"+c.GateFivePosition+\",\"+c.GateSixPosition+\",\"+c.GateSevenPosition+\",\"+c.GateEightPosition+\",\"+c.GateNinePosition+\",\"+c.GateTenPosition+\",\"+c.Red_Phase+\",\"+c.Yellow_Phase+\",\"+c.Blue_Phase+\",0,0,0,0,0,0,\"+c.Gate_ConditionNinetoTen+\",\"+c.Gate_ModeNinetoTen+\",\"+c.Gate_StatusNinetoTen,\ndata4 : d.Year +\"-\"+ d.Month +\"-\"+ d.Day+\",\"+ d.Hour+\":\"+d.Minute+\":\"+d.Second+\",\"+d.Upstream+\",\"+a.Downstream+\",\"+d.Tidal+\",\"+d.Danger_Set_Point+\",\"+d.Alert_Set_Point+\",\"+d.Control_Level_Set_Point+\",\"+d.Gate_ConditionOnetoFour+\",\"+d.Gate_ConditionFivetoEight+\",\"+d.Gate_ModeOnetoFour+\",\"+d.Gate_ModeFivetoEight+\",\"+d.Gate_StatusOnetoFour+\",\"+d.Gate_StatusFivetoEight+\",\"+d.GateOnePosition+\",\"+d.GateTwoPosition+\",\"+d.GateThreePosition+\",\"+d.GateFourPosition+\",\"+d.GateFivePosition+\",\"+d.GateSixPosition+\",\"+d.GateSevenPosition+\",\"+d.GateEightPosition+\",\"+d.GateNinePosition+\",\"+d.GateTenPosition+\",\"+d.Red_Phase+\",\"+d.Yellow_Phase+\",\"+d.Blue_Phase+\",0,0,0,0,0,0,\"+d.Gate_ConditionNinetoTen+\",\"+d.Gate_ModeNinetoTen+\",\"+d.Gate_StatusNinetoTen\n},\n\"status\":\"complete\"\n}\n}\nreturn msg;","outputs":1,"noerr":0,"x":411,"y":589,"wires":[["3001fb50.7d6fe4"]]},{"id":"3001fb50.7d6fe4","type":"xml","z":"b166da4.4f2dc28","name":"","property":"payload","attr":"","chr":"","x":539,"y":588,"wires":[["e3b0310c.77154"]]},{"id":"24efa47f.17bfec","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"trend","server":"192.168.1.102","port":"1433","encyption":true,"database":"trainee","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]`

3.) How to display or debug full data because I want preview full data.

TQVM.

[torchard]

Thank you for reposting your code, I am able to import it now.

1.) To change the minute 5 to “05” try the following code in a function block:

min = ('0' + minute).slice(-2);

This will add a “0” to the front which gives you “05”, but if you put in a number like 45 you only need two digits and so you must slice(-2) to avoid “045”. You can use the same method for months.

2.) Can you please paste in the exact output you get from the trend_data MSSQL node?
I cannot select from your database to test the payload, but if you paste the output I can inject it into my flow manually.

3.) Open the node and select “system console” and then look at your Node-RED logs, the debug pane is limited but there is no limit for the log.

[traineeTKH]

convert data to XML I solve it already.
Today I just saw the YouTube video taking about this min = (‘0’ + minute).slice(-2);

Thanks all of you helping me. TQVM.