Reputation: 355
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
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!
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 & character!</SomeText>
</row>
</root>
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