Clever Geek Handbook

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

I am getting the error above when trying to run this. I identify the problem - it is because of the V & in the description, but I'm not sure how to solve it. I cannot use the replacement because I will probably have the same situation with a different character / word (using amp for example). Thanks 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)
+3


source to share


1 answer


The problem was that your XML string was truncated, leaving the incomplete object / invalid character the same (note the trailing &

):

19&#34;&#32;Individual&#32;V&#32;spoke&#32;alloy&#32;wheels&#32;&#45;&#32;Style&


You just need to increase the size varchar

to solve the problem, for example:

,CONVERT(XML, 
        pref.value('(description/text())[1]', 'varchar(max)')
 ).value('.', 'varchar(max)') as [DescriptionFixed]
+6


source






More articles:


All Articles