XML column splitting in ADF copy activity

Prapul Kumar Dongari 116

Hi team , I have table in SQL which contains XML column ,while i was copying data from SQL to ADLS (.csv format) the xml column data is getting splitted into new rows.

note : ADF sink dataset settings are as follows

escape character : doublequote

quote character : doublequote

XML column format is shown in screenshot:

User's image

please help me with solution

ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2023-05-02T10:07:27.7033333+00:00

Hi Prapul Kumar Dongari,

Thank you for posting query in Microsoft Q&A Platform.

It seems your XML data also has double quotes in it. So kindly use different column delimiters and quote characters and try. For example, you can consider using pipe symbol | as column delimiter.

Please let me know how it goes.
Prapul Kumar Dongari 116 Reputation points

2023-05-04T06:09:14.0966667+00:00

hi @ShaikMaheer-MSFT we tried using | delimeter but its not working
ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2023-05-08T15:53:33.8433333+00:00

Hi Prapul Kumar Dongari, Did you tried with different quote character as well?
ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2023-05-24T06:48:44.9133333+00:00

Hi Prapul Kumar Dongari, Just checking if any updates ? If you found any other work around then kindly feel free to share here. That helps community as well.
Javid Ali Khan Lavani 0 Reputation points

2023-11-22T11:18:52.0733333+00:00

Hi Maheer,

I am also facing with the similar issue

i have tried by changing the quote character also. Then also it is splitting into two rows i am saving the file in xml format in Data lake.

Share via

XML column splitting in ADF copy activity

Your answer