SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML Cast - Illegal character error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

whizkidgps
Starting Member

6 Posts

Posted - 07/19/2012 :  07:18:42  Show Profile  Reply with Quote
<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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/19/2012 :  07:31:55  Show Profile  Reply with Quote
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 - 07/19/2012 :  07:40:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/19/2012 :  07:59:02  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 07/19/2012 08:00:23
Go to Top of Page

whizkidgps
Starting Member

6 Posts

Posted - 07/19/2012 :  09:06:26  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 07/19/2012 :  09:27:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 07/19/2012 :  09:42:25  Show Profile  Reply with Quote
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>'

Edited by - whizkidgps on 07/19/2012 09:44:45
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/19/2012 :  10:35:39  Show Profile  Reply with Quote
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

USA
1 Posts

Posted - 12/19/2012 :  09:56:43  Show Profile  Reply with Quote
Thanks "sunitabeck"

it works for me

J. Fleischman
Go to Top of Page

naresh arjala
Starting Member

India
1 Posts

Posted - 01/03/2013 :  05:41:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000