I got a unrecognizable code when i used the dbo.fn_ExtractField().Why?

(imported topic written by joey_zheng91)

My sql is :

SELECT ‘ActionSite’ AS ‘Sitename’, P.ID, ‘Category’ AS ‘PropertyName’,

dbo.fn_ExtractField(‘Category’, 0, P.Fields) AS ‘PropertyValue’


dbo.LOCAL_OBJECT_DEFS AS P ON V.ID = P.ID AND V.LatestVersion = P.Version

WHERE (V.Sitename = ‘ActionSite’) AND (NOT (P.ParentID = 1)) AND (dbo.fn_ExtractField(‘Fixlet Type’, 0, P.Fields) = ‘Task’) AND (dbo.fn_ExtractField(‘Category’, 0, P.Fields) IS NOT NULL)

the result is :

ActionSite 235 Category 绋嬪簭绠$悊

ActionSite 250 Category 绋嬪簭绠$悊

ps: The ‘Category’ is a string of Chinese. if i use the dbo.fn_ExtractField(),i will get a unrecognizable code. How can i get the correct value? Who can help me?

(imported comment written by joey_zheng91)

if my sql code like this,the result is correct.

dbo.fn_ExtractField(‘Category’, 0, convert( varbinary(max), CONVERT(VARCHAR(8000),convert( xml, convert( varbinary(max), p.fields ) ))))

who can tell me reason?Is there any simpler solution?

(imported comment written by BenKus)

Hi joey,

It looks like your solution works well… I would continue to use that method.


(imported comment written by joey_zheng91)

Hi ben,

You means that my method is the best solution?

(imported comment written by joey_zheng91)

Hi ben,

Do you known any other methods?

(imported comment written by BenKus)

Hi Joey,

I haven’t reproduced your issue and I don’t know of any other solution and I don’t know of any reason you shouldn’t use your current method.


(imported comment written by joey_zheng91)

Hi Ben,

Thanks your replay.

I think my current method is great complexity.if my data is very large.Is there any efficiency questions?

(imported comment written by SY57_Jim_Montgomery)

The Chinese characters are not expected, right?

Can you post the results from this query for one of the tasks that returns Chinese Characters?

select top 1 p.id, p.name, convert( xml, convert( varbinary(max), p.fields ) ) as ConvertedXML
from local_object_defs p 
join versions v on v.id=p.id and v.sitename='ActionSite' and p.version=v.latestversion
and dbo.fn_ExtractField('Fixlet Type', 0, P.Fields) = 'Task'
order by name

You might need to add a where clause for p.id=235 to get a task that is broken.

I’m curious if the Chinese Characters are stored in the database like that, of if something else is causing it to get returned and displayed incorrectly.