Using this solution from Forrest:
DECLARE @XML xml =
'<?xml version="1.0" encoding="UTF-8"?>
<Orders>
<Order>
<OrderID>334</OrderID>
<AmountPaid currencyID="EUR">17.10</AmountPaid>
<UserID>marc58</UserID>
<ShippingAddress>
<Name>Marc Juppé</Name>
<Address>Rue garçonneé III° arrondissement</Address>
<City>Paris</City>
<StateOrProvince></StateOrProvince>
<Country>FR</Country>
<Phone>333333333</Phone>
<PostalCode>22222</PostalCode>
</ShippingAddress>
<ShippingCosts>4.50</ShippingCosts>
<Items>
<Item>
<Details>
<ItemID>3664</ItemID>
<Store>47</Store>
<Title>MCPU DDA010</Title>
<SKU>mmx</SKU>
</Details>
<Quantity>1</Quantity>
<Price currencyID="EUR">6.2</Price>
</Item>
<Item>
<Details>
<ItemID>3665</ItemID>
<Store>45</Store>
<Title>MCPU DFZ42</Title>
<SKU>mmy</SKU>
</Details>
<Quantity>2</Quantity>
<Price currencyID="EUR">3.2</Price>
</Item>
</Items>
</Order>
</Orders>'
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 è
but get this error:
XML parsing: line 162, character 41, well formed check: undeclared entity
while if I convert to è
it works.
The problem is I cannot HTMLEncode the XML File since I expect it will convert also all the "<" and ">" of the XML Structure, and probably also something else.
Can suggest the best solution to HTMLEncode the XML before parsing?