Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML Cast - Illegal character error

Author  Topic 

whizkidgps
Starting Member

6 Posts

Posted - 2012-07-19 : 07:18:42
<r:Name>test Küname</r:Name>

when I casting above said XML file line into XML datatype, it thorws
"XML parsing: line 10, character 19, illegal xml character" error message, due to ü

character.

When I removed that character then it cast perfectly.

But, I should not remove that character. how can i do casting having such character? Please let me know.

Thanks.
Whizkid

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 07:31:55
You can try casting it to unicode first and then to XML. Something like this:
SELECT CAST(CAST(YourXMLCol AS NVARCHAR(4000)) AS XML);
Go to Top of Page

whizkidgps
Starting Member

6 Posts

Posted - 2012-07-19 : 07:40:44
Now, I'm getting "XML parsing: line 1, character 38, unable to switch the encoding" error.. And moreover I should not use "4000" length, I'm not sure how the XML file length should be...
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 07:59:02
You can use NVARCHAR(MAX) instead of NVARCHAR(4000).

The encoding problem is another thing. In your string, at the very beginning, the encoding is already specified. For example like this:
'<?xml version="1.0" encoding="UTF-8"?>
<r>test Küname</r>'
But, you cannot store that accented character (ü) in UTF-8. So your choices that I can think of are:

a) Change the encoding in the string. So you would change the UTF-8 to UTF-16.
'<?xml version="1.0" encoding="UTF-16"?>
<r>test Küname</r>'


b) remove the accented characters.
Go to Top of Page

whizkidgps
Starting Member

6 Posts

Posted - 2012-07-19 : 09:06:26
Great!! Thanks for your prompt reply!!

Can you please let me know, what's the difference between UTF-8 & UTF-16?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-19 : 09:27:37
It's how some characters are represented.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

whizkidgps
Starting Member

6 Posts

Posted - 2012-07-19 : 09:42:25
Instead of UTF-16, with out converting into Nvarchar, I've tried encoding format as "ISO-8859-1" and it seems to be working fine.

'<?xml version="1.0" encoding="ISO-8859-1"?>
<r>test Küname</r>'
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 10:35:39
You should not have to convert even for UTF-16. For UTF-8 and UTF-16, you do not even need to specify the encoding. This page is a little dated, but still mostly accurate: http://msdn.microsoft.com/en-us/magazine/cc302162.aspx

Regardless, since you got it working, it is only of academic interest to you anyway .)
Go to Top of Page

Fleischman718
Starting Member

1 Post

Posted - 2012-12-19 : 09:56:43
Thanks "sunitabeck"

it works for me

J. Fleischman
Go to Top of Page

naresh arjala
Starting Member

1 Post

Posted - 2013-01-03 : 05:41:29
DECLARE @XMLData XML
DECLARE @TextData NVARCHAR(MAX)

SET @XMLData=(SELECT convert(varchar,ISNULL(O.DateStartOrder,''),101) AS DateStartOrder ,
O.DateStopOrder ,
O.DateDC ,
OrderDescription =
RTRIM(RTRIM(LTRIM(ISNULL(PH.NamePharm,'') + ' ' ))
+ CASE WHEN ISNULL(ND.NameGeneric,'') = '' THEN ''
WHEN ISNULL(ND.NameGeneric,'') <> ISNULL(PH.NamePharm,'')
--THEN ''
THEN ' (' + ISNULL(ND.NameGeneric,'') + ')'
ELSE ''
END ),
-- + ' ' +
ISNULL(ND.Strength,'')
+ ' ' + ISNULL(ND.FormDosage,'')
+ ' ' + ISNULL(O.InstructionsDosing,'') AS SIG
FROM Orders O
LEFT OUTER JOIN OrderType OT ON O.IdOrderType = OT.IdOrderType
LEFT OUTER JOIN Pharm PH on PH.IdPharm = O.IdPharm
LEFT OUTER JOIN NDC ND ON O.IdNDC = ND.IdNDC
LEFT OUTER JOIN Users U1 WITH (NOLOCK) ON ISNULL(O.IdUserEntered, 0) = U1.IdUser
AND O.IdClinicNumber = U1.IdClinicNumber
WHERE O.IdPatient = 1577411
AND O.IdClinicNumber = 60285
AND O.IdUserNurse IS NOT NULL
AND O.DateStartOrder < GETDATE()
AND O.DATEDC IS NULL
AND ISNULL(O.DateStopOrder, GETDATE() + 1) > GETDATE()
AND ISNULL(O.IdSigNotRequired, 0) NOT IN (3, 4)
AND ISNULL(O.IsRejected,0) = 0
AND ISNULL(O.IsDisputed,0) = 0
AND ISNULL(O.IsGeneratedInError,0) = 0
AND O.IdOrderType = 2
--ORDER BY OrderDescription
FOR XML PATH('Results'),ROOT('HomeMedication'))

select @XMLData





ehen trying to run this code showing an error: XML parsing: line 1, character 121, illegal xml character


The reason for that error is '#xB;':
<OrderDescription>Imdur (isosorbide mononitrate#xB;ext. release#xB;)</OrderDescription>
Please rewrite the code to get the xml without error

naresh arjala
Go to Top of Page
   

- Advertisement -