Japnese char in XML

Question

Post reply

Japnese char in XML

Bill Maners

Ten Centuries

Points: 1340
More actions
May 15, 2012 at 1:40 pm

#255978

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

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?!?!

Bill Maners Ten Centuries Points: 1340 More actions · Answer 2

Bill Maners

Ten Centuries

Points: 1340

May 16, 2012 at 6:36 am

#1488328

I tried it but same result...

drew.allen SSC Guru Points: 76985 More actions · Answer 3

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

SQL Padawan Right there with Babe Points: 792 More actions · Answer 4

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?

Bill Maners Ten Centuries Points: 1340 More actions · Answer 5

The nvarchar change worked. I had not updated the function.

Thanks for the help...