title: XQuery a SQL Server NVARCHAR column
body: <p>I have a table with a <code>nvarchar</code> column which stores XML. I'm trying to find all rows which has a specific node empty</p>
  
  <p>Here is the structure of the table <code>JOB</code>:</p>
  
  <pre><code>ID           NVARCAHR(64)
  NAME         NVARCAHR(128) 
  ExtraInfo    NVARCAHR(MAX)
  </code></pre>
  
  <p>Here is  a sample of the content of the column <code>ExtraInfo</code>, I'm looking for:</p>
  
  <pre><code>&lt;ModifyMetadata&gt;
      &lt;hostName t="ws"&gt;host2&lt;/hostName&gt;
      &lt;Name t="ws"&gt;myname&lt;/Name&gt;
      &lt;modifiedFields t="lt"&gt;
          &lt;ExtraInfoAdditionalField&gt;
              &lt;FieldName t="ws"&gt;foo&lt;/FieldName&gt;
              &lt;Values t="lws"&gt;
                  &lt;s/&gt;
              &lt;/Values&gt;
              &lt;FieldType t="ws"&gt;String&lt;/FieldType&gt;
          &lt;/ExtraInfoAdditionalField&gt;
          &lt;ExtraInfoAdditionalField&gt;
              &lt;FieldName t="ws"&gt;bar&lt;/FieldName&gt;
              &lt;Values t="lws"&gt;
                  &lt;s/&gt;
              &lt;/Values&gt;
              &lt;FieldType t="ws"&gt;String&lt;/FieldType&gt;
          &lt;/ExtraInfoAdditionalField&gt;
          &lt;/modifiedFields&gt;
  &lt;/ModifyMetadata&gt;
  </code></pre>
  
  <p>With this sample basically I'm looking for all rows with the node <code>ModifyMetadata</code> and where the nodes <code>ExtraInfoAdditionalField\Values</code> are empty (this is my understanding of what <code>&lt;s/&gt;</code> means).</p>
  
  <p>First I tried with a simple <code>LIKE</code> in with this query:</p>
  
  <pre><code>SELECT *  
  FROM [JOB]
  WHERE ExtraInfo LIKE '%\&gt;\&lt;\/s\&gt;%' ESCAPE '\' 
  </code></pre>
  
  <p>But this query also returns rows where the nodes <code>ExtraInfoAdditionalField\Values</code> have some content.</p>
  
  <p>The XQuery way: I switched to another method where I'm trying to <code>CAST</code> the column to XML and then return only the row where the nodes <code>ExtraInfoAdditionalField\Values</code> are empty.</p>
  
  <p>But then I'm getting confused about the methodolgy to query this column with XQuery or any other available methodology.</p>
  
  <p>I have tried to <code>CAST</code> directly in the <code>SELECT</code></p>
  
  <pre><code>SELECT CAST(ExtraInfo AS XML)
  FROM [JOB]
  WHERE ExtraInfo.value('XQUERYHERE') IS NULL
  </code></pre>
  
  <p>But SQL Server doesn't know what <code>ExtraInfo. value</code> is.</p>
  
  <p>I tried to <code>CAST</code> it in a variable:</p>
  
  <pre><code>DECLARE @ExtraInfo XML
  SET @ExtraInfo = CAST([MYDB].[dbo].[JOB].ExtraInfo AS XML)
  
  SELECT ExtraInfo 
  FROM [JOB]
  WHERE @ExtraInfo.value('XQUERYHERE') IS NULL
  </code></pre>
  
  <p>But SQL doesn't understand what is <code>[MYDB].[dbo].[JOB].ExtraInfo</code></p>
  
  <p>I have tried to <code>CAST</code> directly in the <code>SELECT</code> and rename it:</p>
  
  <pre><code>SELECT CAST(ExtraInfo AS XML) as myinfo
  FROM [JOB]
  WHERE myinfo.value('XQUERYHERE') IS NULL
  </code></pre>
  
  <p>But T-SQL doesn't know what <code>myinfo</code> is.</p>
  
  <p>So I'm clearly lacking of understanding in what way It possible to XML query a column which is stored in <code>NVARCHAR</code> format </p>
  
  <p>I hope this I this is clear enough, thanks in advance for any input</p>