TSQL (2014) - Importing XML with accented and punctuation Characters Using this solution from Forrest:

DECLARE @XML xml = 
'&lt;?xml version="1.0" encoding="UTF-8"?&gt;
   &lt;Orders&gt;
    &lt;Order&gt;
        &lt;OrderID&gt;334&lt;/OrderID&gt;
        &lt;AmountPaid currencyID="EUR"&gt;17.10&lt;/AmountPaid&gt;
        &lt;UserID&gt;marc58&lt;/UserID&gt;
        &lt;ShippingAddress&gt;
            &lt;Name&gt;Marc Juppé&lt;/Name&gt;
            &lt;Address&gt;Rue  garçonneé III° arrondissement&lt;/Address&gt;
            &lt;City&gt;Paris&lt;/City&gt;
            &lt;StateOrProvince&gt;&lt;/StateOrProvince&gt;
            &lt;Country&gt;FR&lt;/Country&gt;
            &lt;Phone&gt;333333333&lt;/Phone&gt;
            &lt;PostalCode&gt;22222&lt;/PostalCode&gt;
         &lt;/ShippingAddress&gt;
         &lt;ShippingCosts&gt;4.50&lt;/ShippingCosts&gt;
         &lt;Items&gt;
            &lt;Item&gt;
               &lt;Details&gt;
                    &lt;ItemID&gt;3664&lt;/ItemID&gt;
                    &lt;Store&gt;47&lt;/Store&gt;
                    &lt;Title&gt;MCPU DDA010&lt;/Title&gt;
                    &lt;SKU&gt;mmx&lt;/SKU&gt;
                &lt;/Details&gt;
                &lt;Quantity&gt;1&lt;/Quantity&gt;
                &lt;Price currencyID="EUR"&gt;6.2&lt;/Price&gt;
            &lt;/Item&gt;
            &lt;Item&gt;
               &lt;Details&gt;
                    &lt;ItemID&gt;3665&lt;/ItemID&gt;
                    &lt;Store&gt;45&lt;/Store&gt;
                    &lt;Title&gt;MCPU DFZ42&lt;/Title&gt;
                    &lt;SKU&gt;mmy&lt;/SKU&gt;
                &lt;/Details&gt;
                &lt;Quantity&gt;2&lt;/Quantity&gt;
                &lt;Price currencyID="EUR"&gt;3.2&lt;/Price&gt;
            &lt;/Item&gt;
        &lt;/Items&gt;
    &lt;/Order&gt;
&lt;/Orders&gt;'

SELECT 
    x.value('./ItemID[1]','int') AS ItemID,
    x.value('./Store[1]','int') AS Store,
    x.value('./Title[1]','nvarchar(100)') AS Title,
    x.value('./SKU[1]','nvarchar(100)') AS SKU,
    x.value('../Quantity[1]','int') AS Qty,
    x.value('../Price[1]','decimal(11,2)') AS Price,
    x.query('//OrderID[1]').value('.','int') AS OrderID,
    x.query('//AmountPaid[1]').value('.','decimal(11,2)') AS AmountPaid,
    x.query('//UserID[1]').value('.','nvarchar(100)') AS UserID,
    x.query('//ShippingCosts[1]').value('.','decimal(11,2)') AS ShippingCosts
FROM @XML.nodes('//Item/Details') i(x)


I receive an error like


  XML parsing: line 162, character 34, illegal xml character


when need to parse fields containing accented characters (àèòìùé) or °

I tried converting é int &amp;egrave; but get this error:


  XML parsing: line 162, character 41, well formed check: undeclared entity


while if I convert to &amp;#232; it works.

The problem is I cannot HTMLEncode the XML File since I expect it will convert also all the "&lt;" and ">" of the XML Structure, and probably also something else.

Can suggest the best solution to HTMLEncode the XML before parsing?
The XML you have is invalid in UTF-8 encoding. The accented characters needs to be encoded. For instance ° should be encoded as Â°.

Here is a shorter version that also fails.

declare @X xml = '&lt;?xml version="1.0" encoding="UTF-8"?&gt;°';
select @X.value('text()[1]', 'nchar(1)');



  Msg 9420, Level 16, State 1, Line 1 XML parsing: line 1, character 39,
  illegal xml character


Should be like this instead if you use UTF-8.

declare @X xml = '&lt;?xml version="1.0" encoding="UTF-8"?&gt;Â°';
select @X.value('text()[1]', 'nchar(1)');


You need to trace the source of the data backwards to the producer to see where you end up with invalid XML.