'ADF Pipeline format datetime from XML source

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.

Please see a sample of failing xml below:

<?xml version="1.0"?>
<ns0:PayReqInvoice xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="https://acme.smartplatform.net/">
  <Invoices>
    <Invoice>
      <SupplierReference>123456</SupplierReference>
      <SupplierInvoiceReference>JH Test Shay OT 2</SupplierInvoiceReference>
      <RecipientInvoiceReference>S124366</RecipientInvoiceReference>
      <DateInvoiceIssued>10/5/2021</DateInvoiceIssued>
      <InvoiceNetTotal>20.00</InvoiceNetTotal>
      <InvoiceVATTotal>5.00</InvoiceVATTotal>
      <DateOfSupply>30/09/2021</DateOfSupply>
      <InvoiceYear>2021</InvoiceYear>
      <InvoiceDetails>
        <InvoiceDetail>
          <ItemDescription>Paying journey Id 923534 user job 1188237</ItemDescription>
          <NetCharge>10.00</NetCharge>
          <RevenueAccountCode>
            <Costcentre>12345</Costcentre>
            <SubCostcentre1>678</SubCostcentre1>
            <SubCostcentre2>901</SubCostcentre2>
            <Votecode>234</Votecode>
            <Subcode>11</Subcode>
          </RevenueAccountCode>
        </InvoiceDetail>
        <InvoiceDetail>
          <ItemDescription>Paying journey Id 923534 user job 1188238</ItemDescription>
          <NetCharge>10.00</NetCharge>
          <RevenueAccountCode>
            <Costcentre>12345</Costcentre>
            <SubCostcentre1>678</SubCostcentre1>
            <SubCostcentre2>901</SubCostcentre2>
            <Votecode>234</Votecode>
            <Subcode>11</Subcode>
          </RevenueAccountCode>
        </InvoiceDetail>
      </InvoiceDetails>
    </Invoice>
    <Invoice>
      <SupplierReference>123456</SupplierReference>
      <SupplierInvoiceReference>Shay OT test3</SupplierInvoiceReference>
      <RecipientInvoiceReference>S124366</RecipientInvoiceReference>
      <DateInvoiceIssued>10/7/2021</DateInvoiceIssued>
      <InvoiceNetTotal>20.00</InvoiceNetTotal>
      <InvoiceVATTotal>5.00</InvoiceVATTotal>
      <DateOfSupply>01/10/2021</DateOfSupply>
      <InvoiceYear>2021</InvoiceYear>
      <InvoiceDetails>
        <InvoiceDetail>
          <ItemDescription>Paying journey Id 923535 user job 1188239</ItemDescription>
          <NetCharge>10.00</NetCharge>
          <RevenueAccountCode>
            <Costcentre>12345</Costcentre>
            <SubCostcentre1>678</SubCostcentre1>
            <SubCostcentre2>901</SubCostcentre2>
            <Votecode>234</Votecode>
            <Subcode>11</Subcode>
          </RevenueAccountCode>
        </InvoiceDetail>
        <InvoiceDetail>
          <ItemDescription>Paying journey Id 923535 user job 1188240</ItemDescription>
          <NetCharge>10.00</NetCharge>
          <RevenueAccountCode>
            <Costcentre>12345</Costcentre>
            <SubCostcentre1>678</SubCostcentre1>
            <SubCostcentre2>901</SubCostcentre2>
            <Votecode>234</Votecode>
            <Subcode>11</Subcode>
          </RevenueAccountCode>
        </InvoiceDetail>
      </InvoiceDetails>
    </Invoice>
  </Invoices>
</ns0:PayReqInvoice>

azure-data-factory

Solution 1:^[1]

In copy data activity Additional columns, you cannot pass the dynamic expression to use functions with the existing column.

Using dynamic content, you can only add the system variables, functions, pipeline variables, and parameters.

You can use $$COLUMN in the Additional columns and select the existing column as is but cannot add expressions and functions to this existing column dynamically.

Currently, there is no option to change the datatype of XML source data using copy data activity.

You can change the format after loading it to sink or You can try using dataflow activity to parse the source data and using derived column transformation to change the datatype and copy it to sink.

Refer to this MS document for more information.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	NiharikaMoola-MT

'ADF Pipeline format datetime from XML source

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]