Xpath One To Many Update

0 Views

Like ( 0 )

Report

Posted by takolota1

4,740

Want to more efficiently update from one data-set to another?
Have single records in your updated source data-set that need to update multiple records in your destination data-set?
Working on a batch update workflow, found you can batch send data to your data-set, but you can't efficiently generate the updated data for your batch call without many Apply to each loops & queries?

With some Xpath processing there is a way to get from this...

Updated Source:

[
  {"SourceId": 1, "SourceTitle": "Advil", "SourceCode": "A", "SourceDate": "2024-01-01"},
  {"SourceId": 2, "SourceTitle": "Tums", "SourceCode": "B", "SourceDate": "2024-01-02"},
  {"SourceId": 3, "SourceTitle": "Aspirin", "SourceCode": "C", "SourceDate": "2024-01-03"}
]

Destination:

[
  {"DestinationId": 5, "DestinationTitle": "Advil", "DestinationCode": null, "DestinationDate": null},
  {"DestinationId": 6, "DestinationTitle": "Advil", "DestinationCode": null, "DestinationDate": null},
  {"DestinationId": 7, "DestinationTitle": "Advil", "DestinationCode": null, "DestinationDate": null},
  {"DestinationId": 8, "DestinationTitle": "Tums", "DestinationCode": null, "DestinationDate": null},
  {"DestinationId": 9, "DestinationTitle": "Tums", "DestinationCode": null, "DestinationDate": null},
  {"DestinationId": 10, "DestinationTitle": "Aspirin", "DestinationCode": null, "DestinationDate": null}
]

To this...

Updated Destination:

[
  {"DestinationId": 5, "DestinationTitle": "Advil", "DestinationCode": "A", "DestinationDate": "2024-01-01"},
  {"DestinationId": 6, "DestinationTitle": "Advil", "DestinationCode": "A", "DestinationDate": "2024-01-01"},
  {"DestinationId": 7, "DestinationTitle": "Advil", "DestinationCode": "A", "DestinationDate": "2024-01-01"},
  {"DestinationId": 8, "DestinationTitle": "Tums", "DestinationCode": "B", "DestinationDate": "2024-01-02"},
  {"DestinationId": 9, "DestinationTitle": "Tums", "DestinationCode": "B", "DestinationDate": "2024-01-02"},
  {"DestinationId": 10, "DestinationTitle": "Aspirin", "DestinationCode": "C", "DestinationDate": "2024-01-03"}
]

... without using any Apply to each loops.

You can import the template with a sample data set using one of the methods at the bottom of the post. You can immediately test it with the sample Source, Destination, key column names settings, & Updated destination data mappings.
And/Or to use this for your own data, you can switch the sample Source & Destination data with dynamic content to the JSON arrays of your source & destination data, switch the DestinationKeyColumnNames & SourceKeyColumnNames to your key column names, & set the Select Updated destination data action mapping to fit your destination column names & updated source value references.

Thanks for any feedback,

Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).

And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.

Solution Zip Download Link: https://drive.google.com/file/d/17gEL7U2O_lWlvpS5k0hepO7Ek-9st74N/view?usp=sharing

Power Automate Legacy Import: https://drive.google.com/file/d/1gjyOgaO8o-swLsCZDzSlzxdcUOO5bJP6/view?usp=sharing

Code to copy & paste into a new action's My clipboard in the Classic Designer:

