title: CONVERTING SQL NVARCHAR(MAX) TO XML and getting a Value from XML string
body: <p>I am trying read from the nodes of a column -where an XML string is stored. The column is of type NVARCHAR(MAX).</p>
  
  <p>The following is the script to create table - 
      SET ANSI_NULLS ON
      GO</p>
  
  <pre><code>SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[XML_Dummy](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [XMLValue] [nvarchar](max) NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  
  GO
  </code></pre>
  
  <p>The following is the script to enter values to it -</p>
  
  <pre><code>INSERT INTO [dbo].[XML_Dummy]
             ([Name]
             ,[XMLValue])
       VALUES
             ('abcd'
             ,'&lt;?xml version="1.0" encoding="UTF-8"?&gt;
              &lt;STAFFv xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
                   &lt;EMPLOYEE_NUMBER&gt;123456&lt;/EMPLOYEE_NUMBER&gt;
                  &lt;TITLE&gt;Mr&lt;/TITLE&gt;
                  &lt;INITIALS&gt;J&lt;/INITIALS&gt;
                  &lt;FORENAME&gt;PEARL&lt;/FORENAME&gt;
                  &lt;SURNAME&gt;HOFFMAN&lt;/SURNAME&gt;
                  &lt;GENDER&gt;MALE&lt;/GENDER&gt;
                  &lt;DATE_OF_BIRTH&gt;1992-01-01&lt;/DATE_OF_BIRTH&gt;
                    &lt;DEPARTMENT_DESC&gt;SUPER SUPPORT TEAM&lt;/DEPARTMENT_DESC&gt;
                    &lt;JOB_TITLE_DESC&gt;GENERAL DOGSBODY&lt;/JOB_TITLE_DESC&gt;
                    &lt;ORIGINAL_DATE_JOINED&gt;2014-05-01&lt;/ORIGINAL_DATE_JOINED&gt;
                    &lt;CURRENT_EMPLOYEE financialyear="2014"&gt;Y&lt;/CURRENT_EMPLOYEE&gt;
                    &lt;INTERNAL_EMAIL xsi:nil="true" /&gt;
                    &lt;CHANGE_TYPE&gt;INSERT&lt;/CHANGE_TYPE&gt;
                    &lt;CHANGE_DATE&gt;2014-03-27&lt;/CHANGE_DATE&gt;
              &lt;/STAFF&gt;')
  GO
  </code></pre>
  
  <p>My goal is to get the GENDER from this string.
  However to start with when I am writing the following query - </p>
  
  <pre><code>     declare @XMLVALUECAST_ xml
      declare @XMLVALUECONVERT_ xml
  
   SELECT 
           @XMLVALUECAST_ = CAST(XMLValue AS XML),
           @XMLVALUECONVERT_ = CONVERT(XML, XMLValue)
  
        --CAST(CAST(CAST(XMLValue AS NTEXT) AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender 
   FROM  
      [dbo].[XML_Dummy]
  
      print @XMLVALUECAST_
      PRINT @XMLVALUECONVERT_
  </code></pre>
  
  <p>I get the error - 
  Msg 9402, Level 16, State 1, Line 3
  XML parsing: line 1, character 38, unable to switch the encoding.</p>
  
  <p>I am using SQL Server 2012 (11.0.5058.0)</p>