How to ignore the illegal characters from XML file in sql using OPENROWSET?

Question

How to ignore the illegal characters from XML file in sql using OPENROWSET?

Mummana Venkatesh Babu 0

When we are using the below code to read the XML file, we are getting the error as illegal characters. Here is the illegal character in the XML User's image

Below is the error message. (0 rows affected) Msg 9420, Level 16, State 1, Line 3 XML parsing: line 6612766, character 142, illegal xml character Below is the code.


INSERT INTO [KM].[XML_XML]([FILE_NAME],[BATCH_CODE],[XML])
	(SELECT   'Billrun_KPNRetail_20230302_20230262_PRODUCTION_1002_KPN.xml' AS [FILE_NAME], 'KM_230302' AS [BATCH_CODE]
	, CAST(BulkColumn AS XML) AS [XML] FROM OPENROWSET 
	( BULK 'importfiles/KM/XML/Billrun_KPNRetail_20230302_20230262_PRODUCTION_1002_KPN.xml', DATA_SOURCE = 'BlobStorage'
	, CODEPAGE = '65001', SINGLE_BLOB) AS ImportFile)

Viorel 122.6K Reputation points

2023-04-05T14:05:40.9+00:00

Edit the file manually, enter a valid character, and re-import the file. The application that creates this file must be fixed to generate valid files only.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-05T22:11:05.9333333+00:00

Are you sure that the file is a UTF_8 file? If it is, a lone 0xC3 in the file is invalid. Ot it is not UTF-8 at all. I note that the character is an Ã. Which is not a character I would expect to see in the middle of a Dutch word. Thus, it does not really seem to be a case of bad encoding. Just a damaged file.
Mummana Venkatesh Babu 0 Reputation points

2023-04-06T07:25:16.0766667+00:00

@Erland Sommarskog , yes it is UTF_8 file. this is coming from source team. is there any other way to ignore that characters?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-06T20:51:42.93+00:00

Rather than storing it as XML, you can store it as nvarchar(MAX). But if you want to get data out of it using XML methods, that will be difficult, but you would have descend to parse it with charindex. Which is no fun.

Or you could write a user-defined function that cleans up the XML. But that is even less fun. A lone Ã should be removed. But if you have, say Ã£ that's a legal UTF-8 sequence, which you probably shouldn't touch.

A more workable option is to specify the code page as 1252, and then accept that non-ASCII characters are mutilated.

I still think the best option is that the source system gets its act together and start producing legal XML. That is, legal XML requires that the encoding is respected.

1 answer

Your answer

Viorel 122.6K Reputation points

2023-04-05T14:05:40.9+00:00

Edit the file manually, enter a valid character, and re-import the file. The application that creates this file must be fixed to generate valid files only.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-05T22:11:05.9333333+00:00

Are you sure that the file is a UTF_8 file? If it is, a lone 0xC3 in the file is invalid. Ot it is not UTF-8 at all. I note that the character is an Ã. Which is not a character I would expect to see in the middle of a Dutch word. Thus, it does not really seem to be a case of bad encoding. Just a damaged file.
Mummana Venkatesh Babu 0 Reputation points

2023-04-06T07:25:16.0766667+00:00

@Erland Sommarskog , yes it is UTF_8 file. this is coming from source team. is there any other way to ignore that characters?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-06T20:51:42.93+00:00

Rather than storing it as XML, you can store it as nvarchar(MAX). But if you want to get data out of it using XML methods, that will be difficult, but you would have descend to parse it with charindex. Which is no fun.

Or you could write a user-defined function that cleans up the XML. But that is even less fun. A lone Ã should be removed. But if you have, say Ã£ that's a legal UTF-8 sequence, which you probably shouldn't touch.

A more workable option is to specify the code page as 1252, and then accept that non-ASCII characters are mutilated.

I still think the best option is that the source system gets its act together and start producing legal XML. That is, legal XML requires that the encoding is respected.

Answer 1

LiHongMSFT-4306 31,566

Hi @Mummana Venkatesh Babu You can use an SSIS package to pull in the data and cleanse it. Either throw out the entire record into a trash table or filter off the character and insure it's encoding to UTF-8 standards.

Also, you could pull in the record as a string, strip the character with a REPLACE and then convert it into XML.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Mummana Venkatesh Babu 0 Reputation points

2023-04-06T07:26:51.06+00:00

Hi Cosmog, we are not using SSIS in our project. We need to load the XML data into a SQL table. Is there any other way?
LiHongMSFT-4306 31,566 Reputation points

2023-04-07T07:31:54.79+00:00

Hi @Mummana Venkatesh Babu How about setting the correct encoding at the top: <?xml version="1.0" encoding="UTF-16"?>

Refer to this thread for more details: Can I insert UTF-8 XML into SQL Server?.

Share via

How to ignore the illegal characters from XML file in sql using OPENROWSET?

1 answer

Your answer