ADF Pipeline format datetime from XML source

Question

I have an Azure Data Factory pipeline with an xml file as a source. The target sink is an Oracle table containing a datetime column.

The mapping for most of the columns has been simple, as shown below:

My problem is mapping to "SupplyDate"

This needs to come from a source column with the following path:

$[\'ns0:PayReqInvoice\'][\'Invoices\'][\'Invoice\'][0][\'DateOfSupply\']

I've tried using the "Add dynamic content" editor to wrap with the @formatDateTime function as follows:

But this gives an "Unrecognized expression" exception.

Where did I go wrong?

I've also experimented implementing using data flows but that won't let me select my target Oracle dataset.

Answer 1

Hello @Rob Bowman and welcome to Microsoft Q&A.

I see you are having trouble in the mapping of your copy. You are trying to do a change of date format inside the mapping.

I think I know what went wrong. In my experience, I can use a reference starting with $ , or I can use a function starting with @ , but they do not mix in my experience. I wish they did sometimes, mixing would make REST pagination much more versatile.

I suspect behind the scenes there are two different parsers, and which one to use is decided by the starting character.
I also don't recall using @formatDateTime in mapping expression before. I'm not saying it won't work, I just don't recall trying it. Does it work in other scenarios for you? If it does, then I have an idea, go to the Copy Activity's source settings, and find "Additional columns" at the bottom. Use the duplicate column option to give a name to that path. The idea behind this, it to allow you in the mapping do @formatDateTime(MyNewColumn) , this way avoiding puting the path inside the function and getting the error.

If this does not work, and if neither Data Factory nor Oracle will do the conversion for you, then we can try another workaround. XML -> Delimited Text -> Oracle . The point behind this, it to leverage the type conversion settings available in Delimited Text, to help parse the incoming date formats. Oracle formats are fixed so nothing needed on that side, right?

Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators