question

DAmicoFrancesco-8795 avatar image
0 Votes"
DAmicoFrancesco-8795 asked MartinJaffer-MSFT answered

xml source to Db sink mapping issue with 1 or more Elements in structure

I have an issue with the copy data activity. Scenario input is xml to db sproc tabletype sink.
I noticed it works fine as long as we have 0 or 2+ elements, if we have exactly one child element in the source Xml it is not mapped and send to the DB.
it looks like internally in the ADF the xml is converted into Json, but if the xml has only one child it dose not convert it to an array, but the mapping requires it to be a collection.



Mapping is like
element
item -> [] array Collection reference is checked

this dose not work if item is 1, it will not throw an error , but will not map the item.

Can we somehow adjust the mapping, to allow a collection or a single element both?
Can we influence the internal conversion from xml to json to force it to use an array?




azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @DAmicoFrancesco-8795 and welcome to Microsoft Q&A. Thank you for your detailed description and insight.

Could you please give an example file we can reproduce the error with? Redact any sensitive information.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

@DAmicoFrancesco-8795 I heard back from the product group. The response was not what I expected.

This is by design for copy at moment as xml detect multiple elements as array, while single element as object. The collectionReference can only work with array. However, without collection reference, it will work for both array and object.

If we must use collectionReference with single element, we can use dataflow flatten transformation for now, dataflow can define the schema in source and force to read single element as array, just make sure we import source projection with xml file with multiple elements.

Does this help at all?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DAmicoFrancesco-8795 avatar image
0 Votes"
DAmicoFrancesco-8795 answered MartinJaffer-MSFT commented

Hi, thanks for the reply @MartinJaffer-MSFT
some examples:

with 1 item

 <ns0:PersistItemMaster xmlns:ns0="http://ESB.Services.XXX">
     <ns0:ItemMasters>
         <SiteId>XX</SiteId>
         <ItemMasterRows>
             <ItemMaster>
                 <PluCode>1000000000001</PluCode>
                 <PluName>PluNameX</PluName>
             </ItemMaster>
         </ItemMasterRows>
     </ns0:ItemMasters>
 </ns0:PersistItemMaster>

With 2:

 <ns0:PersistItemMaster xmlns:ns0="http://ESB.Services.XXX">
         <ns0:ItemMasters>
             <SiteId>XX</SiteId>
             <ItemMasterRows>
                 <ItemMaster>
                     <PluCode>1000000000001</PluCode>
                     <PluName>PluNameX</PluName>
                 </ItemMaster>
                   <ItemMaster>
                     <PluCode>1000000000002</PluCode>
                     <PluName>PluNameX</PluName>
                 </ItemMaster>
             </ItemMasterRows>
         </ns0:ItemMasters>
     </ns0:PersistItemMaster>


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for bringing this to our attention, @DAmicoFrancesco-8795 . I have reproduced the issue, and agree with your assessment. In my opinion, this is a product defect. I will bring it to the developer's attention, and let you know when I hear back.

0 Votes 0 ·