title: Convert to XML having special character name in SQL server
body: <p>There is a XML in the below format present in a column of datatype VARCHAR(MAX) in sql table</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;APIDATA xmlns="mynamespace"&gt; 
  &lt;TRADE Action="Insert" CompanyID="33" Trader="Aleš Holubec" 
  &lt;/TRADE&gt;
  &lt;/APIDATA&gt;
  </code></pre>
  
  <p>I need to fetch attribute values of Action and CompanyID . i.e, Insert and 33</p>
  
  <p>In SQL, i used the below query</p>
  
  <pre><code>;WITH XMLNAMESPACES(DEFAULT 'mynamespace')
  
  SELECT CONVERT(XML,column_name).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')
  </code></pre>
  
  <p>But i get the below error</p>
  
  <pre><code>XML parsing: line 1, character 537, illegal xml character
  </code></pre>
  
  <p>The reason is Aleš Holubec is the name of the trader in the XML which we cannot change.
  Please help how to resolve this</p>