Post reply

Querying XML data type with UNICODE characters

  • October 12, 2015 at 4:01 am

    #304167

    Hi, Experts

    I'am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).

    Would you be able to advise me? TIA.

    Below is the sample script.

    DECLARE @XMLVAR XML

    SET @XMLVAR = '<?xml version="1.0"?>

    <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'

    SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME

    ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER

    FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)

  • October 12, 2015 at 12:20 pm

    #1832856

    eklavu (10/12/2015)

    Hi, Experts

    I'am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).

    Would you be able to advise me? TIA.

    Below is the sample script.

    DECLARE @XMLVAR XML

    SET @XMLVAR = '<?xml version="1.0"?>

    <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <NAME>QA*??1</NAME><NUMBER /></POLICY_SEARCH>'

    SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME

    ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER

    FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)

    Quick thought, change the implicit xml conversion from single byte to double byte, automatically converted to UTF-16 in SQL Server by prefixing the NVARCHAR N'' prefix to the value converted to XML.

    😎

    DECLARE @XMLVAR XML

    SET @XMLVAR = N'<?xml version="1.0"?>

    <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <NAME>QA*??1</NAME><NUMBER /></POLICY_SEARCH>'

    SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME

    ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER

    FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)

    QA*??1

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

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