{"id":"a89dc0c1-0f4d-430d-a4ea-486d1d9f12d5","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Xpath_One_To_Many_Update","operationDefinition":{"type":"Scope","actions":{"Root":{"type":"Compose","inputs":{"root":{"values":"@outputs('Source')"}},"runAfter":{"Source":["Succeeded"]},"description":"Format destination data into JSON ready to convert to XML","metadata":{"operationMetadataId":"bf5bba38-67e5-476b-99ff-9064445783e2"}},"XML":{"type":"Compose","inputs":"@xml(outputs('Root'))","runAfter":{"Root":["Succeeded"]},"metadata":{"operationMetadataId":"2c27d4b7-252e-48ac-99c9-a51fa8b2bf26"}},"Select":{"type":"Select","inputs":{"from":"@outputs('Destination')","select":{"Destination":"@item()","Source":"@if(empty(outputs('settings')['DestinationKeyColumnName2']),\r\n\r\nif(\r\n    empty(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], '''])'))), \r\n        json('{}'), \r\n        json(xml(join(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], '''])[', if(equals('last', toLower(outputs('settings')['MatchFirstOrLastSource'])),'last()', 1), ']')), '')))?['values']\r\n),\r\nif(\r\n    empty(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], ''' and ', outputs('settings')['SourceKeyColumnName2'], '=''', item()?[outputs('settings')['DestinationKeyColumnName2']], '''])'))), \r\n    json('{}'),\r\n    json(xml(join(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], ''' and ', outputs('settings')['SourceKeyColumnName2'], '=''', item()?[outputs('settings')['DestinationKeyColumnName2']], '''])[', if(equals('last', toLower(outputs('settings')['MatchFirstOrLastSource'])),'last()', 1), ']')), '')))?['values']\r\n)\r\n)"}},"runAfter":{"XML":["Succeeded"]},"description":"Use xpath( ) to append destination data /w 1st or last source XML records that match a query.","metadata":{"operationMetadataId":"922ebb9c-427c-4198-bb5c-d838ad6723fd"}},"Select_Updated_destination_data":{"type":"Select","inputs":{"from":"@body('Select')","select":{"DestinationId":"@item()?['Destination']?['DestinationId']","DestinationColumnName":"SourceValue","DestinationTitle":"@item()?['Source']?['SourceTitle']","DestinationCode":"@item()?['Source']?['SourceCode']","DestinationDate":"@item()?['Source']?['SourceDate']"}},"runAfter":{"Select":["Succeeded"]},"description":"Destination column names on the left & dynamic content for their new values from source data on the right. But you'll want to keep the destination primary key/ID value. For source values use item()?['Source']?['InsertSourceColumnName']","metadata":{"operationMetadataId":"bbe3a0ef-2827-4b49-b41b-b1caa26e0d12"}},"Source":{"type":"Compose","inputs":[{"SourceId":0,"SourceTitle":"Advil","SourceCode":"A","SourceDate":"2024-01-01"},{"SourceId":1,"SourceTitle":"Advil","SourceCode":"A2","SourceDate":"2024-02-01"},{"SourceId":2,"SourceTitle":"Tums","SourceCode":"B","SourceDate":"2024-01-02"},{"SourceId":3,"SourceTitle":"Aspirin","SourceCode":"C","SourceDate":"2024-01-03"},{"SourceId":4,"SourceTitle":"TestSourceNoMatchTitle","SourceCode":"C","SourceDate":"2024-01-04"},{"SourceId":5,"SourceTitle":"TestSourceNoMatchTitle2","SourceCode":"C","SourceDate":"2024-01-05"}],"runAfter":{"Destination":["Succeeded"]},"description":" To use this for other data, enter dynamic content for a JSON array of all your updated source values","metadata":{"operationMetadataId":"11ef5e2c-93af-4221-bdc5-44083125a026"}},"settings":{"type":"Compose","inputs":{"DestinationKeyColumnName":"DestinationTitle","DestinationKeyColumnName2":"","SourceKeyColumnName":"SourceTitle","SourceKeyColumnName2":"","MatchFirstOrLastSource":"last"},"runAfter":{},"description":"Key columns used to determine which source items update which destination items. 2nd Column names enable matching up items only where both source & both destination columns match. MatchFirstOrLastSource get the \"first\" or \"last\" matching source item","trackedProperties":{"meta":{"type":"SP.Data.@{outputs('settings')?['listName']}ListItem"},"batchGUID":"@{guid()}","changeSetGUID":"@{guid()}"},"metadata":{"operationMetadataId":"459dc501-ad7e-4252-8540-df307a69607f"}},"Destination":{"type":"Compose","inputs":[{"DestinationId":5,"DestinationTitle":"Advil","DestinationCode":null,"DestinationDate":null},{"DestinationId":6,"DestinationTitle":"Advil","DestinationCode":null,"DestinationDate":null},{"DestinationId":7,"DestinationTitle":"Advil","DestinationCode":null,"DestinationDate":null},{"DestinationId":8,"DestinationTitle":"Tums","DestinationCode":null,"DestinationDate":null},{"DestinationId":9,"DestinationTitle":"Tums","DestinationCode":null,"DestinationDate":null},{"DestinationId":10,"DestinationTitle":"Aspirin","DestinationCode":null,"DestinationDate":null},{"DestinationId":11,"DestinationTitle":"TestDestinationNoMatchTitle","DestinationCode":null,"DestinationDate":null}],"runAfter":{"settings":["Succeeded"]},"description":" To use this for other data, enter dynamic content for a JSON array of all your destination values","metadata":{"operationMetadataId":"362ebcba-0149-4194-bcdb-15a890d6fbef"}},"Select_Destination_keys":{"type":"Select","inputs":{"from":"@outputs('Destination')","select":"@if(empty(outputs('settings')['DestinationKeyColumnName2']),\r\nitem()[outputs('settings')['DestinationKeyColumnName']],\r\nconcat(item()[outputs('settings')['DestinationKeyColumnName']],item()[outputs('settings')['DestinationKeyColumnName2']])\r\n)"},"runAfter":{"Select":["Succeeded"]},"metadata":{"operationMetadataId":"b27fbb32-fb83-4597-a3ed-098beea086a5"}},"Filter_array_Source_records_not_in_destination":{"type":"Query","inputs":{"from":"@outputs('Source')","where":"@not(contains(body('Select_Destination_keys'), if(empty(outputs('settings')['SourceKeyColumnName2']), item()[outputs('settings')['SourceKeyColumnName']], concat(item()[outputs('settings')['SourceKeyColumnName']], item()[outputs('settings')['SourceKeyColumnName2']]))))"},"runAfter":{"Select_Destination_keys":["Succeeded"]},"metadata":{"operationMetadataId":"4005a7e4-d07d-4450-ae56-90d81468cc65"}}},"runAfter":{},"description":"For other data, enter key column names in settings, dynamic content for a JSON array of all your destination values in Destination, & of all your Source values in Source. Enter new column names & values in Updated destination data action.","metadata":{"operationMetadataId":"523232c8-465c-423a-a457-f31b339440f3"}}}

