XML Manipulation and searching

General
1

G'Day Peoples,

Im a Bit of a Noob with XML and it turns out just doing some work with XML and an web API

Is there a way of searching XML for example, So if the location of the value, in this case "Printing Status" is searchable ?

msg.payload = msg.payload.jobs.job[0].sections[0].section[3].custom_fields[0].custom_field[4].value[0]

something like msg.payload.jobs.job[0]['Printing Status"][0]

That way if the XML structure changes it doesnt break

here is the debug xml structure

image
image433Ă—764 61.7 KB

Unfortunately I cant post the flow as it has API keys in it

Also is there an easy way to show all wildcards for example

msg.payload.jobs.job[*].status[0]

So it returns all job ticket numbers from the xml ?

Any Help is appreciated!

2

What you show is not xml. Looks like you have passed it through a xml node and it has been converted to a Javascript object.

You can seach the Object with a function node using Javascript functions e.g. find() or filter(). Or you can use JSONata in a change node. JSONata is great for wildcards, but most find it confusing syntactically.

Would provide an example but you have not given an example input and output in a form that is copyable.

3

Thanks for the replay

This is whats coming from the API without the xml node.

image
image425Ă—516 93.3 KB

Do I leave as is and use JSONata?

4

No you convert the xml to a Javascript Object, as you showed in first post. Then use JSONata expression to select the info you want.

1 Like
5

Thanks for helping.

I Have spent the last 6 hours reading and playing around and I have made some progress.

Just one last thing

Im trying to search for "Rego" in the objects and return the value

image

Ive tried
payload..name="Rego"
payload..name.Rego
payload.**.job.sections.section.custom_fields.custom_field.Rego

These either return undefined or False

payload.**.name returns a list with the target in it

image

Any ideas?

6

I refer to my OG post.

with out data i can only offer a idea that may work, but i doubt it.

payload.**.name[$ = "Rego"]

As stated in OG reply I would need input data and output example.

7

Thanks.. Very helpful... and im nearly there!
That returned
image

The Value im after is value
image

I tried

payload.**.name[$ = "Rego"].value

That didnt work

I can see why you say the syntax of jsonata is hard!

8

This is my final guess without the asked for info, it is likely not correct as I have no idea of your objects correct format. Getting the correct expression from the information you have supplied is like fixing a car through a letter box.

$$.payload.*[$.name[0] = "rego"].value[0]

2 Likes
9

Tried that.. undefined

What info on the objects would you like me to post so you can see it all?

10

The complete object, copy payload and post here.

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path/value to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

p.s. I am out of house in 30 min wont be back at pc for 6-7 hours.

11 
{"id":[{"_":"38688191","$":{"readonly":"readonly"}}],"job_type":[{"_":"job","$":{"readonly":"readonly"}}],"group_ids":["6855"],"status":["completed"],"third_party_id":[""],"enable_hazards":["false"],"is_incident":["false"],"taggings":[{"$":{"type":"array"}}],"hazards":[{"$":{"type":"array"}}],"time_windows":[{"$":{"type":"array"}}],"required_capacity":[""],"negative_time_windows":["false"],"customer_name":["NRMA Insurance"],"template_name":["ICRT Insurance Company Ring In Transfer"],"customer_id":["5830889"],"customer_third_party_id":["vwork16885941928796637"],"worker_id":["74271"],"worker_third_party_id":[""],"worker_name":["905 MZ"],"worker_duration":["2.3"],"published_at":[{"_":"2023-11-13T23:42:02+00:00","$":{"type":"datetime"}}],"published_by":[{"_":"Dispatch Maria Lang","$":{"readonly":"readonly"}}],"has_pod":[{"_":"false","$":{"readonly":"readonly"}}],"signed_at":[{"$":{"readonly":"readonly"}}],"signed_by":[{"$":{"readonly":"readonly"}}],"paused_at":[""],"planned_duration":["43200"],"planned_start_at":[{"_":"2023-11-13T04:47:29+00:00","$":{"type":"datetime"}}],"planned_end_at":[{"_":"2023-11-13T16:47:29+00:00","$":{"type":"datetime","readonly":"readonly"}}],"actual_start_at":[{"_":"2023-11-13T23:43:15+00:00","$":{"type":"datetime","readonly":"readonly"}}],"actual_duration":[{"_":"8289","$":{"readonly":"readonly"}}],"alarm_at":[{"$":{"type":"datetime"}}],"requested_at":[{"$":{"type":"datetime"}}],"code":[""],"notes":[{"$":{"type":"array"},"note":[{"id":[{"_":"3787402","$":{"readonly":"readonly"}}],"body":["Notes - "],"created_by_user_id":[{"_":"72074","$":{"readonly":"readonly"}}],"created_at":[{"_":"2023-11-08T04:52:22+00:00","$":{"readonly":"readonly","type":"datetime"}}],"updated_at":[{"_":"2023-11-08T04:52:22+00:00","$":{"readonly":"readonly","type":"datetime"}}]}]}],"adhoc_alert_email_recipient":[{"name":[""],"email":[""]}],"adhoc_alert_sms_recipient":[{"name":[""],"mobile":["61 "]}],"equipments":[{"$":{"type":"array"}}],"steps":[{"$":{"type":"array"},"step":[{"id":[{"_":"115523414","$":{"readonly":"readonly"}}],"name":["On Route"],"completed_at":[{"_":"2023-11-13T23:43:15+00:00","$":{"type":"datetime"}}],"created_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"updated_at":[{"_":"2023-11-13T23:43:15+00:00","$":{"type":"datetime","readonly":"readonly"}}],"hazards_confirmation":[{"_":"false","$":{"type":"boolean"}}]},{"id":[{"_":"115523415","$":{"readonly":"readonly"}}],"name":["Vehicle on Board"],"completed_at":[{"_":"2023-11-14T02:01:23+00:00","$":{"type":"datetime"}}],"location":[{"id":[{"_":"127037575","$":{"type":"integer"}}],"created_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"updated_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"address1":[""],"address2":[""],"suburb":[""],"city":["Carlisle"],"post_code":["6101"],"country":["Australia"],"province":[""],"region":["AU"],"state":[""],"formatted_address":["BG Panel"],"lat":[{"_":"-31.988988","$":{"type":"decimal"}}],"lng":[{"_":"115.917907","$":{"type":"decimal"}}],"deleted_at":[{"$":{"type":"datetime"}}]}],"created_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"updated_at":[{"_":"2023-11-14T02:01:23+00:00","$":{"type":"datetime","readonly":"readonly"}}],"hazards_confirmation":[{"_":"false","$":{"type":"boolean"}}]},{"id":[{"_":"115523416","$":{"readonly":"readonly"}}],"name":["Delivered to Destination"],"completed_at":[{"_":"2023-11-14T02:01:24+00:00","$":{"type":"datetime"}}],"location":[{"id":[{"_":"127037576","$":{"type":"integer"}}],"created_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"updated_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"address1":[""],"address2":[""],"suburb":[""],"city":["Bibra Lake"],"post_code":["6163"],"country":["Australia"],"province":[""],"region":["AU"],"state":[""],"formatted_address":["Pickles"],"lat":[{"_":"-32.095761","$":{"type":"decimal"}}],"lng":[{"_":"115.806365","$":{"type":"decimal"}}],"deleted_at":[{"$":{"type":"datetime"}}]}],"created_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"updated_at":[{"_":"2023-11-14T02:01:25+00:00","$":{"type":"datetime","readonly":"readonly"}}],"hazards_confirmation":[{"_":"false","$":{"type":"boolean"}}]}]}],"sections":[{"$":{"type":"array"},"section":[{"title":["Dispatch Details"],"custom_fields":[{"$":{"type":"array"},"custom_field":[{"name":["Driver Image ID"],"value":[""],"type":["pick_list"],"pick_list_id":["143031"],"permission":["hidden"]},{"name":["Name of Auth Person"],"value":["Lee"],"type":["text"],"permission":["required"]},{"name":["Comp of Auth"],"value":["NRMA"],"type":["text"],"permission":["required"]},{"name":["Vehicle Good to Go"],"value":["Ready to go"],"type":["text"],"permission":["required"]}]}]},{"title":["Vehicle Details/Tow Authority"],"custom_fields":[{"$":{"type":"array"},"custom_field":[{"name":["Vehicle Type"],"values":[{"$":{"type":"array"},"value":["Car 4WD"]}],"type":["multi_pick_list"],"pick_list_id":["144389"],"permission":["required"]},{"name":["Rego"],"value":["1END171"],"type":["text"],"permission":["required"]},{"name":["Colour"],"value":["Blue"],"type":["pick_list"],"pick_list_id":["143033"],"permission":["required"]},{"name":["Make"],"value":["Jeep"],"type":["pick_list"],"pick_list_id":["145332"],"permission":["required"]},{"name":["Model"],"value":["Cherokee"],"type":["pick_list"],"pick_list_id":["143032"],"permission":["required"]},{"name":["Yard for Storage"],"values":[{"$":{"type":"array"},"value":["No Yard Required"]}],"type":["multi_pick_list"],"pick_list_id":["144879"],"permission":["required"]},{"name":["Additional Items Towed"],"value":[""],"type":["pick_list"],"pick_list_id":["146139"],"permission":["optional"]},{"name":["Signed Tow Authority"],"value":["https://api-nogzip.vworkapp.com/api/v5/attachments/file.jpg"],"attachment_id":["18662214"],"type":["image"],"permission":["optional"]},{"name":["Terms & Conditions"],"value":["https://tinyurl.com/3m94xyhh"],"type":["link"],"permission":["read_only"]},{"name":["Keys"],"values":[{"$":{"type":"array"},"value":["Yes"]}],"type":["multi_pick_list"],"pick_list_id":["142531"],"permission":["required"]},{"name":["Signature for Terms"],"value":[{"signature":[{"signed_by":[""],"signed_at":[{"$":{"type":"datetime"}}],"image":[""]}]}],"type":["signature"],"permission":["optional"]},{"name":["Location"],"lat":["0.0"],"lng":["0.0"],"formatted_address":[""],"type":["location"],"permission":["optional"]}]}]},{"title":["Vehicle Images "],"custom_fields":[{"$":{"type":"array"},"custom_field":[{"name":["Photo - Front"],"value":["https://api-nogzip.vworkapp.com/api/v5/attachments/file.jpg"],"attachment_id":["18659465"],"type":["image"],"permission":["required"]},{"name":["Photo - Side 1"],"value":["https://api-nogzip.vworkapp.com/api/v5/attachments/18659467.jpg"],"attachment_id":["18659467"],"type":["image"],"permission":["required"]},{"name":["Photo - Back"],"value":["https://api-nogzip.vworkapp.com/api/v5/attachments/18659472.jpg"],"attachment_id":["18659472"],"type":["image"],"permission":["required"]},{"name":["Photo - Side 2"],"value":["https://api-nogzip.vworkapp.com/api/v5/attachments/18659476.jpg"],"attachment_id":["18659476"],"type":["image"],"permission":["required"]},{"name":["Photo 4"],"value":[""],"type":["image"],"permission":["optional"]},{"name":["Photo 5"],"value":[""],"type":["image"],"permission":["optional"]},{"name":["Photo 6"],"value":[""],"type":["image"],"permission":["optional"]},{"name":["Photo 7"],"value":[""],"type":["image"],"permission":["optional"]},{"name":["Photo 8"],"value":[""],"type":["image"],"permission":["optional"]},{"name":["Photo 9"],"value":[""],"type":["image"],"permission":["optional"]}]}]},{"title":["Job Details Complete (Admin Section Only)"],"custom_fields":[{"$":{"type":"array"},"custom_field":[{"name":["Yard Zone"],"values":[{"$":{"type":"array"}}],"type":["multi_pick_list"],"pick_list_id":["146022"],"permission":["hidden"]},{"name":["VI Number"],"value":[""],"type":["text"],"permission":["optional"]},{"name":["Claim Number"],"value":["NRP1"],"type":["text"],"permission":["required"]},{"name":["Release Documents"],"value":[""],"type":["link"],"permission":["hidden"]},{"name":["Printing Status"],"value":["Printed"],"type":["pick_list"],"pick_list_id":["144880"],"permission":["hidden"]},{"name":["Job Details Complete"],"value":["1"],"type":["checkbox"],"permission":["hidden"]}]}]}]}],"invoice":[{"id":["5585103"],"number":["INV-3476"],"description":["Job Date: 14th Nov 2023\r\nJob Type: ICRT Insurance Company Ring In Transfer\r\nWorker Name 905 MZ\r\nContact Name:     \r\nClaim Number: NRP1.    \r\nRego: 1END171\r\nMake: Jeep \r\nModel: Cherokee\r\nPick Up Location: BG Panel\r\nDrop Off Location: Pickles,  Australia\r\n\r\nOwner's Name: \r\nOwner's Ph No: 61 \r\nOwner's Address: \r\n"],"tax_rate":["10.0"],"pricebook_id":["1792"],"line_items":[{"$":{"type":"array"},"line_item":[{"id":["17974380"],"code":[""],"description":["905 MZ Distance(km) []"],"unit_cost":["0.0"],"requested_quantity":["0.0"],"actual_quantity":["15.8585036121208"]},{"id":["17980732"],"code":["IAG - Class 1 Transfer Tow"],"description":["INSURANCE GROUP - Class 1 (Up to 2030 Kgs), Transfer Tow / Second Tow (10 Kilometres Free)"],"unit_cost":["90.0"],"requested_quantity":["0.0"],"actual_quantity":["1.0"]},{"id":["17980733"],"code":["IAG - Class 1 Excess Kms"],"description":["INSURANCE AUSTRALIA GROUP - Class 1 (Up to 2030 Kgs), Excess Kilometres (10 Kilometres Free)"],"unit_cost":["3.0"],"requested_quantity":["22.0"],"actual_quantity":["12.0"]}]}]}],"created_at":[{"_":"2023-11-08T04:52:21+00:00","$":{"type":"datetime","readonly":"readonly"}}],"created_by":[{"_":"Dispatcher Ian","$":{"readonly":"readonly"}}],"updated_at":[{"_":"2023-11-14T06:49:24+00:00","$":{"type":"datetime","readonly":"readonly"}}]}

Does that help?

12

The path to the data item you want would also be needed. otherwise i would need to guess that to.

guess to correct property
$$.payload.**.custom_field[*][$.name[0] = "Rego"].value[0]
or

$$.payload.**[*][$.name[0] = "Rego"].value[0]

13

That worked!... Thank you so much!

Learning so much

Appreciate your help!

14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.