October 31, 2013 at 9:19 am
XML RETRIEVAL ISSUE DUE TO Foriegn Lanuage
I have a function to retrieve an xml element from a column in a table (data type of ntext) cast as XML. The element value is in foriegn language. The element is returned without issue as datatype XML. But when I cast ( or convert) as varchar, I get the following error.
"Conversion of one or more characters from XML to target collation impossible"
Yet when I assign the same xml to a variarable and retrieve the element I get the XML converted to ???? and I can convert to varchar without error.
I am perplexed.
Any guidance would be much appreciated.
Thanks,
Bill
Code-------------------------------------------
The wf_task_user_data table holds the 'real' xml data.
Table definition...
CREATE TABLE [dbo].[wf_task_user_data](
[task_id] [int] NOT NULL,
[data_xml] [ntext] NOT NULL,
CONSTRAINT [XPKwf_task_user_data] PRIMARY KEY CLUSTERED
(
[task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 87) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[wf_task_user_data] WITH CHECK ADD CONSTRAINT [wf_tasks_FK_wf_task_user_data] FOREIGN KEY([task_id])
REFERENCES [dbo].[wf_tasks] ([task_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[wf_task_user_data] CHECK CONSTRAINT [wf_tasks_FK_wf_task_user_data]
GO
Function to retrieve XML element
CREATE FUNCTION [dbo].[fnSPC_GetTaskXML](@TaskID int)
RETURNS XML
BEGIN
DECLARE @Result XML
SET
@Result =(
Select cast(data_xml as xml)
FROM wf_task_user_data tud
Where tud.task_id = @TaskID
)
RETURN @Result
END
The picture attached shows the result...
October 31, 2013 at 11:23 am
I've run into this before. Take a look at this article: http://nerdwords.blogspot.com/2010/01/sql-server-error-conversion-of-xml.html
-- Itzik Ben-Gan 2001
October 31, 2013 at 1:04 pm
Thank you.
This article helped!!
October 31, 2013 at 1:48 pm
No Problem:-)
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply