CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlencoding
alexandre
alexandre

Reputation: 355

XML parsing Error Illegal qualified name character

I'm using SQL Server 2008 Management Studio and I try to insert some XML data into my table. I've never done that before can you please explain me why SQL is throwing an error?

Here's my data:

select 
    IDPatient, IDObjet, Nplan, dateCreation, dateModification, actif,
    case 
       when ISNULL(unite_20,'') + ISNULL(date_20,'') <> '' 
          then 20
       when ISNULL(unite_19,'') + ISNULL(date_19,'') <> '' 
          then 19 
       when ISNULL(unite_18,'') + ISNULL(date_18,'') <> '' 
          then 18 
       when ISNULL(unite_17,'') + ISNULL(date_17,'') <> '' 
          then 17 
       when ISNULL(unite_16,'') + ISNULL(date_16,'') <> '' 
          then 16 
       when ISNULL(unite_15,'') + ISNULL(date_15,'') <> '' 
          then 15
       ... and so forth .....
       when ISNULL(unite_1,'') + ISNULL(date_1,'') <> '' 
          then 1 
    End As NbrAdministration,
    0,      
    '<IDObjet>'+ 
 case when ISNULL(unite_1,'') + ISNULL(date_1,'') <> '' then
 '<Administration1>'+
 '<Unite>'+isnull(convert(varchar,Unite_1),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_1,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_1,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_1,'')+'</Initiale>'+
 '<Site>'+isnull(Site_1,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_1),'')+'</IDRV>'+
 '</Administration1>'
 else ''
 end + 
  case when ISNULL(unite_2,'') + ISNULL(date_2,'') <> '' then
 '<Administration2>'+
 '<Unite>'+isnull(convert(varchar,Unite_2),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_2,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_2,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_2,'')+'</Initiale>'+
 '<Site>'+isnull(Site_2,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_2),'')+'</IDRV>'+
 '</Administration2>'   
 else ''
 end +
  case when ISNULL(unite_3,'') + ISNULL(date_3,'') <> '' then
 '<Administration3>'+
 '<Unite>'+isnull(convert(varchar,Unite_3),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_3,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_3,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_3,'')+'</Initiale>'+
 '<Site>'+isnull(Site_3,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_3),'')+'</IDRV>'+
 '</Administration3>'
 else ''
 end +
 case when ISNULL(unite_4,'') + ISNULL(date_4,'') <> '' then
 '<Administration4>'+
 '<Unite>'+isnull(convert(varchar,Unite_4),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_4,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_4,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_4,'')+'</Initiale>'+
 '<Site>'+isnull(Site_4,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_4),'')+'</IDRV>'+
 '</Administration4>'
 else ''
 end +
 case when ISNULL(unite_5,'') + ISNULL(date_5,'') <> '' then
 '<Administration5>'+
 '<Unite>'+isnull(convert(varchar,Unite_5),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_5,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_5,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_5,'')+'</Initiale>'+
 '<Site>'+isnull(Site_5,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_5),'')+'</IDRV>'+
 '</Administration5>'
 else ''    
 end +
 case when ISNULL(unite_6,'') + ISNULL(date_6,'') <> '' then
 '<Administration6>'+
 '<Unite>'+isnull(convert(varchar,Unite_6),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_6,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_6,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_6,'')+'</Initiale>'+
 '<Site>'+isnull(Site_6,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_6),'')+'</IDRV>'+
 '</Administration6>'
 else ''    
 end +
 case when ISNULL(unite_7,'') + ISNULL(date_7,'') <> '' then
 '<Administration7>'+
 '<Unite>'+isnull(convert(varchar,Unite_7),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_7,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_7,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_7,'')+'</Initiale>'+
 '<Site>'+isnull(Site_7,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_7),'')+'</IDRV>'+
 '</Administration7>'
 else ''    
 end +
 case when ISNULL(unite_8,'') + ISNULL(date_8,'') <> '' then
 '<Administration8>'+
 '<Unite>'+isnull(convert(varchar,Unite_8),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_8,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_8,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_8,'')+'</Initiale>'+
 '<Site>'+isnull(Site_8,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_8),'')+'</IDRV>'+
 '</Administration8>'
 else ''    
 end +
 case when ISNULL(unite_9,'') + ISNULL(date_9,'') <> '' then
 '<Administration9>'+
 '<Unite>'+isnull(convert(varchar,Unite_9),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_9,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_9,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_9,'')+'</Initiale>'+
 '<Site>'+isnull(Site_9,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_9),'')+'</IDRV>'+
 '</Administration9>'
 else ''    
 end +
 case when ISNULL(unite_10,'') + ISNULL(date_10,'') <> '' then
 '<Administration10>'+
 '<Unite>'+isnull(convert(varchar,Unite_10),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_10,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_10,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_10,'')+'</Initiale>'+
 '<Site>'+isnull(Site_10,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_10),'')+'</IDRV>'+
 '</Administration10>'
 else ''
 end +
 case when ISNULL(unite_11,'') + ISNULL(date_11,'') <> '' then
 '<Administration11>'+
 '<Unite>'+isnull(convert(varchar,Unite_11),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_11,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_11,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_11,'')+'</Initiale>'+
 '<Site>'+isnull(Site_11,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_11),'')+'</IDRV>'+
 '</Administration11>'
 else ''
 end + 
  case when ISNULL(unite_12,'') + ISNULL(date_12,'') <> '' then
 '<Administration12>'+
 '<Unite>'+isnull(convert(varchar,Unite_12),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_12,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_12,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_12,'')+'</Initiale>'+
 '<Site>'+isnull(Site_12,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_12),'')+'</IDRV>'+
 '</Administration12>'
 else ''
 end +
  case when ISNULL(unite_13,'') + ISNULL(date_13,'') <> '' then
 '<Administration13>'+
 '<Unite>'+isnull(convert(varchar,Unite_13),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_13,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_13,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_13,'')+'</Initiale>'+
 '<Site>'+isnull(Site_13,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_13),'')+'</IDRV>'+
 '</Administration13>'
 else ''
 end +
 case when ISNULL(unite_14,'') + ISNULL(date_14,'') <> '' then
 '<Administration14>'+
 '<Unite>'+isnull(convert(varchar,Unite_14),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_14,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_14,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_14,'')+'</Initiale>'+
 '<Site>'+isnull(Site_14,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_14),'')+'</IDRV>'+
 '</Administration14>'
 else ''
 end +
 case when ISNULL(unite_15,'') + ISNULL(date_15,'') <> '' then
 '<Administration15>'+
 '<Unite>'+isnull(convert(varchar,Unite_15),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_15,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_15,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_15,'')+'</Initiale>'+
 '<Site>'+isnull(Site_15,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_15),'')+'</IDRV>'+
 '</Administration15>'
 else ''    
 end +
 case when ISNULL(unite_16,'') + ISNULL(date_16,'') <> '' then
 '<Administration16>'+
 '<Unite>'+isnull(convert(varchar,Unite_16),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_16,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_16,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_16,'')+'</Initiale>'+
 '<Site>'+isnull(Site_16,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_16),'')+'</IDRV>'+
 '</Administration16>'
 else ''    
 end +
 case when ISNULL(unite_17,'') + ISNULL(date_17,'') <> '' then
 '<Administration17>'+
 '<Unite>'+isnull(convert(varchar,Unite_17),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_17,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_17,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_17,'')+'</Initiale>'+
 '<Site>'+isnull(Site_17,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_17),'')+'</IDRV>'+
 '</Administration17>'
 else ''    
 end +
 case when ISNULL(unite_18,'') + ISNULL(date_18,'') <> '' then
 '<Administration18>'+
 '<Unite>'+isnull(convert(varchar,Unite_18),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_18,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_18,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_18,'')+'</Initiale>'+
 '<Site>'+isnull(Site_18,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_18),'')+'</IDRV>'+
 '</Administration18>'
 else ''    
 end +
 case when ISNULL(unite_19,'') + ISNULL(date_19,'') <> '' then
 '<Administration19>'+
 '<Unite>'+isnull(convert(varchar,Unite_19),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_19,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_19,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_19,'')+'</Initiale>'+
 '<Site>'+isnull(Site_19,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_19),'')+'</IDRV>'+
 '</Administration19>'
 else ''    
 end +
 case when ISNULL(unite_20,'') + ISNULL(date_20,'') <> '' then
 '<Administration20>'+
 '<Unite>'+isnull(convert(varchar,Unite_20),'')+'</Unite>'+
 '<Date>'+isnull(convert(varchar,Date_20,120),'')+'</Date>'+
 '<Texte>'+isnull(Texte_20,'')+'</Texte>'+
 '<Initiale>'+isnull(Initiale_20,'')+'</Initiale>'+
 '<Site>'+isnull(Site_20,'')+'</Site>'+
 '<Cancel></Cancel>'+
 '<IdNote></IdNote>'+
 '<IDRV>'+isnull(convert(varchar,IDRV_20),'')+'</IDRV>'+
 '</Administration20>'
 else ''
 end + '</IDObjet>'
 from  tmp_CarnetVac

