Reputation: 95
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)
Upvotes: 4
Views: 19248
Reputation: 89295
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]
Upvotes: 10