Getting specific data from a large xml

General
1

Hi everyone,

So, i'm working with a machine that posts its setup information in a .xml file, but it's information comes in a History topic. So if someone changes any setup value the "actual value" will be written furter in the history (See image below).

image
image875×346 11.7 KB

I need to get only the last value of each setup variable, like if "Zones Set value bottom [°C] Zone 10 from 240 to 215" was the last modification of "ZONE 10", 215 is what i need.

I managed to get and transform the xml file into jason like this:
image

and the result was:

image
image545×581 15.8 KB

but because of lacking javascript skills i'm stuck and need u guys!

any help will be appreciated!!

2

You can use something called Regular Expressions in a Function node to filter out the numbers and pick the last one which is the number you want.

//regex match digits (numbers) and take the last [2]
let number = Number(msg.payload.match(/\d+/g)[2])

// filter out the zone with regex
let zone = msg.payload.match(/Zone\s(\S)+/g)[0]

msg.payload = {}
msg.payload.zone = zone
msg.payload.number = number

return msg;

Example:

[{"id":"fa0639af.697938","type":"inject","z":"ac0f61dd.69e26","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"Zones Set value bottom [°C] Zone 10 from 240 to 215","payloadType":"str","x":240,"y":840,"wires":[["a37dbc9f.4775e8"]]},{"id":"a37dbc9f.4775e8","type":"function","z":"ac0f61dd.69e26","name":"Regex match","func":"//regex match digits (numbers) and take the last [2]\nlet number = Number(msg.payload.match(/\\d+/g)[2])\n\n// filter out the zone wit regex\nlet zone = msg.payload.match(/Zone\\s(\\S)+/g)[0]\n\nmsg.payload = {}\nmsg.payload.zone = zone\nmsg.payload.number = number\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":420,"y":840,"wires":[["fcfd4a1b.334da8"]]},{"id":"fcfd4a1b.334da8","type":"debug","z":"ac0f61dd.69e26","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":840,"wires":[]}]

If you want to apply it for all elements of the History array then use the Copy value icon on the root of your msg from the Debug window and send as a copy to play with :wink:

3

If you walk through the History[0] object with

const out = ()
history = msg.payload.SOLDER_PRG.History[0]
const hKeys = Object.keys(history)
Object.keys(hKeys).forEach( key => {
    let myitem = history[key][0]
    let zone = '' // you need to use probably a regex to extract the right zone from myitem - if nobody has answered after my dinner, I'll try and put something together.
    let temp = '' // another regex needed here or combine with above
    let dt = '' // same for date/time if you need that
    out[zone] = {dt: dt, temp: temp, zone: zone}
})

msg.payload = out
return msg

Because you use an object, as long as the History is in the right order, you will end up with the last entry being kept and everything else ignored (because it is overwritten as you go along).

4

I tried but it doesn't seem to work to all file. How do i specify wich zone i want? I want all zones. If i use the file as a string, it returns me the wrong value, it is returning me '10'.

<?xml version="1.0" encoding="UTF-8"?><!-- Factory solder PRG DOC --><SOLDER_PRG>  <Version>1.10</Version>  <PrgName>    <stfProg>BASEBOARD.xml</stfProg>  </PrgName>  <Bib>    <stfBib>TBK</stfBib>  </Bib>  <Info>    <stfInfo>Defaultprogram</stfInfo>  </Info>  <Benutzer>    <stfBenutzer>factory</stfBenutzer>  </Benutzer>  <History>    <Items>65</Items>    <Item_0>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 1 from 120 to 90</Item_0>    <Item_1>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 2 from 150 to 110</Item_1>    <Item_2>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 3 from 180 to 130</Item_2>    <Item_3>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 4 from 200 to 145</Item_3>    <Item_4>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 5 from 170 to 150</Item_4>    <Item_5>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 6 from 170 to 165</Item_...

here is the copy u asked, but it came incomplete for some reason.

5

Hi, i tried this way:

const out = ()
history = msg.payload.SOLDER_PRG.History[0]
const hKeys = Object.keys(history)
Object.keys(hKeys).forEach( key => {
    let myitem = history[key][0]
    let zone = Number(msg.payload.match(/\d+/g)[2]) 
    let temp = msg.payload.match(/Zone 2\s(\S)+/g)[0]
    out[zone] = {temp: temp, zone: zone}
})

msg.payload = out
return msg

I got this:
image

How will it filter wich zone i want? Regex matches the exactly string?

Tnx for replying!

6

change line 1 to const out = {}

7

image

Tnx. Now, another one haha!

8

is it possible to try to send the complete xml data .. because you will need some filtering of the lines that dont include a Zone like you show in your first screenshot

9

can you attach a full valid XML file?

10

It doesnt accept .xml, so i'm sending a .txt!

BASEBOARD.txt (64.1 KB)

11

Any good?

image
image788×439 20.3 KB

Demo flow...

[{"id":"dd11d063.35bfb","type":"inject","z":"59f3e1c6.409f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":610,"y":540,"wires":[["80016038.2eea5"]]},{"id":"80016038.2eea5","type":"template","z":"59f3e1c6.409f8","name":"xml","field":"payload","fieldType":"msg","format":"html","syntax":"mustache","template":"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<!-- Factory solder PRG DOC -->\n<SOLDER_PRG>\n    <Version>1.10</Version>\n    <PrgName>\n        <stfProg>BASEBOARD.xml</stfProg>\n    </PrgName>\n    <Bib>\n        <stfBib>TBK</stfBib>\n    </Bib>\n    <Info>\n        <stfInfo>Defaultprogram</stfInfo>\n    </Info>\n    <Benutzer>\n        <stfBenutzer>factory</stfBenutzer>\n    </Benutzer>\n    <History>\n        <Items>65</Items>\n        <Item_0>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 1 from 120 to 90</Item_0>\n        <Item_1>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 2 from 150 to 110</Item_1>\n        <Item_2>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 3 from 180 to 130</Item_2>\n        <Item_3>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 4 from 200 to 145</Item_3>\n        <Item_4>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 5 from 170 to 150</Item_4>\n        <Item_5>2017.10.09 07:48:47 User:Service Zones Set value top [°C] Zone 6 from 170 to 165</Item_5>\n    </History>\n</SOLDER_PRG>","output":"str","x":750,"y":540,"wires":[["b3a5e3f1.ed7b1"]]},{"id":"c0903850.b315c8","type":"function","z":"59f3e1c6.409f8","name":"","func":"const out = {}\nvar history = msg.payload.SOLDER_PRG.History[0]\nconst hKeys = Object.keys(history)\n\nfor (let index = 0; index < hKeys.length; index++) {\n    const key = hKeys[index];\n    const myitem = history[key];\n    if(myitem && myitem.length) {\n        let str = myitem[0];\n        let matches = /Zone (\\d+).*?(\\d+).*?(\\d+)/g.exec(str)\n        if (!matches || matches.length < 3) continue\n        let _zone = matches[1]\n        let _from = Number(matches[2])\n        let _to = Number(matches[3])\n        out[\"Zone_\" + _zone] = { zone: _zone, from: _from, to: _to } \n    } \n}\n\nmsg.payload = out\nreturn msg\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1000,"y":540,"wires":[["d14ca258.31617"]]},{"id":"d14ca258.31617","type":"debug","z":"59f3e1c6.409f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1150,"y":540,"wires":[]},{"id":"b3a5e3f1.ed7b1","type":"xml","z":"59f3e1c6.409f8","name":"","property":"payload","attr":"","chr":"","x":870,"y":540,"wires":[["c0903850.b315c8","36360b58.a9c4a4"]]},{"id":"36360b58.a9c4a4","type":"debug","z":"59f3e1c6.409f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":990,"y":580,"wires":[]}]
2 Likes
12

That was super fast Steve :wink:

my contribution ..

let arr = Object.values(msg.payload.SOLDER_PRG.History[0])

let result = []

arr.forEach(el => {
    
    // test if Zone actually exists
    if ( /Zone\s(\S)+/g.test(el[0])) {
         //regex match digits (numbers) and take the last [2]
         let value = Number(el[0].match(/\d+/g)[8])
         // filter out the zone with regex
         let zone = el[0].match(/Zone\s(\S)+/g)[0]
         result.push({zone, value})
    }
   
})


msg.payload = result

return msg;

image

2 Likes
13

It's working exactly how i wanted! It's taking the last updated value to each zone, no matter how much changes happen! I couldn't fully comprehend it, so one last question, how would i wrote the code if i wanted Conveyor speed and width

 <Item_10>2017.10.09 07:52:40 User:Service  Conveyor conv.1 from 100 cm/min to 75 cm/min</Item_10>
<Item_18>2017.10.25 17:26:00 User:produza  Width Conveyor width (Width 1) from 100 mm to 90,5 mm</Item_18>

U guys are f***ng awesome!!

14

good you asked that last question because personally i didnt take into consideration that your values could be a floating point value and that changes the filtering a bit

maybe this?

let arr = Object.values(msg.payload.SOLDER_PRG.History[0])

let result = {}

arr.forEach(el => {
    
    // test if Zone actually exists
    if ( /Zone\s(\S)+/g.test(el[0])) {
         //regex match digits (numbers) 
         let from = Number(el[0].match(/\d+\,?\d+?/g)[6].replace(",", "."))
         let to = Number(el[0].match(/\d+\,?\d+?/g)[7].replace(",", "."))
         // filter out the zone with regex
         let zone = el[0].match(/Zone\s(\S)+/g)[0]
         result[zone] = {zone, from, to}
    }
    // test if Service Conveyor exists
    if (/Service\s\sConveyor/g.test(el[0])) {
         let from = Number(el[0].match(/\d+\,?\d+?/g)[6].replace(",", "."))
         let to = Number(el[0].match(/\d+\,?\d+?/g)[7].replace(",", "."))
         // filter out the Conveyor with regex
         let conv = el[0].match(/conv\.\d+/g)[0]
         result[conv] = {conv, from, to}
    }
     // test if Conveyor width exists
    if (/Conveyor\swidth/g.test(el[0])) {
         let from = Number(el[0].match(/\d+\,?\d+?/g)[6].replace(",", "."))
         let to = Number(el[0].match(/\d+\,?\d+?/g)[7].replace(",", "."))
       
         result["produza"] = {from, to}
    }
 
})


msg.payload = result

return msg;

ps. there are multiple User:produza Width Conveyor width entries.
you need only the latest one ?

1 Like
15

Tried the code and got this:
image

Yes, i need only the last update to the following variables:
Zone 1..10, Cooling Zone 1, Conveyor Speed and Width.

16

Where did you add the Function node code ?
Right after the XML node ?

image
image973×135 8.78 KB

17

Yeah, exactly like that

image
image761×51 7.1 KB

the one u sent before was working!

18

Dunno .. its working for me .. here's the Flow

[{"id":"fa0639af.697938","type":"inject","z":"ac0f61dd.69e26","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"Zones Set value bottom [°C] Zone 10 from 240 to 215","payloadType":"str","x":150,"y":700,"wires":[["5c600d6c.fa489c"]]},{"id":"a37dbc9f.4775e8","type":"function","z":"ac0f61dd.69e26","name":"Regex match","func":"let arr = Object.values(msg.payload.SOLDER_PRG.History[0])\n\nlet result = {}\n\narr.forEach(el => {\n    \n    // test if Zone actually exists\n    if ( /Zone\\s(\\S)+/g.test(el[0])) {\n         //regex match digits (numbers) \n         let from = Number(el[0].match(/\\d+\\,?\\d+?/g)[6].replace(\",\", \".\"))\n         let to = Number(el[0].match(/\\d+\\,?\\d+?/g)[7].replace(\",\", \".\"))\n         // filter out the zone with regex\n         let zone = el[0].match(/Zone\\s(\\S)+/g)[0]\n         result[zone] = {zone, from, to}\n    }\n    // test if Service Conveyor exists\n    if (/Service\\s\\sConveyor/g.test(el[0])) {\n         let from = Number(el[0].match(/\\d+\\,?\\d+?/g)[6].replace(\",\", \".\"))\n         let to = Number(el[0].match(/\\d+\\,?\\d+?/g)[7].replace(\",\", \".\"))\n         // filter out the Conveyor with regex\n         let conv = el[0].match(/conv\\.\\d+/g)[0]\n         result[conv] = {conv, from, to}\n    }\n     // test if Conveyor width exists\n    if (/Conveyor\\swidth/g.test(el[0])) {\n         let from = Number(el[0].match(/\\d+\\,?\\d+?/g)[6].replace(\",\", \".\"))\n         let to = Number(el[0].match(/\\d+\\,?\\d+?/g)[7].replace(\",\", \".\"))\n       \n         result[\"produza\"] = {from, to}\n    }\n \n})\n\n\nmsg.payload = result\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":670,"y":700,"wires":[["fcfd4a1b.334da8"]]},{"id":"fcfd4a1b.334da8","type":"debug","z":"ac0f61dd.69e26","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":890,"y":700,"wires":[]},{"id":"4ed03110.fe5be8","type":"xml","z":"ac0f61dd.69e26","name":"","property":"payload","attr":"","chr":"","x":450,"y":700,"wires":[["a37dbc9f.4775e8","bfeb104a.25ef58"]]},{"id":"5c600d6c.fa489c","type":"file in","z":"ac0f61dd.69e26","name":"xml","filename":"C:\\Users\\user\\desktop\\baseboard.xml","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":290,"y":700,"wires":[["4ed03110.fe5be8"]]},{"id":"bfeb104a.25ef58","type":"debug","z":"ac0f61dd.69e26","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":600,"y":640,"wires":[]}]

Change the File in path to your file

1 Like
19

It works!

Thank you guys very much! I wish i could mark u both as "solution"!

U guys made me happier! U r awesomeee!

20

It was a good challenge with Regular Expressions.
Test it thoroughly though cause there could be some cases we missed (like that with the floats)

If you want to learn more about RegEx there is an interesting video tutorial that covers many cases of the syntax

1 Like