CODEWITHSUNDEEP
sqlxml
97ldave
97ldave

Reputation: 5249

SQL XML Parser Error: illegal name character

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.

Upvotes: 1

Views: 8244

Answers (1)

97ldave
97ldave

Reputation: 5249

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.

Upvotes: 2

Related Questions