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); |
|
|
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... |
|
|
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. |
|
|
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? |
|
|
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" |
|
|
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>' |
|
|
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.aspxRegardless, since you got it working, it is only of academic interest to you anyway .) |
|
|
Fleischman718
Starting Member
1 Post |
Posted - 2012-12-19 : 09:56:43
|
Thanks "sunitabeck" it works for meJ. Fleischman |
|
|
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 @XMLDataehen trying to run this code showing an error: XML parsing: line 1, character 121, illegal xml characterThe reason for that error is '#xB;':<OrderDescription>Imdur (isosorbide mononitrate#xB;ext. release#xB;)</OrderDescription>Please rewrite the code to get the xml without errornaresh arjala |
|
|
|