xml mapping in ADF for xml file

Question

i'm hitting an url through ADF from that url i'm getting one xml response. From that xml i need to extract few values.

url-https://registers.esma.europa.eu/solr/esma_registers_fitrs_files/select?q=Delta&fq=creation_date:%5B2021-10-18T00:00:00Z+TO+2021-10-18T23:59:59Z%5D&wt=xml&indent=true&start=0&rows=100

PFA, XML response:143021-filelist-20211022.xml

please help me providing the mapping that needs to be passed in copy activity to get the hightlighted fields in the screnshot as a csv file (ss.png)

Answer 1

Hi @nishi hatekar , Did you try collectionReference in the copy activity 's mapping tab? Thanks

Answer 2

Hello @nishi hatekar ,

Thanks for the question and using MS Q&A platform.

From the doc here , the attribute name is parsed as @attributename and value as _value_

Output :

On the Copy Data, you will get the attributeName & attribute value.

You will have to normalize this tabular data using the dataflow.

AttributeName : Name  
AttributeValue : Value  

TO

Name : Value  

You'll have to use Pivot function within Dataflow to achieve the Above Transformation.

Note :
Unfortunately, you cannot import or REST API as source in the Data Flow. So, you need it do as two steps

Hope this will help. Please let us know if any further queries.

------------------------------

• 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