Code to copy & paste into the New Designer:

{"nodeId":"Xpath_One_To_Many_Update-copy","serializedOperation":{"type":"Scope","description":"For other data, enter key column names in settings, dynamic content for a JSON array of all your destination values in Destination, & of all your Source values in Source. Enter new column names & values in Updated destination data action.","actions":{"Root":{"type":"Compose","description":"Format destination data into JSON ready to convert to XML","inputs":{"Root":{"values":"@outputs('Source')"}},"runAfter":{"Source":["Succeeded"]},"metadata":{"operationMetadataId":"bf5bba38-67e5-476b-99ff-9064445783e2"}},"XML":{"type":"Compose","inputs":"@xml(outputs('Root'))","runAfter":{"Root":["Succeeded"]},"metadata":{"operationMetadataId":"2c27d4b7-252e-48ac-99c9-a51fa8b2bf26"}},"Select":{"type":"Select","description":"Use xpath( ) to append destination data /w 1st or last source XML records that match a query.","inputs":{"from":"@outputs('Destination')","select":{"Destination":"@item()","Source":"@if(empty(outputs('settings')['DestinationKeyColumnName2']),\r\n\r\nif(\r\n    empty(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], '''])'))), \r\n        json('{}'), \r\n        json(xml(join(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], '''])[', if(equals('last', toLower(outputs('settings')['MatchFirstOrLastSource'])),'last()', 1), ']')), '')))?['values']\r\n),\r\nif(\r\n    empty(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], ''' and ', outputs('settings')['SourceKeyColumnName2'], '=''', item()?[outputs('settings')['DestinationKeyColumnName2']], '''])'))), \r\n    json('{}'),\r\n    json(xml(join(xpath(outputs('XML'), concat('(//values[', outputs('settings')['SourceKeyColumnName'], '=''', item()?[outputs('settings')['DestinationKeyColumnName']], ''' and ', outputs('settings')['SourceKeyColumnName2'], '=''', item()?[outputs('settings')['DestinationKeyColumnName2']], '''])[', if(equals('last', toLower(outputs('settings')['MatchFirstOrLastSource'])),'last()', 1), ']')), '')))?['values']\r\n)\r\n)"}},"runAfter":{"XML":["Succeeded"]},"metadata":{"operationMetadataId":"922ebb9c-427c-4198-bb5c-d838ad6723fd"}},"Select_Updated_destination_data":{"type":"Select","description":"Destination column names on the left & dynamic content for their new values from source data on the right. But you'll want to keep the destination primary key/ID value. For source values use item()?['Source']?['InsertSourceColumnName']","inputs":{"from":"@body('Select')","select":{"DestinationId":"@item()?['Destination']?['DestinationId']","DestinationColumnName":"SourceValue","DestinationTitle":"@item()?['Source']?['SourceTitle']","DestinationCode":"@item()?['Source']?['SourceCode']","DestinationDate":"@item()?['Source']?['SourceDate']"}},"runAfter":{"Select":["Succeeded"]},"metadata":{"operationMetadataId":"bbe3a0ef-2827-4b49-b41b-b1caa26e0d12"}},"Source":{"type":"Compose","description":" To use this for other data, enter dynamic content for a JSON array of all your updated source values","inputs":[{"SourceId":0,"SourceTitle":"Advil","SourceCode":"A","SourceDate":"2024-01-01"},{"SourceId":1,"SourceTitle":"Advil","SourceCode":"A2","SourceDate":"2024-02-01"},{"SourceId":2,"SourceTitle":"Tums","SourceCode":"B","SourceDate":"2024-01-02"},{"SourceId":3,"SourceTitle":"Aspirin","SourceCode":"C","SourceDate":"2024-01-03"},{"SourceId":4,"SourceTitle":"TestSourceNoMatchTitle","SourceCode":"C","SourceDate":"2024-01-04"},{"SourceId":5,"SourceTitle":"TestSourceNoMatchTitle2","SourceCode":"C","SourceDate":"2024-01-05"}],"runAfter":{"Destination":["Succeeded"]},"metadata":{"operationMetadataId":"11ef5e2c-93af-4221-bdc5-44083125a026"}},"settings":{"type":"Compose","description":"Key columns used to determine which source items update which destination items. 2nd Column names enable matching up items only where both source & both destination columns match. MatchFirstOrLastSource get the \"first\" or \"last\" matching source item","inputs":{"DestinationKeyColumnName":"DestinationTitle","DestinationKeyColumnName2":"","SourceKeyColumnName":"SourceTitle","SourceKeyColumnName2":"","MatchFirstOrLastSource":"last"},"trackedProperties":{"meta":{"type":"SP.Data.@{outputs('settings')?['listName']}ListItem"},"batchGUID":"@{guid()}","changeSetGUID":"@{guid()}"},"metadata":{"operationMetadataId":"459dc501-ad7e-4252-8540-df307a69607f"}},"Destination":{"type":"Compose","description":" To use this for other data, enter dynamic content for a JSON array of all your destination values","inputs":[{"DestinationId":5,"DestinationTitle":"Advil","DestinationCode":null,"DestinationDate":null},{"DestinationId":6,"DestinationTitle":"Advil","DestinationCode":null,"DestinationDate":null},{"DestinationId":7,"DestinationTitle":"Advil","DestinationCode":null,"DestinationDate":null},{"DestinationId":8,"DestinationTitle":"Tums","DestinationCode":null,"DestinationDate":null},{"DestinationId":9,"DestinationTitle":"Tums","DestinationCode":null,"DestinationDate":null},{"DestinationId":10,"DestinationTitle":"Aspirin","DestinationCode":null,"DestinationDate":null},{"DestinationId":11,"DestinationTitle":"TestDestinationNoMatchTitle","DestinationCode":null,"DestinationDate":null}],"runAfter":{"settings":["Succeeded"]},"metadata":{"operationMetadataId":"362ebcba-0149-4194-bcdb-15a890d6fbef"}},"Select_Destination_keys":{"type":"Select","inputs":{"from":"@outputs('Destination')","select":"@if(empty(outputs('settings')['DestinationKeyColumnName2']),\r\nitem()[outputs('settings')['DestinationKeyColumnName']],\r\nconcat(item()[outputs('settings')['DestinationKeyColumnName']],item()[outputs('settings')['DestinationKeyColumnName2']])\r\n)"},"runAfter":{"Select":["Succeeded"]},"metadata":{"operationMetadataId":"b27fbb32-fb83-4597-a3ed-098beea086a5"}},"Filter_array_Source_records_not_in_destination":{"type":"Query","inputs":{"from":"@outputs('Source')","where":"@not(contains(body('Select_Destination_keys'), if(empty(outputs('settings')['SourceKeyColumnName2']), item()[outputs('settings')['SourceKeyColumnName']], concat(item()[outputs('settings')['SourceKeyColumnName']], item()[outputs('settings')['SourceKeyColumnName2']]))))"},"runAfter":{"Select_Destination_keys":["Succeeded"]},"metadata":{"operationMetadataId":"4005a7e4-d07d-4450-ae56-90d81468cc65"}}},"runAfter":{},"metadata":{"operationMetadataId":"523232c8-465c-423a-a457-f31b339440f3"}},"allConnectionData":{},"staticResults":{},"isScopeNode":true,"mslaNode":true}

code snippet widget

Categories:

AI Builder

Comments

takolota1 4,740
Posted at

Xpath One To Many Update

Description

Placeholder

Like (0)

Report