以SQL Server 2008 XML数据类型存储Unicode数据

I have XML data type as a table column. I have stored some XML text inside it. The XML text contains some Hebrew characters, but after I store it in the table, all the Hebrew characters are turned to “?”. What can I do to fix it?

我有XML数据类型作为表列。我在其中存储了一些XML文本。 XML文本包含一些希伯来字符，但在将其存储在表格中之后，所有希伯来字符都变为“？”。我该怎么做才能解决它？

This is my UPDATE statement:

这是我的UPDATE语句：

update db_owner.Screen_Template set template_xml = 
'<?xml version="1.0"?>
    <Screen xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../GUIGenerator_V2/Scheme/GG_Scheme.xsd">
        <Legend EntityType="Request">
            <Module ForceTabs="1">
                <Events>
                    <Event Value="onafter_moduleinit_beforedraw()" Type="onafter_moduleinit_beforedraw"/>
                </Events>
                <Section Id="Header" Type="header" Caption="Header" ContainerCSS="background:#C1E0F4;border-bottom:2px groove;margin-bottom:3px;padding:10 3 3 3">
                    <Containers>
                        <Form Id="Header" Caption="General Details" Source="Request" Layout="Fixed">
                            <Layout>
                                <table cellaspacing="0" cellpadding="0">
                                    <tr>
                                        <td>
                                            <ControlRef Id="Origin" ShowCaption="1"/>
                                        </td>
                                        <td>
                                            <ControlRef Id="Type" ShowCaption="1"/>
                                        </td>
                                        <td>
                                            <ControlRef Id="Process" ShowCaption="1"/>
                                        </td>
                                        <td width="20" align="center">
                                            <span class="separator"/>
                                        </td>
                                        <td>
                                            <ControlRef Id="FindBy" ShowCaption="1"/>
                                        </td>
                                        <td>
                                            <ControlRef Id="Find"/>
                                        </td>
                                        <td>
                                            <button width="30" onclick="doFindCustomer()">
                                                <Caption Caption="GO"/>
                                            </button>
                                        </td>
                                    </tr>
                                </table>
                            </Layout>
                            <Controls>
                                <Control Id="Origin" Caption="Origin" FieldName="Origin" DataType="string" Width="90" CaptionWidth="70" ReadOnly="1" Mandatory="1" Hidden="1"/>
                                <Control Id="Type" Caption="Type" FieldName="Type" DataType="select" Width="60" CaptionWidth="40" ReadOnly="1" Mandatory="1" DefaultValue="Service" Hidden="1">
                                    <Member Value="Allert" Caption="Allert"/>
                                    <Member Value="Service" Caption="Service"/>
                                    <Member Value="Marketing" Caption="Marketing"/>
                                </Control>
                                <Control Id="Process" Caption="Process" FieldName="Process" DataType="string" Width="90" CaptionWidth="70" ReadOnly="1" Mandatory="1" Hidden="1"/>
                                <Control Id="FindBy" Caption="Find:" VCaption="Find By" FieldName="FindBy" DataType="select" Width="90" CaptionWidth="50" ReadOnly="0" Mandatory="0" CaptionCSS="font-weight:bold" DefaultValue="IMTSI">
                                    <Member Value="IMTSI" Caption="IMTSI"/>
                                    <Member Value="ID" Caption="ID"/>
                                </Control>
                                <Control Id="Find" Caption="Find Value" FieldName="Find" DataType="string" Width="90" CaptionWidth="60" ReadOnly="0" Mandatory="0" CaptionCSS="font-weight:bold"/>
                            </Controls>
                        </Form>
                    </Containers>
                </Section>
                <Section Id="1" Type="page" Caption="tech visit sms reminder">
                    <Containers>
                        <Form Id="General" Caption="General Details" Source="Request" Layout="Fixed">
                            <Controls>
                                <Control Id="IMTSI" Caption="IMTSI" Source="Param[@Name = ''IMTSI'']" FieldName="Value" DataType="string" Width="120" CaptionWidth="120" BreakAfter="1" Mandatory="0" Hidden="1"/>
                                <Control Id="MessageType" Caption="Message Type" Source="Param[@Name = ''MessageType'']" FieldName="Value" DataType="select" Width="150" CaptionWidth="120" ReadOnly="1" Mandatory="1" DefaultValue="Allert" Action="handleMessageTypeChange()" BreakAfter="1">
                                    <Member Value="SMS" Caption="SMS"/>
                                    <Member Value="EMAIL" Caption="EMAIL"/>
                                    <Member Value="ATOS" Caption="ATOS"/>
                                    <Member Value="SELF SERVICE" Caption="SELF SERVICE"/>
                                </Control>
                                <Control Id="Language" Caption="Language" Source="Param[@Name = ''Language'']" FieldName="Value" DataType="select" Width="120" CaptionWidth="120" ReadOnly="0" BreakAfter="1" SelectionType="single" Hidden="0" SeparateAfter="1">
                                    <Member Value="heb" Caption="heb"/>
                                    <Member Value="eng" Caption="eng"/>
                                </Control>
                                <Control Id="Mobile" Caption="Mobile" Source="Param[@Name = ''Mobile'']" FieldName="Value" DataType="string" Width="120" CaptionWidth="120" BreakAfter="1" Mandatory="1" SeparateAfter="0"/>
                                <Control Id="FirstName" Caption="First Name" Source="Param[@Name = ''FirstName'']" FieldName="Value" DataType="string" Width="120" BreakAfter="1" CaptionWidth="120" Mandatory="1"/>
                                <Control Id="Visitday" Caption="Visit day" Source="Param[@Name = ''Visitday'']" FieldName="Value" DataType="select" Width="120" CaptionWidth="120" ReadOnly="0" BreakAfter="1" Mandatory="1" SeparateBefore="1" SeparateAfter="1">
                                    <Member Value="&#x5D0;" Caption="&#x5E8;&#x5D0;&#x5E9;&#x5D5;&#x5DF;"/>
                                    <Member Value="&#x5D1;" Caption="&#x5E9;&#x5E0;&#x5D9;"/>
                                    <Member Value="&#x5D2;" Caption="&#x5E9;&#x5DC;&#x5D9;&#x5E9;&#x5D9;"/>
                                    <Member Value="&#x5D3;" Caption="&#x5E8;&#x5D1;&#x5D9;&#x5E2;&#x5D9;"/>
                                    <Member Value="&#x5D4;" Caption="&#x5D7;&#x5DE;&#x5D9;&#x5E9;&#x5D9;"/>
                                </Control>
                                <Control Id="Date" Caption="Visit date" Source="Param[@Name = ''Date'']" FieldName="Value" DataType="date" EnableTime="0" Width="120" CaptionWidth="120" Height="60" BreakAfter="1" MaxLen="20" Mandatory="1" SeparateBefore="1" SeparateAfter="1"/>
                                <Control Id="Visithours" Caption="Visit hours" Source="Param[@Name = ''Visithours'']" FieldName="Value" DataType="string" EnableTime="1" Width="120" CaptionWidth="120" Height="60" BreakAfter="1" MaxLen="20" Mandatory="1" SeparateBefore="1" SeparateAfter="1"/>
                                <Control Id="Email" Caption="Email" Source="Param[@Name = ''Email'']" FieldName="Value" DataType="string" Width="350" CaptionWidth="120" BreakAfter="1" Mandatory="0" Hidden="1"/>
                                <Control Id="Subject" Caption="Subject" Source="Param[@Name = ''Subject'']" FieldName="Value" DataType="string" Width="350" CaptionWidth="120" BreakAfter="1" MaxLen="40" Hidden="1"/>
                                <Control Id="Sender" Caption="Sender" Source="Param[@Name = ''Sender'']" FieldName="Value" DataType="string" Width="350" CaptionWidth="120" ReadOnly="1" BreakAfter="1"/>
                                <Control Id="Priority" Caption="Priority" Source="Param[@Name = ''Priority'']" FieldName="Value" DataType="select" Width="350" CaptionWidth="120" BreakAfter="1" DefaultValue="LOW" Hidden="1">
                                    <Member Value="LOW" Caption="Low" Glyf="../../GUIGenerator_V2/assets/themes/Images/flag_blue.png"/>
                                    <Member Value="MED" Caption="Medium" Glyf="../../GUIGenerator_V2/assets/themes/Images/flag_yellow.png"/>
                                    <Member Value="HIGH" Caption="High" Glyf="../../GUIGenerator_V2/assets/themes/Images/flag_red.png"/>
                                </Control>
                            </Controls>
                        </Form>
                    </Containers>
                </Section>
            </Module>
            <DefaultData>
                <Request Origin="ISRAEL" Type="Service" Process="17">
                    <Param Name="MessageType" Type="Field" Value="SMS"/>
                    <Param Name="FirstName" Type="Field"/>
                    <Param Name="Visitday" Type="Field"/>
                    <Param Name="Date" Type="Field"/>
                    <Param Name="Visithours" Type="Field"/>
                    <Param Name="Mobile" Type="Field"/>
                    <Param Name="Sender" Type="Field" Value="BTP|*5755"/>
                    <Param Name="Priority" Type="Field" Value="LOW"/>
                    <Param Name="Language" Type="Field" Value="heb"/>
                </Request>
            </DefaultData>
        </Legend>
        <Data/>
    </Screen>'
