How do I use flatten for a XML file which has schema drift enabled.

Question

How do I use flatten for a XML file which has schema drift enabled.

Kalim, Yarak 25

I've a complex XML which contains data in arrays. I've to load this data to Snowflake. There are some conditions that I've to keep in mind, mentioned below.

Conditions:

XML file schema will change very often.
Some of the columns in the XML may come someday and may not on the other days. Entire node will be absent if no data is to be sent for that column.
Some columns name don't match with the Sink table column names
There will be some column names which will be repeated in the XML file. Want to remove some column explicitly.

AnnuKumari-MSFT 34,451 Reputation points Microsoft Employee

2023-07-24T08:14:55.5266667+00:00

Hi Kalim, Yarak ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

I understand that you are looking for a way to flatten a complex XML file and load it into Snowflake where the XML file schema changes frequently. Please let me know if that is not the case.

Could you please share the xml file content as an attachment in .txt format for us to provide better resolution by reproducing your scenario .

I could suggest you to use mapping dataflow to load the xml data into snowflake by enabling Allow Schema drift in source . For more information regarding the same, kindly visit this documentation: Schema drift in mapping data flow

Looking forward to the source file content. Thankyou in advance.
Kalim, Yarak 25 Reputation points

2023-07-24T11:31:29.6466667+00:00

Hi,

Yes your understanding is correct that I first want to flatten the complex xml file and then since schema will change frequently I want to use schema drift.

However if I enable 'Allow Schema drift' in the source I will not get the complex xml structure to flatten the arrays within the xml file by using unroll by option.sample.txt
Kalim, Yarak 25 Reputation points

2023-07-26T13:56:28.8666667+00:00

AnnuKumari-MSFT Have shared the sample file in the last comment.

2 answers

Your answer

AnnuKumari-MSFT 34,451 Reputation points Microsoft Employee

2023-07-24T08:14:55.5266667+00:00

Hi Kalim, Yarak ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

I understand that you are looking for a way to flatten a complex XML file and load it into Snowflake where the XML file schema changes frequently. Please let me know if that is not the case.

Could you please share the xml file content as an attachment in .txt format for us to provide better resolution by reproducing your scenario .

I could suggest you to use mapping dataflow to load the xml data into snowflake by enabling Allow Schema drift in source . For more information regarding the same, kindly visit this documentation: Schema drift in mapping data flow

Looking forward to the source file content. Thankyou in advance.
Kalim, Yarak 25 Reputation points

2023-07-24T11:31:29.6466667+00:00

Hi,

Yes your understanding is correct that I first want to flatten the complex xml file and then since schema will change frequently I want to use schema drift.

However if I enable 'Allow Schema drift' in the source I will not get the complex xml structure to flatten the arrays within the xml file by using unroll by option.sample.txt
Kalim, Yarak 25 Reputation points

2023-07-26T13:56:28.8666667+00:00

AnnuKumari-MSFT Have shared the sample file in the last comment.

Answer 1

Sahil kumar 0

you may change the name of column

Kalim, Yarak 25 Reputation points

2023-07-21T10:15:02.3733333+00:00

What is coming in the source file can't be changed. Needs to be handled at runtime only.

Answer 2

AnnuKumari-MSFT 34,451 Microsoft Employee

Hi Kalim, Yarak ,

Apologies for delay in response. I tried to repro your scenario. The fact that you want to handle dynamic schema for xml after flattening it is making it complex to achieve.

Here is a clear explanation on how to handle dynamic schema using Rule Based Mapping in dataflow . However, the very first thing we need to take care is not to import schema for one file as we have to handle multiple files. In your case, if we do not import schema/projection, it wouldn't be able to get flattened as flatten transformation. So, it's contrasting.

Probably, you need to handle it programmatically using your own custom code.

Hope it helps. Kindly revert back in case of any concerns. Thankyou

ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2023-08-09T08:38:59.8433333+00:00

Hi Kalim, Yarak , just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

How do I use flatten for a XML file which has schema drift enabled.

2 answers

Your answer