This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 35%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hi,
Could you tell me if ADF can read/write and output data in XML ? Is this the best practice to do that ?
Or we should try databricks or python code to do that as best approach.
Please could you explain in detail the steps and the best method to do this.
So what is the best approach? Should we use azure databricks and how ?
Hi - looking at the below comment I am confused as other documents suggested that sink data is supported in ADF.
What is the best option shall we use databricks?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 61%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Thanks for reaching MS Q&A
Azure Data Factory (ADF) can indeed read XML data from various connectors like Amazon S3, Azure Blob, Azure Data Lake Storage, FTP, and more. However, it’s important to note that ADF supports XML as a source but not as a sink. This means you can read XML data into ADF, but you cannot write or output data in XML format directly using ADF.
As for whether this is the best practice, it depends on your specific use case. If you only need to read XML data and transform it within ADF, then using ADF could be a good approach. However, if you need to write or output data in XML format, you might need to consider other options.
One such option could be using Databricks or Python. PySpark, for example, provides support for reading and writing XML files using the spark-xml package. This package is an external package developed by Databricks and can be used to read XML files into PySpark DataFrames and write PySpark DataFrames to XML files.
Here are the general steps to read and write XML files using PySpark:
Python
from pyspark.sql import SparkSession
# Create a SparkSession
spark = SparkSession.builder.getOrCreate()
# Read XML file into a DataFrame
df = spark.read.format('com.databricks.spark.xml').options(rowTag='yourRowTag').load('yourFile.xml')
# Perform your transformations...
# Write DataFrame to an XML file
df.write.format('com.databricks.spark.xml').option('rootTag', 'yourRootTag').option('rowTag', 'yourRowTag').save('yourOutputFile.xml')
In this code:
'yourRowTag' should be replaced with the tag that wraps each record in your XML file.'yourFile.xml' and 'yourOutputFile.xml' should be replaced with the paths to your input and output XML files, respectively.'yourRootTag' should be replaced with the root tag for your output XML file.Remember to install the spark-xml package and ensure it’s available in your Spark environment before running the code.
This approach gives you the flexibility of PySpark and Python for data transformation and allows you to read and write XML data. However, the best method ultimately depends on your specific requirements, the complexity of your data transformation, and the environment in which you’re working. It’s always a good idea to evaluate different approaches and choose the one that best fits your needs.
Hope this helps. Do let us know if you any further queries.
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
@Sourav just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Are we sure that ADF does not support XML as sink ? Chat GPT suggested that it supports. Can anyone confirm the latest supported feature in ADF sink as XML supported or not ?
@Sourav As of the latest information available, Azure Data Factory (ADF) does not support XML as a sink. This means that while ADF can read data from XML files, it cannot write or output data in XML format.
You can also try using the query in copy activity source which returns XML format and in sink change the file extension to .XML
Refer to this blog post SO
However, features are constantly evolving, so it’s always a good idea to check the official Microsoft documentation or Azure updates for the most recent information. https://learn.microsoft.com/en-us/azure/data-factory/format-xml
Hey,
Based on my knowledge XML file format is only supported as a source in ADF and not as a sink.
Meaning you can read XML data but cannot write an XML file.
So you would have to write your custom logic ia Azure functions/ADB etc for achieving your XML file output
So what is the best practice in this scenario?