Here's the error :

Msg 9455, Niveau 16, État 1, Ligne 3 XML parsing: line 1, character 80, illegal qualified name character

Thank you for your help!

Upvotes: 2

Views: 5504

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

You should create your XML with FOR XML PATH! Doing this by binding strings together is a very bad habit you really should avoid!

To explain you what is happening (probably):

I use a table variable to mock up a table with values and insert two "rows": 

DECLARE @tbl TABLE(SomeText VARCHAR(100));
INSERT INTO @tbl VALUES
 ('This is some text.')
,('Even more ...');

This is roughly what you are doing: Combine the elements on string-base and cast it to XML. This works actually...

SELECT CAST('<Element>' + SomeText + '</Element>' AS XML)
FROM @tbl;

But now I add a row, which contains an illegal character 

INSERT INTO @tbl VALUES('Forbidden & character!'); --The & (ampersand) is forbidden (as well as <, > and some more)

Suddenly the same statement throws "your" error. Simply because an ampersand within normal text is forbidden and must be escaped.

SELECT CAST('<Element>' + SomeText + '</Element>' AS XML)
FROM @tbl;

From your question we cannot know, what exactly is going wrong... Might be a &. <, or >, might be a quot in the wrong place... might be a special character where you'd need unicode encoding. Might be enough to start all your string literals with a leading N (N'string-literal') and use NVARCHAR instead of VARCHAR everywhere... But all this would not solve the real problem: Do not create XML via string concatenation!

This is the way you should do this:

SELECT SomeText
FROM @tbl
FOR XML PATH('row'),ROOT('root'),TYPE

As you can see, the forbidden character is properly escaped implicitly:

<root>
  <row>
    <SomeText>This is some text.</SomeText>
  </row>
  <row>
    <SomeText>Even more ...</SomeText>
  </row>
  <row>
    <SomeText>Forbidden &amp; character!</SomeText>
  </row>
</root>

UPDATE

I must admit, that I'm quite sure, that this does not really solve your problem... And I must admit, that your whole query smells a bit... But you did not show enough to help you further...

I'd suggest to start a new question where you show some (reduced) sample data and the expected output. This can - for sure - be done much better...

Upvotes: 3

Related Questions