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

Content Authoring
1

(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’

FROM dbo.VERSIONS AS V INNER JOIN

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?

2

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

3

(imported comment written by BenKus)

Hi joey,

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

Ben

4

(imported comment written by joey_zheng91)

Hi ben,

You means that my method is the best solution?

5

(imported comment written by joey_zheng91)

Hi ben,

Do you known any other methods?

6

(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.

Ben

7

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

8

(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
where
p.parentid=0
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.

–JIm