title: Store Unicode data in SQL Server 2008 XML datatype
body: <p>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?</p>
  
  <p>This is my UPDATE statement:</p>
  
  <pre><code>update db_owner.Screen_Template set template_xml = 
  '&lt;?xml version="1.0"?&gt;
      &lt;Screen xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../GUIGenerator_V2/Scheme/GG_Scheme.xsd"&gt;
          &lt;Legend EntityType="Request"&gt;
              &lt;Module ForceTabs="1"&gt;
                  &lt;Events&gt;
                      &lt;Event Value="onafter_moduleinit_beforedraw()" Type="onafter_moduleinit_beforedraw"/&gt;
                  &lt;/Events&gt;
                  &lt;Section Id="Header" Type="header" Caption="Header" ContainerCSS="background:#C1E0F4;border-bottom:2px groove;margin-bottom:3px;padding:10 3 3 3"&gt;
                      &lt;Containers&gt;
                          &lt;Form Id="Header" Caption="General Details" Source="Request" Layout="Fixed"&gt;
                              &lt;Layout&gt;
                                  &lt;table cellaspacing="0" cellpadding="0"&gt;
                                      &lt;tr&gt;
                                          &lt;td&gt;
                                              &lt;ControlRef Id="Origin" ShowCaption="1"/&gt;
                                          &lt;/td&gt;
                                          &lt;td&gt;
                                              &lt;ControlRef Id="Type" ShowCaption="1"/&gt;
                                          &lt;/td&gt;
                                          &lt;td&gt;
                                              &lt;ControlRef Id="Process" ShowCaption="1"/&gt;
                                          &lt;/td&gt;
                                          &lt;td width="20" align="center"&gt;
                                              &lt;span class="separator"/&gt;
                                          &lt;/td&gt;
                                          &lt;td&gt;
                                              &lt;ControlRef Id="FindBy" ShowCaption="1"/&gt;
                                          &lt;/td&gt;
                                          &lt;td&gt;
                                              &lt;ControlRef Id="Find"/&gt;
                                          &lt;/td&gt;
                                          &lt;td&gt;
                                              &lt;button width="30" onclick="doFindCustomer()"&gt;
                                                  &lt;Caption Caption="GO"/&gt;
                                              &lt;/button&gt;
                                          &lt;/td&gt;
                                      &lt;/tr&gt;
                                  &lt;/table&gt;
                              &lt;/Layout&gt;
                              &lt;Controls&gt;
                                  &lt;Control Id="Origin" Caption="Origin" FieldName="Origin" DataType="string" Width="90" CaptionWidth="70" ReadOnly="1" Mandatory="1" Hidden="1"/&gt;
                                  &lt;Control Id="Type" Caption="Type" FieldName="Type" DataType="select" Width="60" CaptionWidth="40" ReadOnly="1" Mandatory="1" DefaultValue="Service" Hidden="1"&gt;
                                      &lt;Member Value="Allert" Caption="Allert"/&gt;
                                      &lt;Member Value="Service" Caption="Service"/&gt;
                                      &lt;Member Value="Marketing" Caption="Marketing"/&gt;
                                  &lt;/Control&gt;
                                  &lt;Control Id="Process" Caption="Process" FieldName="Process" DataType="string" Width="90" CaptionWidth="70" ReadOnly="1" Mandatory="1" Hidden="1"/&gt;
                                  &lt;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"&gt;
                                      &lt;Member Value="IMTSI" Caption="IMTSI"/&gt;
                                      &lt;Member Value="ID" Caption="ID"/&gt;
                                  &lt;/Control&gt;
                                  &lt;Control Id="Find" Caption="Find Value" FieldName="Find" DataType="string" Width="90" CaptionWidth="60" ReadOnly="0" Mandatory="0" CaptionCSS="font-weight:bold"/&gt;
                              &lt;/Controls&gt;
                          &lt;/Form&gt;
                      &lt;/Containers&gt;
                  &lt;/Section&gt;
                  &lt;Section Id="1" Type="page" Caption="tech visit sms reminder"&gt;
                      &lt;Containers&gt;
                          &lt;Form Id="General" Caption="General Details" Source="Request" Layout="Fixed"&gt;
                              &lt;Controls&gt;
                                  &lt;Control Id="IMTSI" Caption="IMTSI" Source="Param[@Name = ''IMTSI'']" FieldName="Value" DataType="string" Width="120" CaptionWidth="120" BreakAfter="1" Mandatory="0" Hidden="1"/&gt;
                                  &lt;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"&gt;
                                      &lt;Member Value="SMS" Caption="SMS"/&gt;
                                      &lt;Member Value="EMAIL" Caption="EMAIL"/&gt;
                                      &lt;Member Value="ATOS" Caption="ATOS"/&gt;
                                      &lt;Member Value="SELF SERVICE" Caption="SELF SERVICE"/&gt;
                                  &lt;/Control&gt;
                                  &lt;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"&gt;
                                      &lt;Member Value="heb" Caption="heb"/&gt;
                                      &lt;Member Value="eng" Caption="eng"/&gt;
                                  &lt;/Control&gt;
                                  &lt;Control Id="Mobile" Caption="Mobile" Source="Param[@Name = ''Mobile'']" FieldName="Value" DataType="string" Width="120" CaptionWidth="120" BreakAfter="1" Mandatory="1" SeparateAfter="0"/&gt;
                                  &lt;Control Id="FirstName" Caption="First Name" Source="Param[@Name = ''FirstName'']" FieldName="Value" DataType="string" Width="120" BreakAfter="1" CaptionWidth="120" Mandatory="1"/&gt;
                                  &lt;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"&gt;
                                      &lt;Member Value="&amp;#x5D0;" Caption="&amp;#x5E8;&amp;#x5D0;&amp;#x5E9;&amp;#x5D5;&amp;#x5DF;"/&gt;
                                      &lt;Member Value="&amp;#x5D1;" Caption="&amp;#x5E9;&amp;#x5E0;&amp;#x5D9;"/&gt;
                                      &lt;Member Value="&amp;#x5D2;" Caption="&amp;#x5E9;&amp;#x5DC;&amp;#x5D9;&amp;#x5E9;&amp;#x5D9;"/&gt;
                                      &lt;Member Value="&amp;#x5D3;" Caption="&amp;#x5E8;&amp;#x5D1;&amp;#x5D9;&amp;#x5E2;&amp;#x5D9;"/&gt;
                                      &lt;Member Value="&amp;#x5D4;" Caption="&amp;#x5D7;&amp;#x5DE;&amp;#x5D9;&amp;#x5E9;&amp;#x5D9;"/&gt;
                                  &lt;/Control&gt;
                                  &lt;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"/&gt;
                                  &lt;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"/&gt;
                                  &lt;Control Id="Email" Caption="Email" Source="Param[@Name = ''Email'']" FieldName="Value" DataType="string" Width="350" CaptionWidth="120" BreakAfter="1" Mandatory="0" Hidden="1"/&gt;
                                  &lt;Control Id="Subject" Caption="Subject" Source="Param[@Name = ''Subject'']" FieldName="Value" DataType="string" Width="350" CaptionWidth="120" BreakAfter="1" MaxLen="40" Hidden="1"/&gt;
                                  &lt;Control Id="Sender" Caption="Sender" Source="Param[@Name = ''Sender'']" FieldName="Value" DataType="string" Width="350" CaptionWidth="120" ReadOnly="1" BreakAfter="1"/&gt;
                                  &lt;Control Id="Priority" Caption="Priority" Source="Param[@Name = ''Priority'']" FieldName="Value" DataType="select" Width="350" CaptionWidth="120" BreakAfter="1" DefaultValue="LOW" Hidden="1"&gt;
                                      &lt;Member Value="LOW" Caption="Low" Glyf="../../GUIGenerator_V2/assets/themes/Images/flag_blue.png"/&gt;
                                      &lt;Member Value="MED" Caption="Medium" Glyf="../../GUIGenerator_V2/assets/themes/Images/flag_yellow.png"/&gt;
                                      &lt;Member Value="HIGH" Caption="High" Glyf="../../GUIGenerator_V2/assets/themes/Images/flag_red.png"/&gt;
                                  &lt;/Control&gt;
                              &lt;/Controls&gt;
                          &lt;/Form&gt;
                      &lt;/Containers&gt;
                  &lt;/Section&gt;
              &lt;/Module&gt;
              &lt;DefaultData&gt;
                  &lt;Request Origin="ISRAEL" Type="Service" Process="17"&gt;
                      &lt;Param Name="MessageType" Type="Field" Value="SMS"/&gt;
                      &lt;Param Name="FirstName" Type="Field"/&gt;
                      &lt;Param Name="Visitday" Type="Field"/&gt;
                      &lt;Param Name="Date" Type="Field"/&gt;
                      &lt;Param Name="Visithours" Type="Field"/&gt;
                      &lt;Param Name="Mobile" Type="Field"/&gt;
                      &lt;Param Name="Sender" Type="Field" Value="BTP|*5755"/&gt;
                      &lt;Param Name="Priority" Type="Field" Value="LOW"/&gt;
                      &lt;Param Name="Language" Type="Field" Value="heb"/&gt;
                  &lt;/Request&gt;
              &lt;/DefaultData&gt;
          &lt;/Legend&gt;
          &lt;Data/&gt;
      &lt;/Screen&gt;'
  where UID like '4'
  </code></pre>
  
  <p>and the SELECT statement:</p>
  
  <pre><code>SELECT * FROM Screen_Template WHERE env LIKE '" + env + "' AND OpCo LIKE '" + opco + "' AND UID LIKE '" + uid + "'"
  </code></pre>