Vikas Kunte Vikas Kunte - 11 months ago 56
SQL Question

Convert to XML having special character name in SQL server

There is a XML in the below format present in a column of datatype VARCHAR(MAX) in sql table

<?xml version="1.0" encoding="UTF-8"?>
<APIDATA xmlns="mynamespace">
<TRADE Action="Insert" CompanyID="33" Trader="AleŇ° Holubec"

I need to fetch attribute values of Action and CompanyID . i.e, Insert and 33

In SQL, i used the below query


SELECT CONVERT(XML,column_name).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

But i get the below error

XML parsing: line 1, character 537, illegal xml character

The reason is AleŇ° Holubec is the name of the trader in the XML which we cannot change.
Please help how to resolve this

Answer Source

You can fix this by converting your string to nvarchar(max) and then replace the encoding to UTF-16.

SELECT CONVERT(XML, replace(cast(column_name as nvarchar(max)), '<?xml version="1.0" encoding="UTF-8"?>', '<?xml version="1.0" encoding="UTF-16"?>')).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

Or you can just remove the XML declaration.

SELECT CONVERT(XML, replace(column_name, '<?xml version="1.0" encoding="UTF-8"?>', '')).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')