Here is my flow for sending room details. I have an email node set up, and a http request node which links to my Click Send account API.
[{"id":"be9ac30e.6056c","type":"ui_form","z":"e5a2b317.822f2","name":"","label":"","group":"5abf0d7c.3d62b4","order":0,"width":0,"height":0,"options":[{"label":"Please enter the room number to which you want the details for.","value":"Room","type":"text","required":true},{"label":"Please enter the customer's phone number to send their details via text.","value":"number","type":"text","required":false},{"label":"Please enter customer email.","value":"Cust","type":"text","required":false}],"formValue":{"Room":"","number":"","Cust":""},"payload":"","topic":"","x":90,"y":40,"wires":[["2820deb7.048eb2"]]},{"id":"46191f3b.d1f8f","type":"function","z":"e5a2b317.822f2","name":"TEST","func":"msg.topic = \"SELECT * FROM dbo.PasswordStore WHERE Room='\" + msg.payload[\"Room\"] + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":140,"wires":[["1721dee4.a7dad1","561d1965.c6c9e8"]]},{"id":"1721dee4.a7dad1","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":330,"y":240,"wires":[]},{"id":"253e0d99.caa072","type":"template","z":"e5a2b317.822f2","name":"HTML","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html>\n <head>\n{{#payload}}\n <h1>Room Number - {{{Room}}}</h1>\n </head>\n<body>\n <p>Email Address - {{{Email}}}</p>\n <p>Password - {{{Password}}}</p>\n </body>\n{{/payload}}\n</html>","output":"str","x":710,"y":120,"wires":[["9af45029.d04dc"]]},{"id":"561d1965.c6c9e8","type":"sqldbs","z":"e5a2b317.822f2","mydb":"32e83433.d136cc","querytype":"select","name":"","x":450,"y":180,"wires":[["549d69b5.6aca98"]]},{"id":"549d69b5.6aca98","type":"change","z":"e5a2b317.822f2","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":260,"wires":[["253e0d99.caa072","5309eedc.1c17a","e1e3358f.8131f8"]]},{"id":"ec28c11.655804","type":"e-mail","z":"e5a2b317.822f2","server":"smtp.gmail.com","port":"465","secure":true,"name":"","dname":"","x":949,"y":46,"wires":[]},{"id":"9af45029.d04dc","type":"change","z":"e5a2b317.822f2","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Your Room Details","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":80,"wires":[["ec28c11.655804","5a857164.793cd"]]},{"id":"5a857164.793cd","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":940,"y":160,"wires":[]},{"id":"2820deb7.048eb2","type":"change","z":"e5a2b317.822f2","name":"","rules":[{"t":"move","p":"payload.Cust","pt":"msg","to":"to","tot":"msg"},{"t":"move","p":"payload.number","pt":"msg","to":"number","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":220,"y":80,"wires":[["46191f3b.d1f8f","f4f3de50.62ac9"]]},{"id":"f4f3de50.62ac9","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":460,"y":80,"wires":[]},{"id":"afcc41c0.28eec","type":"http request","z":"e5a2b317.822f2","name":"Send SMS","method":"POST","ret":"obj","url":"https://rest.clicksend.com/v3/sms/send","tls":"","x":986,"y":460,"wires":[["37246a0f.f8f786"]]},{"id":"238a85c2.a9a68a","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":996,"y":359,"wires":[]},{"id":"37246a0f.f8f786","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1145,"y":434,"wires":[]},{"id":"5309eedc.1c17a","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":750,"y":262,"wires":[]},{"id":"e1e3358f.8131f8","type":"template","z":"e5a2b317.822f2","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n \"messages\": [\n {\n \"to\": \"{{payload.number}}\",\n \"body\": \"Room Number - {{payload.Room}} Email - {{payload.Email}} Password - {{payload.Password}}\",\n \"from\": \"Room Details\"\n }\n ]\n}","output":"json","x":764,"y":369,"wires":[["238a85c2.a9a68a","afcc41c0.28eec"]]},{"id":"5abf0d7c.3d62b4","type":"ui_group","z":"","name":"Print Room Details","tab":"e1207017.0cfd8","disp":true,"width":"12","collapse":false},{"id":"32e83433.d136cc","type":"sqldbsdatabase","z":"","host":"den1.mssql4.gear.host","port":"1433","db":"passwordstore1","dialect":"mssql"},{"id":"e1207017.0cfd8","type":"ui_tab","z":"","name":"Print Room Details","icon":"dashboard"}]
You can see that I have requested the data from my MSSQL Database, and then set the msg.payload of that data to msg.payload[0]. So my data is now under msg.payload[0].
However, I have tried referencing my data as {{msg.payload[0].Room}}, and {{payload[0].Room}}, and both of those come out with the same output as before.
If you need any more info on my flow, please ask and I will do my best to answer.
Thanks.