ADF - dataflow - xml parsing with optional tags

Bhanuprasad 121

I have xml file, if the optional elements are present able to use flatten activity and parse the xml it works fine.
When the optional elements (or tags) not present the process fails.

With tag:
<EDocument DVersion="1.0" Client="100" Id="5" Created="1/30/2021">
<Export_List>
<Export BDate="5/29/2020" Amount="57.45" ANumber="119" PName="1/2021" Year="2021" />
</Export_List>
</EDocument>

Without tag:
<EDocument DocVersion="1.0" Client="100" Id="5" Created="1/30/2021">
</EDocument>

Tried with filter, byName(column), also referred the following logic with no avail
https://learn.microsoft.com/en-us/answers/questions/91259/issue-with-adf-data-flow-xml-source-and-optional-n.html

Bhanuprasad 121 Reputation points

2021-02-03T03:15:46.343+00:00

Could you please advise, how we can achieve this?
HarithaMaddi-MSFT 10,136 Reputation points

2021-02-03T15:42:46.253+00:00

Hi @Bhanuprasad ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

I am able to preview the data by using the contents shared above without tags. Can you please suggest the expected output to be achieved and are you planning to use flatten activity for achieving which result, this would help us in additional investigation.

Looking forward for your response!
Bhanuprasad 121 Reputation points

2021-02-03T16:13:40.303+00:00

Thank you for responding, Yes I am able to achieve this.
Just to repeat, when the data is present the process works fine. When no data is present then we have issue with the Flatten Hierarchy activity.
HarithaMaddi-MSFT 10,136 Reputation points

2021-02-03T16:29:08.9+00:00

Thanks @Bhanuprasad for sharing details. Can you please elaborate on the "No data", kindly share sample file and the error/undesired output screenshot that you are facing while using flatten activity and the expected output.
Bhanuprasad 121 Reputation points

2021-02-03T18:56:53.317+00:00

When we receive the data like below, the process fails
Without tag:
<EDocument DocVersion="1.0" Client="100" Id="5" Created="1/30/2021">
</EDocument>

Error Message:
{"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: org.apache.spark.sql.AnalysisException: No such struct field Export_List in @Evan ! , @Created, @DVersion;","Details":"org.apache.spark.sql.AnalysisException: No such struct field Export_List in @Evan ! , @Created, @DVersion;"}

Unsuccessful in filter out if the data element not found

Filter Condition:
hasColumn(byName(toString(EDocument.Export_List)))

Error Message:
Filter2'(Line 48/Col 30): Column name function 'byName' does not accept column or argument parameters","Details":"
Haritha Maddi 26 Reputation points

2021-02-04T13:05:48.013+00:00

Thanks @Bhanuprasad for sharing. As per my understanding, this is my design and it cannot work if the element that is used is missing in input data. To clarify, the requirement is to flatten if the element is found else do nothing?
Bhanuprasad 121 Reputation points

2021-02-05T02:35:17.907+00:00

Yes that is correct. "requirement is to flatten if the element is found else do nothing"
Please confirm if this is a limitation?

2 answers

HarithaMaddi-MSFT 10,136 Reputation points

2021-02-05T07:46:22.883+00:00
Thanks @Bhanuprasad for confirming the details. Its not possible using a direct option in dataflow but the only possibility I can think of is to use multiple dataflows and by using lookup and IF Condition activities, trigger the dataflow (either with flatten or without flatten). Disabling an activity with a condition is unavailable in data factory today. I understand this is adding more code, I would recommend you to upvote an existing idea in feedback forum which will be closely monitored by data factory product team and considered for future implementations.

IF Condition Expression:

@empty(activity('Lookup1').output.value[0].Edocument)

Hope this helps! Please let us know for further queries and we will be glad to assist.

--

Please accept an answer if correct. 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.
Please sign in to rate this answer.
Bhanuprasad 121 Reputation points

2021-02-05T21:58:59.07+00:00

Could you please clarify on the config in lookup activity. This is the output I see out of Lookup activity [NOTE: Lookup was set as xml dataset]

