I'm new to Azure Data Factory. In Azure Data Factory I have an XML file I want to flatten and convert to a CSV file. I add a Data Flow, and to the Data Flow I add a "source1" where I have already done an "Import projection". I added a "flatten1". In the flatten1 Settings I have to choose a field to "Unroll By" but all fields in my XML file are grayed out. How do fix this so I can choose an Unroll by? Could there be something wrong with my XML file? I got no errors about it from Azure. When I publish the pipeline I get this error: "sink1 The following column(s) have a complex structure which can only be written to Snowflake, REST, ORC, JSON, AVRO, and Azure Cosmos DB: 'Order'. Please remove the columns or update the sink to Snowflake, REST, ORC, JSON, AVRO, or Azure Cosmos DB."

Hello Chuck Roberts , The flatten transformation unrolls an array. Hence, the unroll root must be an array of complex objects. The possible root cause is that your complex object type is not an array. You can refer the below document for more information about the flatten transformation requirement. https://learn.microsoft.com/en-us/azure/data-factory/data-flow-flatten#unroll-by I hope this answers your question.

Unroll By button for flatten is grayed out

Chuck Roberts 110

I'm new to Azure Data Factory. In Azure Data Factory I have an XML file I want to flatten and convert to a CSV file. I add a Data Flow, and to the Data Flow I add a "source1" where I have already done an "Import projection". I added a "flatten1". In the flatten1 Settings I have to choose a field to "Unroll By" but all fields in my XML file are grayed out.

How do fix this so I can choose an Unroll by?
Could there be something wrong with my XML file? I got no errors about it from Azure.

When I publish the pipeline I get this error:

"sink1

The following column(s) have a complex structure which can only be written to Snowflake, REST, ORC, JSON, AVRO, and Azure Cosmos DB: 'Order'. Please remove the columns or update the sink to Snowflake, REST, ORC, JSON, AVRO, or Azure Cosmos DB."

1 answer

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2024-06-24T17:29:41.0766667+00:00

Hello Chuck Roberts,

The flatten transformation unrolls an array. Hence, the unroll root must be an array of complex objects.

The possible root cause is that your complex object type is not an array.

You can refer the below document for more information about the flatten transformation requirement.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-flatten#unroll-by

I hope this answers your question.
Please sign in to rate this answer.
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2024-06-27T19:27:07.26+00:00

Hello Chuck Roberts,

I am checking to see if you have any further questions here.

Chuck Roberts 110 Reputation points

2024-06-28T10:57:55.2933333+00:00

I'm new to Azure. What exactly is an "array of complex objects"? My XML file has one root element called <orders> and the next element is <order> and there is only one of those. And all the data I need is under the <order> element.

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2024-06-28T14:33:11.3166667+00:00

An "array of complex objects" is essentially a list or collection of items where each item has multiple properties or nested elements. In your case, if there is only one <order> element under the root <orders> element, it is not an array but a single instance of a complex object.

The flatten transformation in Azure Data Factory is designed to work with arrays because it "flattens" each item in the array into a separate row in the output. Since your XML has only one <order> element, it is not suitable for the flatten transformation. You would need multiple <order> elements for it to work properly.

I hope this helps.

Chuck Roberts 110 Reputation points

2024-07-01T12:32:20.4766667+00:00

Everyone one of my XML files has only 1 <order> element so it cannot be unrolled. Is there another way to do this? I'm brand new to ADF. I need to make a CSV file and XLS file, each in a different format, from data in the XML file.

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2024-07-01T17:38:18.5966667+00:00

Since your XML structure has a single <order> element, you might not need to use the Flatten transformation if there are no nested arrays within <order>.

If your XML structure is complex and you need to convert column values into column names, use a Pivot transformation.

You can reference this below thread.

https://learn.microsoft.com/en-us/answers/questions/934357/data-factory-read-multilevel-xml-to-csv
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Unroll By button for flatten is grayed out

1 answer

Your answer