question

nishihatekar-3636 avatar image
0 Votes"
nishihatekar-3636 asked SathyamoorthyVijayakumar-MSFT commented

xml mapping in ADF for xml file

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) 143031-ss.png


azure-data-factoryazure-databricks
ss.png (64.5 KiB)
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.

Samy-7940 avatar image
0 Votes"
Samy-7940 answered

Hi @nishihatekar-3636, Did you try collectionReference in the copy activity 's mapping tab? Thanks

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.

SathyamoorthyVijayakumar-MSFT avatar image
0 Votes"
SathyamoorthyVijayakumar-MSFT answered SathyamoorthyVijayakumar-MSFT commented

Hello @nishihatekar-3636,

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_

143471-image.png

Output :

143407-image.png

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.



image.png (23.6 KiB)
image.png (65.5 KiB)
· 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 @nishihatekar-3636,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·