Vikas Kunte Vikas Kunte - 3 months ago 22
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"
</TRADE>
</APIDATA>


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

In SQL, i used the below query

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')

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

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

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')
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.

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