Dear node red forum members,
First time I post a question (normal I can find any solutions here, but i dont know how to find this problem).
I hope this is the right way. Also my primary language is dutch, i try to write it in my best english, also iam a novice in javascript and always try to find a way in this by searching on the web and this forum.
I read a XML file (see example below) and write data into a SQL database (MS SQL Server Express (64-bit) 15.0.2000.5).
Normal this is not a problem but in this case the xml is not straight forward. Part A (picture below) is a part of part B, so every row is an unique value from part B with the information from part A.
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]
The output from reading the XML file in node red:
The XML file (information is fake and as example):
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ProductData>
<Product id="0">
<ID>425</ID>
<TimeStamp>DT#2011-03-28-00:47:37</TimeStamp>
<MaschinNr>111</MaschinNr>
<LoafNr>123</LoafNr>
<ProductTypeNr>33</ProductTypeNr>
<PortionCount>3</PortionCount>
<ActualMaxHeightPipe1>2</ActualMaxHeightPipe1>
<ActualMaxHeightPipe2>3</ActualMaxHeightPipe2>
<ActualMaxHeightPipe3>4</ActualMaxHeightPipe3>
<PipeType>1</PipeType>
<PipeFormat>2</PipeFormat>
<PipeDiameter>0</PipeDiameter>
<PipeEdgeLengthA>5</PipeEdgeLengthA>
<PipeEdgeLengthB>6</PipeEdgeLengthB>
<ActualHeight>1,1</ActualHeight>
<TargetWeight>0</TargetWeight>
<ActualWeight>15.347,1</ActualWeight>
<PreferredWeightPortion>4530</PreferredWeightPortion>
<AveragePortions>4439,03</AveragePortions>
<AverageReturn>1</AverageReturn>
<SettingsValuePreferredWeight>4510</SettingsValuePreferredWeight>
<SettingsValueMaxTargetWeight>4516</SettingsValueMaxTargetWeight>
<SettingsValueMinTargetWeight>4437</SettingsValueMinTargetWeight>
<DataComplete>1</DataComplete>
<Portion id="1">
<ActualWeight>4448,2</ActualWeight>
<Coding>X1</Coding>
</Portion>
<Portion id="2">
<ActualWeight>4536,1</ActualWeight>
<Coding>X2</Coding>
</Portion>
<Portion id="3">
<ActualWeight>4412,8</ActualWeight>
<Coding>X3</Coding>
</Portion>
</Product>
<Product id="1">
<ID>426</ID>
<TimeStamp>DT#2011-03-28-00:47:37</TimeStamp>
<MaschinNr>111</MaschinNr>
<LoafNr>123</LoafNr>
<ProductTypeNr>33</ProductTypeNr>
<PortionCount>3</PortionCount>
<ActualMaxHeightPipe1>2</ActualMaxHeightPipe1>
<ActualMaxHeightPipe2>3</ActualMaxHeightPipe2>
<ActualMaxHeightPipe3>4</ActualMaxHeightPipe3>
<PipeType>1</PipeType>
<PipeFormat>2</PipeFormat>
<PipeDiameter>0</PipeDiameter>
<PipeEdgeLengthA>5</PipeEdgeLengthA>
<PipeEdgeLengthB>6</PipeEdgeLengthB>
<ActualHeight>1,1</ActualHeight>
<TargetWeight>0</TargetWeight>
<ActualWeight>15.347,1</ActualWeight>
<PreferredWeightPortion>4530</PreferredWeightPortion>
<AveragePortions>4439,03</AveragePortions>
<AverageReturn>1</AverageReturn>
<SettingsValuePreferredWeight>4510</SettingsValuePreferredWeight>
<SettingsValueMaxTargetWeight>4516</SettingsValueMaxTargetWeight>
<SettingsValueMinTargetWeight>4437</SettingsValueMinTargetWeight>
<DataComplete>1</DataComplete>
<Portion id="1">
<ActualWeight>4448,2</ActualWeight>
<Coding>X1</Coding>
</Portion>
<Portion id="2">
<ActualWeight>4536,1</ActualWeight>
<Coding>X2</Coding>
</Portion>
<Portion id="3">
<ActualWeight>4412,8</ActualWeight>
<Coding>X3</Coding>
</Portion>```
My flow:
[
{
"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": []
}
]