where UID like '4'

and the SELECT statement:

和SELECT语句：

SELECT * FROM Screen_Template WHERE env LIKE '" + env + "' AND OpCo LIKE '" + opco + "' AND UID LIKE '" + uid + "'"

1 个解决方案

#1

The xml data type has full support for Unicode and will not convert letters to question marks. Something else is going on. It could be one of the following reasons.

xml数据类型完全支持Unicode，不会将字母转换为问号。还有其他事情正在发生。这可能是以下原因之一。

Your OS does not have the correct fonts for Hebrew.
您的操作系统没有希伯来语的正确字体。
The INSERT part of your client app is corrupting the source data into a single-byte character set (e.g using varchar instead of nvarchar).
客户端应用程序的INSERT部分正在将源数据破坏为单字节字符集（例如，使用varchar而不是nvarchar）。
The SELECT part of your client app is corrupting the table data into a single-byte character set (e.g using varchar instead of nvarchar).
客户端应用程序的SELECT部分正在将表数据破坏为单字节字符集（例如，使用varchar而不是nvarchar）。

Update based on poster's new code sample:

根据海报的新代码示例更新：

Your INSERT statement is corrupting Unicode text by using single-byte string constants e.g. 'text' instead of Unicode constants e.g. N'some text'. You need to prefix all string literals with a capital N to ensure uNicode is used.

您的INSERT语句通过使用单字节字符串常量来破坏Unicode文本，例如'text'而不是Unicode常量，例如N'some text'。您需要在所有字符串文字前加上大写字母N，以确保使用uNicode。

UPDATE db_owner.Screen_Template
SET template_xml = 
N'<Screen> ... </Screen>'

#1