XML parsing: line 1, character 80, unexpected end of input
14,472
The problem was that your XML string was truncated, leaving an incomplete entity/invalid character like so (notice the trailing &
) :
19" Individual V spoke alloy wheels - Style&
You simply need to increase the varchar
size to solve the problem, for example :
,CONVERT(XML,
pref.value('(description/text())[1]', 'varchar(max)')
).value('.', 'varchar(max)') as [DescriptionFixed]
Author by
user3253051
Updated on July 18, 2022Comments
-
user3253051 almost 2 years
when trying to run this I'm getting the error above. I've identify the issue - it's because of the V& in the description but I'm not sure how to solve it. I can't use a replace because I probably will have the same situation again with a different character/word( e.g. with&). Thank you
declare @x xml set @x = '<options> <option> <code>95544</code> <description>17&#34;&#32;Star&#32;spoke&#32;alloy&#32;wheels&#32;&#45;&#32;style&#32;393</description> <monthlycost>0.00</monthlycost> <allowed /> </option> <option> <code>107394</code> <description>19&#34;&#32;Individual&#32;V&#32;spoke&#32;alloy&#32;wheels&#32;&#45;&#32;Style&#32;626I</description> <monthlycost>37.13</monthlycost> <allowed /> </option> <option> <code>86469</code> <description>Adaptive&#32;M&#32;Sports&#32;suspension</description> <monthlycost>21.09</monthlycost> <allowed /> </option> <option> <code>61202</code> <description>Metallic&#32;&#45;&#32;Black&#32;sapphire</description> <monthlycost>8.15</monthlycost> <allowed /> </option> <option> <code>94722</code> <description>Move&#32;cloth&#32;&#45;&#32;Anthracite</description> <monthlycost>0.00</monthlycost> <allowed /> </option> <option> <code>11646</code> <description>Solid&#32;&#45;&#32;Alpine&#32;white</description> <monthlycost>0.00</monthlycost> <allowed /> </option> </options>' select pref.value('(code/text())[1]', 'varchar(32)') as Code ,pref.value('(description/text())[1]', 'varchar(80)') as [Description] ,CONVERT(XML, pref.value('(description/text())[1]', 'varchar(80)') ).value('.', 'varchar(80)') as [DescriptionFixed] ,pref.value('(monthlycost/text())[1]', 'varchar(32)') as MontlyCost from @X.nodes('/options/option') AS Options(pref)