426
DT#2011-03-28-00:47:37
111
123
33
3
2
3
4
1
2
0
5
6
1,1
0
15.347,1
4530
4439,03
1
4510
4516
4437
1
4448,2
X1
4536,1
X2
4412,8
X3
```
````
My flow:
```auto
[
{
"id": "220d91c57ea341b1",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": "",
"env": []
},
{
"id": "dc7096150df6cd7f",
"type": "file in",
"z": "220d91c57ea341b1",
"name": "",
"filename": "",
"format": "utf8",
"chunk": false,
"sendError": false,
"encoding": "none",
"allProps": false,
"x": 360,
"y": 200,
"wires": [
[
"34bad0dfbb405ba1"
]
]
},
{
"id": "76ec37334a5792c0",
"type": "debug",
"z": "220d91c57ea341b1",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1750,
"y": 200,[spoiler]This text will be hidden[/spoiler]
"wires": []
},
{
"id": "a95cc556f9d96536",
"type": "function",
"z": "220d91c57ea341b1",
"name": "SQL INSERT table dbo.XML_CenterCutOut ",
"func": " var ProductData = 0\n var Product = 0\n var Product_ID = msg.payload.$.id\n var Product_ID2 = msg.payload.ID[0]\n var TimeStamp = (new Date(msg.payload.TimeStamp[0]).toISOString())\n var MaschinNr = msg.payload.MaschinNr[0]\n var LoafNr = msg.payload.LoafNr[0]\n var ProductTypeNr = msg.payload.ProductTypeNr[0]\n var PortionCount = msg.payload.PortionCount[0]\n var ActualMaxHeightPipe1 = msg.payload.ActualMaxHeightPipe1[0]\n var ActualMaxHeightPipe2 = msg.payload.ActualMaxHeightPipe2[0]\n var ActualMaxHeightPipe3 = msg.payload.ActualMaxHeightPipe3[0]\n var PipeType = msg.payload.PipeType[0]\n var PipeFormat = msg.payload.PipeFormat[0]\n var PipeDiameter = msg.payload.PipeDiameter[0]\n var PipeEdgeLengthA = msg.payload.PipeEdgeLengthA[0]\n var PipeEdgeLengthB = msg.payload.PipeEdgeLengthB[0]\n\t var ActualHeight = msg.payload.ActualHeight[0]\n var TargetWeight = msg.payload.TargetWeight[0]\n var ActualWeight = msg.payload.ActualWeight[0]\n var PreferredWeightPortion = msg.payload.PreferredWeightPortion[0]\n var AveragePortions = msg.payload.AveragePortions[0]\n var AverageReturn = msg.payload.AverageReturn[0]\n var SettingsValuePreferredWeight = msg.payload.SettingsValuePreferredWeight[0]\n var SettingsValueMaxTargetWeight = msg.payload.SettingsValueMaxTargetWeight[0]\n var SettingsValueMinTargetWeight = msg.payload.SettingsValueMinTargetWeight[0]\n\t var DataComplete = msg.payload.DataComplete[0] //OK\n var Portion = msg.payload.Portion[0] //OK\n var Portion_id = msg.payload.Portion_id[0] //NOK\n var Portion_ActualWeight = msg.payload.Portion_ActualWeight[0] //OK\n var Portion_Coding = msg.payload.Portion_Coding[0] //OK\n \n \nmsg.payload = \"INSERT INTO [A-Ware].[dbo].[XML_CenterCutOut](ProductData, Product, Product_ID, Product_ID2, TimeStamp, MaschinNr, LoafNr, ProductTypeNr, PortionCount, ActualMaxHeightPipe1, ActualMaxHeightPipe2, ActualMaxHeightPipe3, PipeType, PipeFormat, PipeDiameter, PipeEdgeLengthA, PipeEdgeLengthB, ActualHeight, TargetWeight, ActualWeight, PreferredWeightPortion, AveragePortions, AverageReturn, SettingsValuePreferredWeight, SettingsValueMaxTargetWeight, SettingsValueMinTargetWeight, DataComplete, Portion, Portion_id, Portion_ActualWeight, Portion_Coding) VALUES ('\"+ProductData+\"', '\"+Product+\"', '\"+Product_ID+\"', '\"+Product_ID2+\"', '\"+TimeStamp+\"', '\"+MaschinNr+\"', '\"+LoafNr+\"', '\"+ProductTypeNr+\"', '\"+PortionCount+\"', '\"+ActualMaxHeightPipe1+\"', '\"+ActualMaxHeightPipe2+\"', '\"+ActualMaxHeightPipe3+\"', '\"+PipeType+\"', '\"+PipeFormat+\"', '\"+PipeDiameter+\"', '\"+PipeEdgeLengthA+\"', '\"+PipeEdgeLengthB+\"', '\"+ActualHeight+\"', '\"+TargetWeight+\"', '\"+ActualWeight+\"', '\"+PreferredWeightPortion+\"', '\"+AveragePortions+\"', '\"+AverageReturn+\"', '\"+SettingsValuePreferredWeight+\"', '\"+SettingsValueMaxTargetWeight+\"', '\"+SettingsValueMinTargetWeight+\"', '\"+DataComplete+\"', '\"+Portion+\"', '\"+Portion_id+\"', '\"+Portion_ActualWeight+\"', '\"+Portion_Coding+\"')\";\n \nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1310,
"y": 200,
"wires": [
[
"76ec37334a5792c0"
]
]
},
{
"id": "34bad0dfbb405ba1",
"type": "xml",
"z": "220d91c57ea341b1",
"name": "",
"property": "payload",
"attr": "",
"chr": "",
"x": 530,
"y": 200,
"wires": [
[
"aa749ab8ce0be585"
]
]
},
{
"id": "7308825c31be8ff0",
"type": "watch-directory",
"z": "220d91c57ea341b1",
"folder": "D:\\simulatie",
"recursive": 0,
"typeEvent": "create",
"ignoreInitial": true,
"ignoredFiles": "LoafDT#",
"ignoredFilesType": "re",
"name": "Watch CenterCutOutDT",
"x": 120,
"y": 260,
"wires": [
[
"dc7096150df6cd7f"
]
]
},
{
"id": "aa749ab8ce0be585",
"type": "split",
"z": "220d91c57ea341b1",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "",
"x": 690,
"y": 200,
"wires": [
[
"d8778c405a04402a"
]
]
},
{
"id": "d8778c405a04402a",
"type": "split",
"z": "220d91c57ea341b1",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "",
"x": 810,
"y": 200,
"wires": [
[
"fed7357a5a80eb57"
]
]
},
{
"id": "fed7357a5a80eb57",
"type": "split",
"z": "220d91c57ea341b1",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "",
"x": 930,
"y": 200,
"wires": [
[
"a95cc556f9d96536",
"19027aec42068589"
]
]
},
{
"id": "19027aec42068589",
"type": "debug",
"z": "220d91c57ea341b1",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 1210,
"y": 120,
"wires": []
}
]
```
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 13:32 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/2 "2022-03-28T13:32:02Z")
Are you asking for advice on how to design the database (which is nothing to do with node-red) or how to get the data into the database once you have designed the db?
---
### Author:  [@vleugd01](https://discourse.nodered.org/u/vleugd01)
#### Post date: [28 March 2022 13:38 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/3 "2022-03-28T13:38:30Z")
I ask for advice about node-red, not the database. If i have to be specific it is a question about:
> From node red:
> Object:
> A (as example for LoafNr)
> B (as example for PipeType)
> C (as example for ActualHeight)
> D[2] (as example for Portion)
>
> How it should be in the database:
> A, B, C, D[0]
> A, B, C, D[1]
> A, B, C, D[2]
I know how to send the information to the database. For me is the biggest question how to prepare it. I need to send seperate messages with the information D + A,B,C. D is a array and ABC needs to be added to D in every message. The message i prepare to send to the database with a INSERT query.
So if:
A=10
B=33
C=123
D[0] = 1
D[1] = 44
D[2] = 9
D[3] = 33
To a single message:
Message1: 10, 33, 123, 1
Message2: 10, 33, 123, 44
Message3: 10, 33, 123, 9
Message4: 10, 33, 123, 33
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [28 March 2022 14:13 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/4 "2022-03-28T14:13:42Z")
Hi, here is a better way.
```auto
[{"id":"34bad0dfbb405ba1","type":"xml","z":"220d91c57ea341b1","name":"","property":"payload","attr":"","chr":"","x":850,"y":140,"wires":[["afb7202973625132"]]},{"id":"aa749ab8ce0be585","type":"split","z":"220d91c57ea341b1","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":200,"wires":[["8df0007b12b0428b"]]},{"id":"c24faade58a75faf","type":"inject","z":"220d91c57ea341b1","name":"file data (sample)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":" 425 DT#2011-03-28-00:47:37 111 123 33 3 2 3 4 1 2 0 5 6 1,1 0 15.347,1 4530 4439,03 1 4510 4516 4437 1 4448,2 X1 4536,1 X2 4412,8 X3 426 DT#2011-03-28-00:47:37 111 123 33 3 2 3 4 1 2 0 5 6 1,1 0 15.347,1 4530 4439,03 1 4510 4516 4437 1 4448,2 X1 4536,1 X2 4412,8 X3 ","payloadType":"str","x":680,"y":140,"wires":[["34bad0dfbb405ba1"]]},{"id":"8df0007b12b0428b","type":"function","z":"220d91c57ea341b1","name":"","func":"\nconst data = {\n ProductData: 0,\n Product: 0,\n Product_ID: msg.payload.$.id,\n Product_ID2: msg.payload.ID[0],\n TimeStamp: (new Date(msg.payload.TimeStamp[0]).toISOString()),\n MaschinNr: msg.payload.MaschinNr[0],\n LoafNr: msg.payload.LoafNr[0],\n ProductTypeNr: msg.payload.ProductTypeNr[0],\n PortionCount: msg.payload.PortionCount[0],\n ActualMaxHeightPipe1: msg.payload.ActualMaxHeightPipe1[0],\n ActualMaxHeightPipe2: msg.payload.ActualMaxHeightPipe2[0],\n ActualMaxHeightPipe3: msg.payload.ActualMaxHeightPipe3[0],\n PipeType: msg.payload.PipeType[0],\n PipeFormat: msg.payload.PipeFormat[0],\n PipeDiameter: msg.payload.PipeDiameter[0],\n PipeEdgeLengthA: msg.payload.PipeEdgeLengthA[0],\n PipeEdgeLengthB: msg.payload.PipeEdgeLengthB[0],\n ActualHeight: msg.payload.ActualHeight[0],\n TargetWeight: msg.payload.TargetWeight[0],\n ActualWeight: msg.payload.ActualWeight[0],\n PreferredWeightPortion: msg.payload.PreferredWeightPortion[0],\n AveragePortions: msg.payload.AveragePortions[0],\n AverageReturn: msg.payload.AverageReturn[0],\n SettingsValuePreferredWeight: msg.payload.SettingsValuePreferredWeight[0],\n SettingsValueMaxTargetWeight: msg.payload.SettingsValueMaxTargetWeight[0],\n SettingsValueMinTargetWeight: msg.payload.SettingsValueMinTargetWeight[0],\n DataComplete: msg.payload.DataComplete[0] //OK,\n}\nconst portions = msg.payload.Portion\nfor (let index = 0; index < portions.length; index++) {\n const p = portions[index];\n data.Portion_id = p.$.id;\n data.Portion_ActualWeight = p.ActualWeight[0];\n data.Portion_Coding = p.Coding[0];\n const m = { payload: data };\n node.send(m);\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":840,"y":200,"wires":[["d2917f815592bdd9"]]},{"id":"d2917f815592bdd9","type":"debug","z":"220d91c57ea341b1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1010,"y":200,"wires":[]},{"id":"afb7202973625132","type":"change","z":"220d91c57ea341b1","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.ProductData.Product","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1000,"y":140,"wires":[["aa749ab8ce0be585"]]}]
```
This gives you the messages - next feed these into a MSSQL-PLUS message and address the items directly using parameters
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 14:14 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/5 "2022-03-28T14:14:53Z")
> [@vleugd01](#):
>
> Message1: 10, 33, 123, 1
> Message2: 10, 33, 123, 44
> Message3: 10, 33, 123, 9
> Message4: 10, 33, 123, 33
You can do that by iterating the array `msg.payload.Portion` and using `node.send()` to send each message. Something like this:
```auto
msg.payload.Portion.forEach( sendIt )
return null
function sendIt( portion ) {
// build the sql string using msg.payload.Timestamp, etc, the portion element is in the variable portion.
const sql = `INSERT INTO table ..... `
node.send( payload: sql )
}
```
I don't use MS SQL so don't know if it is correct to put it in msg.payload, so that may be wrong. I believe that most use node-red-contrib-mssql-plus for MS SQL
---
### Author:  [@vleugd01](https://discourse.nodered.org/u/vleugd01)
#### Post date: [28 March 2022 14:19 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/6 "2022-03-28T14:19:14Z")
Wow, i really have no words. This is exactly what I mean. Thank you!
@Colin Also thanks! Yes i use mssql-plus, i prepare in a function the query and send it as one row.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [28 March 2022 14:20 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/7 "2022-03-28T14:20:04Z")
> [@vleugd01](#):
>
> Yes i use mssql-plus
So instead of using a dangerous SQL STRING to do the insert, use parameters...
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 14:21 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/8 "2022-03-28T14:21:23Z")
Steve's way is better of course.
---
### Author:  [@vleugd01](https://discourse.nodered.org/u/vleugd01)
#### Post date: [28 March 2022 14:29 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/9 "2022-03-28T14:29:10Z")
Ha! I did try that way, it didnt work, now by seeing your example i know what i did wrong. Thanks again.
@Colin I like to learn so i try your way also.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [11 April 2022 14:29 UTC](https://discourse.nodered.org/t/read-xml-and-write-to-a-sql-database/60452/10 "2022-04-11T14:29:31Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.