user3253051
user3253051

Reputation: 95

XML parsing: line 1, character 80, unexpected end of input

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&amp). Thank you

declare @x xml
set @x = '<options>
<option>
<code>95544</code>
<description>17&amp;#34;&amp;#32;Star&amp;#32;spoke&amp;#32;alloy&amp;#32;wheels&amp;#32;&amp;#45;&amp;#32;style&amp;#32;393</description>
<monthlycost>0.00</monthlycost>
<allowed />
</option>
<option>
<code>107394</code>
<description>19&amp;#34;&amp;#32;Individual&amp;#32;V&amp;#32;spoke&amp;#32;alloy&amp;#32;wheels&amp;#32;&amp;#45;&amp;#32;Style&amp;#32;626I</description>
<monthlycost>37.13</monthlycost>
<allowed />
</option>
<option>
<code>86469</code>
<description>Adaptive&amp;#32;M&amp;#32;Sports&amp;#32;suspension</description>
<monthlycost>21.09</monthlycost>
<allowed />
</option>
<option>
<code>61202</code>
<description>Metallic&amp;#32;&amp;#45;&amp;#32;Black&amp;#32;sapphire</description>
<monthlycost>8.15</monthlycost>
<allowed />
</option>
<option>
<code>94722</code>
<description>Move&amp;#32;cloth&amp;#32;&amp;#45;&amp;#32;Anthracite</description>
<monthlycost>0.00</monthlycost>
<allowed />
</option>
<option>
<code>11646</code>
<description>Solid&amp;#32;&amp;#45;&amp;#32;Alpine&amp;#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)

Upvotes: 4

Views: 19248

Answers (1)

har07
har07

Reputation: 89295

The problem was that your XML string was truncated, leaving an incomplete entity/invalid character like so (notice the trailing &) :

19&#34;&#32;Individual&#32;V&#32;spoke&#32;alloy&#32;wheels&#32;&#45;&#32;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]

Upvotes: 10

Related Questions