XML File - Sort by Value

Hi everyone,

I'm trying to sort this data by 'P'. Not too sure where to start. I've had a play with a sort node etc. but no luck.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><root><L>0</L><Name>Diana Prahoveanu</Name><Club>Essex</Club><Time>   20.58</Time><P>10</P><Lap> 1</Lap><L>1</L><Name>Emily Cocksedge</Name><Club>Hertford</Club><Time>   16.75</Time><P>2</P><Lap> 1</Lap><L>2</L><Name>Xenia Coole</Name><Club>Essex</Club><Time>   17.14</Time><P>4</P><Lap> 1</Lap><L>3</L><Name>Georgia Gray</Name><Club>Oxford</Club><Time>   19.58</Time><P>9</P><Lap> 1</Lap><L>4</L><Name>Nikki Harrison</Name><Club>StGeorgeLDN</Club><Time>   18.09</Time><P>8</P><Lap> 1</Lap><L>5</L><Name>Steph Bryan</Name><Club>Central Lanc</Club><Time>   17.83</Time><P>7</P><Lap> 1</Lap><L>6</L><Name>Katie Chapple</Name><Club>Surrey</Club><Time>   17.57</Time><P>6</P><Lap> 1</Lap><L>7</L><Name>Charli Kneale</Name><Club>Glamorgan</Club><Time>   17.34</Time><P>5</P><Lap> 1</Lap><L>8</L><Name>Emma Hollis</Name><Club>Loughborough</Club><Time>   16.99</Time><P>3</P><Lap> 1</Lap><L>9</L><Name>Natasha Coombes</Name><Club>UCP Marjon</Club><Time...

Any pointers would be greatly appreciated!

Many thanks,

Take a look at the xml node that should make it easier because it will then be an object.

This is harder than it needs to be as the xml makes an object of the root xml, holding an array for each value

{"L":["0","1","2","3","4","5","6","7","8"],"Name":["Diana Prahoveanu","Emily Cocksedge","Xenia Coole","Georgia Gray","Nikki Harrison","Steph Bryan","Katie Chapple","Charli Kneale","Emma Hollis"],"Club":["Essex","Hertford","Essex","Oxford","StGeorgeLDN","Central Lanc","Surrey","Glamorgan","Loughborough"],"Time":["   20.58","   16.75","   17.14","   19.58","   18.09","   17.83","   17.57","   17.34","   16.99"],"P":["10","2","4","9","8","7","6","5","3"],"Lap":[" 1"," 1"," 1"," 1"," 1"," 1"," 1"," 1"," 1"]}

which is hard to sort and not the correct format to return it to an xml.
The json format to return it to an xml needs to be an array of objects.

[{"L":"1","Name":"Emily Cocksedge","Club":"Hertford","Time":"   16.75","P":"2","Lap":" 1"},{"L":"8","Name":"Emma Hollis","Club":"Loughborough","Time":"   16.99","P":"3","Lap":" 1"},{"L":"2","Name":"Xenia Coole","Club":"Essex","Time":"   17.14","P":"4","Lap":" 1"},{"L":"7","Name":"Charli Kneale","Club":"Glamorgan","Time":"   17.34","P":"5","Lap":" 1"},{"L":"6","Name":"Katie Chapple","Club":"Surrey","Time":"   17.57","P":"6","Lap":" 1"},{"L":"5","Name":"Steph Bryan","Club":"Central Lanc","Time":"   17.83","P":"7","Lap":" 1"},{"L":"4","Name":"Nikki Harrison","Club":"StGeorgeLDN","Time":"   18.09","P":"8","Lap":" 1"},{"L":"3","Name":"Georgia Gray","Club":"Oxford","Time":"   19.58","P":"9","Lap":" 1"},{"L":"0","Name":"Diana Prahoveanu","Club":"Essex","Time":"   20.58","P":"10","Lap":" 1"}]

i think there is an inconsistency in how the xml node converts from and to json.

So to sort the xml first you need to convert to json, then create an array of sorted key positions, and an array of root property names. Once you have that you can then format a new root array for the xml node to convert back to an xml.
e.g.