"firstRow": { "EDocument": { "@DVersion": 1, "@Client": 100, "@Created": "1/30/2021" } }, "effectiveIntegrationRuntime"...

in If condition:

@empty(activity('Lookup1_copy1').output.firstRow.Export_List)

'empty(activity('Lookup1_copy1').output.firstRow.Export_List)' cannot be evaluated because property 'Export_List' doesn't exist, available properties are EDocument

Please advise, what configuration you have done

HarithaMaddi-MSFT 10,136 Reputation points

2021-02-06T06:47:20.777+00:00

Thanks @Bhanuprasad for posting the query. Below is the config setting that enables first row or all rows to be retrieved from lookup. I have used Edocument element, any specific reason you have used Export_List? To know if all other elements are empty under Edocument, please use Edocument in the IF Condition activity.

Hope this helps! Please let us know for further queries and we will be glad to assist.

Bhanuprasad 121 Reputation points

2021-02-06T13:16:34.697+00:00

EDocument will be always present. As I stated in my problem statement, if "Export_List" is not present (no data) then don't trigger the data flow. That is the reason for checking the "Export_List"

Without tag (no data):
<EDocument DocVersion="1.0" Client="100" Id="5" Created="1/30/2021">
</EDocument>

HarithaMaddi-MSFT 10,136 Reputation points

2021-02-06T14:59:22.267+00:00

Thanks @Bhanuprasad for posting the question. Since Edocument always exists, we are trying to check if any other elements exist under it or NULL else IF Condition activity will fail when export_list is not present in the data.

Please suggest if this answers the query.

Bhanuprasad 121 Reputation points

2021-02-07T06:26:53.69+00:00

Thanks for responding.

That is header line, we need the actual data elements, as shown in the initial screenshot flatten hierarchy --> unroll --> Export_List, that is the data we need to extract, if it this data element is not present then don't do anything.

<Export BDate="5/29/2020" Amount="57.45" ANumber="119" PName="1/2021" Year="2021" />

Apologize if this was not clear.
Sign in to comment
Bhanuprasad 121 Reputation points

2021-02-12T07:24:54.063+00:00

@HarithaMaddi-MSFT , thank you for your suggestions. just checking if there are any alternatives (or) work around we could validate in the xml file tags present or not?
Please sign in to rate this answer.
Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-13T04:41:48.377+00:00

You can use byName and check if it results in null and use a split transform to flatten or not. All this can be done in 1 dataflow.

Bhanuprasad 121 Reputation points

2021-02-14T07:41:25.35+00:00

Thank you for responding @Kiran-MSFT

I had tried this before (posted the comment above as well). Is there any particular way this parameter to be used?

Filter Condition:
hasColumn(byName(toString(EDocument.Export_List)))

Error Message:
Filter2'(Line 48/Col 30): Column name function 'byName' does not accept column or argument parameters","Details":"

Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-15T02:18:43.8+00:00

You can only pass a string value which represents a column name. Like byName('myColName') or pass this in as a string parameter

Bhanuprasad 121 Reputation points

2021-02-15T17:10:05.55+00:00

Without tag:
<EDocument DocVersion="1.0" Client="100" Id="5" Created="1/30/2021">
</EDocument>

--In this case, if I use unroll to get all column names it will fail because it says the Export_List not found
If I use to validate the column names first, it complaints about the column or argument parameters.

Function used: hasColumn(byName(toString(EDocument.Export_List)))
Please advise if I am missing any steps

Bhanuprasad 121 Reputation points

2021-02-15T17:11:31.847+00:00

@MarkKromer-8019 + @Mark Kromer MSFT - Any advise.

Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-15T18:14:11.63+00:00

As I had mentioned you cannot use a column name in byName. You have to use a string. like byName('foo') not byName(foo).

Use a combination of byName and byItem. byItem(byName('EDocument'), 'Export_List'). Do not miss the quotes that is very important.

hasColumn today only works on top level columns like finding EDocument. We don't have one to find items.
Sign in to comment

Share via

ADF - dataflow - xml parsing with optional tags

2 answers