Post reply

reading unicode or chinese char xml file in sql server 2008

  • May 5, 2011 at 6:15 am

    #99294

    I have xml file contains one of colum as chinese char. how to read the text and update in sql server 2008

    declare @xml_ xml , @e nchar(255)

    DECLARE @EDetails TABLE (

    NAMENCHAR(255)

    )

    set @xml_ ='<Event><Name><Value>測試合同 ÓPCM üñ ¿¡</Value></Name></Event>'

    insert into @EDetails

    SELECT

    T.item.value('(Name)[1]', 'NCHAR(100)') as e

    FROM @XML_.nodes('/Event') AS T(item)

    select * from @EDetails

    Plz help to find the solution

  • May 5, 2011 at 6:56 am

    #1320336

    When you set the value for the XML variable, put an "N" in front of the first single-quote. That makes the string unicode. Right now, you're using an ASCII string, and that's where it's losing the data, not in the XML process.

    declare @xml_ xml , @e nchar(255)

    DECLARE @EDetails TABLE (

    NAME NCHAR(255)

    )

    set @xml_ =N'<Event><Name><Value>???? ÓPCM üñ ¿¡</Value></Name></Event>' -- add "N" here

    insert into @EDetails

    SELECT

    T.item.value('(Name)[1]', 'NCHAR(100)') as e

    FROM @XML_.nodes('/Event') AS T(item)

    select * from @EDetails

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 2 posts - 1 through 2 (of 2 total)

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