May 15, 2012 at 1:40 pm
when trying process the following string as an XML statement, the japanese character are causing a failure. the code follows the error.
<DATA><POPULATION><ID>3</ID></POPULATION><CRITERIA><ID/><TITLE/><PHRASE>ATTR:6</PHRASE><MODE>1</MODE></CRITERIA><CRITERIAEXPRESSION><CRITERIAID>6</CRITERIAID><CRITERIATITLE>Criteria_6</CRITERIATITLE><EXPRESSION><EXPRESSION><ATTRIBUTE>5214a6cd-16da-4320-b12e-c462f7373f52</ATTRIBUTE><OPERATOR>8</OPERATOR><VALUE>εδΊ¬η</VALUE></EXPRESSION></EXPRESSION></CRITERIAEXPRESSION></DATA>
Msg 6355, Level 16, State 1, Line 55
Conversion of one or more characters from XML to target collation impossible
--------------------------------------------------------------------------------------------------------------
declare @data XML, @NValue nVarchar(max)
declare @segment_id int
declare @segs table (segment_id int, nodeName nvarchar(max), nodeValue nvarchar(max));
DECLARE Cursor_segments CURSOR FAST_FORWARD FOR
select segment_id, tool_data_xml from CM_segments
xwhere len(cast(tool_data_xml as nvarchar(max))) < 10000
order by segment_id
OPEN Cursor_segments
FETCH NEXT FROM Cursor_segments INTO @segment_id, @data
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing Segment: ' + cast (@segment_id as varchar)
Print cast (@data as nvarchar(MAX))
-- get the list of expressions
DECLARE Exp_cursor CURSOR for
select x.nodeValue
from
(
SELECT cast (node.query('fn:local-name(.)') as varchar(max)) AS NodeName, cast (node.query('./text()') as varchar(max)) AS NodeValue
FROM @data.nodes(N'//*') T(node)
) x
where cast (x.nodeName as varchar(max)) = 'EXPRESSION'
OPEN Exp_cursor
FETCH NEXT FROM exp_cursor INTO @nvalue
WHILE @@FETCH_STATUS = 0
BEGIN
print @nvalue
insert into @segs
select distinct segment_id, nodeName, nodeValue from dbo.fn_get_attrib_guid_from_xml(@segment_id, @nvalue)
FETCH NEXT FROM exp_cursor INTO @nvalue
END
CLOSE Exp_cursor
DEALLOCATE Exp_cursor
FETCH NEXT FROM Cursor_segments INTO @segment_id, @data
END
CLOSE Cursor_segments
DEALLOCATE Cursor_segments
select * from @segs
May 15, 2012 at 2:29 pm
when defining your cursor, maybe you could use nvarchar(max) instead of varchar(max), just a thought!
DECLARE Exp_cursor CURSOR for
select x.nodeValue
from
(
-- this line I would change each occurrence of 'varchar(max)' to 'nvarchar(max)'
SELECT cast (node.query('fn:local-name(.)') as nvarchar(max)) AS NodeName, cast (node.query('./text()') as nvarchar(max)) AS NodeValue
FROM @data.nodes(N'//*') T(node)
) x
where cast (x.nodeName as varchar(max)) = 'EXPRESSION'
OPEN Exp_cursor
FETCH NEXT FROM exp_cursor INTO @nvalue
its worth a try right?!?!
May 16, 2012 at 6:36 am
I tried it but same result...
May 16, 2012 at 7:25 am
Try using .value() instead of .query. I think that the .query may have an implicit conversion to varchar in it somewhere. With the .value, you can specify an explicit conversion to nvarchar.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2012 at 1:18 pm
It is probably coming from the TVF dbo.fn_get_attrib_guid_from_xml. Have you stepped through this because I believe the rest of your code looks fine?
May 17, 2012 at 8:30 am
The nvarchar change worked. I had not updated the function.
Thanks for the help...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply