Convert to XML having special character name in SQL server
sql, sql-server, sql-server-2008, xml
Solution
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)')
Problem
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