| Author |
 Topic  |
|
|
whizkidgps
Starting Member
6 Posts |
 Posted - 07/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/19/2012 : 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 - 07/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/19/2012 : 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.
|
Edited by - sunitabeck on 07/19/2012 08:00:23 |
|
|
|
whizkidgps
Starting Member
6 Posts |
Posted - 07/19/2012 : 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
Sweden
29138 Posts |
Posted - 07/19/2012 : 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 - 07/19/2012 : 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>' |
Edited by - whizkidgps on 07/19/2012 09:44:45 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/19/2012 : 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 .) |
|
|
|
Fleischman718
Starting Member
USA
1 Posts |
Posted - 12/19/2012 : 09:56:43
|
Thanks "sunitabeck"
it works for me
J. Fleischman |
|
|
|
naresh arjala
Starting Member
India
1 Posts |
Posted - 01/03/2013 : 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 |
|
|
| |
Topic |
|
XML Cast - Illegal character error