[{"id":"9055336c.29a5d","type":"inject","z":"30af2d3e.d94ea2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><root><L>0</L><Name>Diana Prahoveanu</Name><Club>Essex</Club><Time>   20.58</Time><P>10</P><Lap> 1</Lap><L>1</L><Name>Emily Cocksedge</Name><Club>Hertford</Club><Time>   16.75</Time><P>2</P><Lap> 1</Lap><L>2</L><Name>Xenia Coole</Name><Club>Essex</Club><Time>   17.14</Time><P>4</P><Lap> 1</Lap><L>3</L><Name>Georgia Gray</Name><Club>Oxford</Club><Time>   19.58</Time><P>9</P><Lap> 1</Lap><L>4</L><Name>Nikki Harrison</Name><Club>StGeorgeLDN</Club><Time>   18.09</Time><P>8</P><Lap> 1</Lap><L>5</L><Name>Steph Bryan</Name><Club>Central Lanc</Club><Time>   17.83</Time><P>7</P><Lap> 1</Lap><L>6</L><Name>Katie Chapple</Name><Club>Surrey</Club><Time>   17.57</Time><P>6</P><Lap> 1</Lap><L>7</L><Name>Charli Kneale</Name><Club>Glamorgan</Club><Time>   17.34</Time><P>5</P><Lap> 1</Lap><L>8</L><Name>Emma Hollis</Name><Club>Loughborough</Club><Time>   16.99</Time><P>3</P><Lap> 1</Lap></root></xml>","payloadType":"str","x":100,"y":140,"wires":[["4df22771.f79b78"]]},{"id":"4df22771.f79b78","type":"xml","z":"30af2d3e.d94ea2","name":"","property":"payload","attr":"","chr":"","x":260,"y":140,"wires":[["509bdc2b.ceb33c","616a3429.d4eb9c"]]},{"id":"509bdc2b.ceb33c","type":"function","z":"30af2d3e.d94ea2","name":"","func":"const key_pos =[];\nconst root = [];\nconst keys = Object.keys(msg.payload.root);\n// create an array of sorted new positions\nfor (const [key, value] of  Object.entries(msg.payload.root.P).sort((a,b) => a[1]-b[1])){\n    key_pos.push(Number(key));\n}\n\n// create new array for root so new xml can be created.\n key_pos.forEach((index_pos, index) => {\n     root[index] = {};\n     keys.forEach(key => {\n         Object.assign(root[index], {[key]: msg.payload.root[key][index_pos]})\n     })\n})\n// assign new root to payload.\nmsg.payload.root = root;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":140,"wires":[["fa63f01c.e0d918","616a3429.d4eb9c"]]},{"id":"616a3429.d4eb9c","type":"debug","z":"30af2d3e.d94ea2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":180,"wires":[]},{"id":"fa63f01c.e0d918","type":"xml","z":"30af2d3e.d94ea2","name":"","property":"payload","attr":"","chr":"","x":570,"y":140,"wires":[["616a3429.d4eb9c"]]}]

output

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><root><L>1</L><Name>Emily Cocksedge</Name><Club>Hertford</Club><Time>   16.75</Time><P>2</P><Lap> 1</Lap><L>8</L><Name>Emma Hollis</Name><Club>Loughborough</Club><Time>   16.99</Time><P>3</P><Lap> 1</Lap><L>2</L><Name>Xenia Coole</Name><Club>Essex</Club><Time>   17.14</Time><P>4</P><Lap> 1</Lap><L>7</L><Name>Charli Kneale</Name><Club>Glamorgan</Club><Time>   17.34</Time><P>5</P><Lap> 1</Lap><L>6</L><Name>Katie Chapple</Name><Club>Surrey</Club><Time>   17.57</Time><P>6</P><Lap> 1</Lap><L>5</L><Name>Steph Bryan</Name><Club>Central Lanc</Club><Time>   17.83</Time><P>7</P><Lap> 1</Lap><L>4</L><Name>Nikki Harrison</Name><Club>StGeorgeLDN</Club><Time>   18.09</Time><P>8</P><Lap> 1</Lap><L>3</L><Name>Georgia Gray</Name><Club>Oxford</Club><Time>   19.58</Time><P>9</P><Lap> 1</Lap><L>0</L><Name>Diana Prahoveanu</Name><Club>Essex</Club><Time>   20.58</Time><P>10</P><Lap> 1</Lap></root>
1 Like

I think I understand! I'm still going through it to figure it out, thank you!