SQL XML Parser Error: illegal name character

1
votes

I am trying to execute the following query:

SELECT CASE CAST(a.AnswerText as XML).value('(/AnswerData/Input[@Name = "AnswerData0"]/Value)[1]', 'varchar(100)')
WHEN 'Other' THEN 'Self'
WHEN 'man' THEN 'Manager'
WHEN 'sub' THEN 'Suborbinate'
WHEN 'Colleague' THEN 'Colleague'
ELSE (CAST(a.AnswerText as XML).value('(/AnswerData/Input[@Name = "AnswerData0"]/Value)[1]', 'nvarchar(100)'))
END as RelationShip, ta.TestAssignmentId

This is just a snippet of a much larger query, but this is where the error is being caused. The error being outputted is:

XML parsing: line 1, character 67, illegal name character

I realised this was a problem with the XML, so I checked the XML and it seems ok to me:

<AnswerData><Input Name="AnswerData0" Correct="false"><Value>Other</Value></Input></AnswerData>
<AnswerData><Input Name="AnswerData0" Correct="true"><Value>sub</Value></Input></AnswerData>
<AnswerData><Input Name="AnswerData0" Correct="false"><Value>Colleague</Value></Input></AnswerData>
<AnswerData><Input Name="AnswerData0" Correct="true"><Value>sub</Value></Input></AnswerData>
<AnswerData><Input Name="AnswerData0" Correct="false"><Value>Colleague</Value></Input></AnswerData>
<AnswerData><Input Name="AnswerData0" Correct="false"><Value>man</Value></Input></AnswerData>
<AnswerData><Input Name="AnswerData0" Correct="false"><Value>Colleague</Value></Input></AnswerData>

Can anyone provide an insight or notice something in the XML which I have missed.

1
sqlxml
you xml is fine and query should work too (it's a bit wierd but I don't know what you want to get). Looks like you have some illegal char in another rows xml data. Why do you need to cast, is your xml stored as varchar column or do you create it on the fly? – Roman Pekar
Its stored as a varchar which is why it needs to be cast – 97ldave
have you tried to do plain select cast(AnswerText as xml) from <your table>? Just to see if there's problems in your xml data? – Roman Pekar
Are you writing SQL (structured query language) and really mean Microsoft SQL Server (the actual product) by this? If yes: please add sql-server tag to make this clear. If not: what database system is this for? + – marc_s
@RomanPekar: I agree with your guess - but it would be nice if the folks asking those questions would tell us instead of leaving us guessing .... – marc_s

1 Answers

2
votes

As it turned out, the reason I was getting the error was due to the fact a.AnswerTest was in fact returning NULL, which meant it could not be cast as XML.

In order to ensure a.AnswerText did not return NULL I added an extra WHERE clause to eliminate this from happening.