title: How to replace XML variable through SQL
body: <p><code>chtml</code> columns of <code>ntext</code>:</p>
  
  <pre><code>&lt;root&gt;
      &lt;Phys&gt;
            &lt;name&gt;Doctor1&lt;/name&gt;
            &lt;gender&gt;M&lt;/gender&gt;
            &lt;langF1&gt;&lt;a title="English" href="/default.aspx?id=23"&gt;English&lt;/a&gt;&lt;/langF1&gt;
      &lt;/Phys&gt;
  &lt;/root&gt;
  &lt;root&gt;
      &lt;Phys&gt;
           &lt;name&gt;Doctor2&lt;/name&gt;
           &lt;gender&gt;F&lt;/gender&gt;
           &lt;langF1&gt;&lt;a title="French" href="/default.aspx?id=56"&gt;French&lt;/a&gt;&lt;/langF1&gt;
      &lt;/Phys&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>Staging table (<code>tableStage</code>):</p>
  
  <pre><code>Name        Gender      Language 1
  Doctor1     F           French
  Doctor2     M           Spanish
  </code></pre>
  
  <p>Stored procedure which updates the <code>chtml</code> column in a table from the value from the staging table:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[Pro]
  (
      @ProviderName varchar(200), -- "Doctor1" for example
      @genderVal varchar(10),
      @langID varchar(10),
      @langTitle varchar(60),
      @langUrl varchar(255),
      @anchorLang varchar(255),
      @langLinkXML xml,
  )
  AS
  BEGIN
      DECLARE @xml xml
  
      Select @xml = cast([chtml] as xml) --cast the chtml column which is of ntext type
      From [myDb].[dbo].[myTable] --to xml so we can update the nodes
      Where [content_title] = @ProviderName
  
      --Update gender val (if exists, update, otherwise insert)
      Select @genderVal = t1.[Gender] From [myDb].[dbo].[tableStage] t1 --replace Value2 and table to the table which is updated through SSIS
      Where t1.Name = @ProviderName
      PRINT @genderVal
  
      If @xml IS NOT NULL
      BEGIN
          Set @xml.modify('replace value of (/root/Physicians/gender/text())[1] with sql:variable("@genderVal")');
      END
      Else
      BEGIN
          PRINT 'NOT WORK'
      END
  
      /* ERROR for above is:  "XQuery: Replacing the value of a node with an empty sequence is allowed only if '()' is used as the new value expression. The new value expression evaluated to an empty sequence but it is not '()'." instead of changing the gender to "F" */
  
  --Update language one field (if exist, delete and add, otherwise just delete)
      --get the language id from the temp language table which matches the language for the provider
      Select @langID = t1.content_id From [myDb].[dbo].[zTempLanguageTable] t1
      INNER JOIN [myDb].[dbo].[tableStage] t2 On t2.[Language 1] = t1.content_title
      Where t2.Name = @ProviderName
  
      If @langID != ''
      BEGIN
          --get the language title from the language id
          Select @langTitle = t1.content_title From [myDb].[dbo].[zTempLanguageTable] t1
          Where @langID = t1.content_id
  
          --set the Url (which will be part static and part dynamic with the id)
          Set @langUrl = '/default.aspx?id=' + @langID + '';
  
          --delete the anchor entry
          Set @xml.modify('delete /root/Physicians/langF1/a');
          --create the XML for the node
          Set @langLinkXML = (
              Select @langTitle as 'a/@title',
                  @langUrl as 'a/@href',
                  '_blank' as 'a/@target',
                  @langTitle as 'a'
                  for xml path(''), type
              );
  
          -- insert the new/updated anchor node
          Set @xml.modify('insert sql:variable("@langLinkXML") into (/root/Physicians/langF1)[1]');
      END
      Else
      BEGIN
          --delete the anchor entry
          Set @xml.modify('delete /root/Physicians/langF1/a');
      END
  
      /* THE ABOVE CHANGE `chtml` to:  "&lt;root&gt;&lt;Physicians&gt;&lt;name&gt;Doctor1&lt;/name&gt;&lt;gender&gt;M&lt;/gender&gt;&lt;/langF1&gt;&lt;/Phys&gt;&lt;/root&gt;" instead of &lt;root&gt;&lt;Physicians&gt;&lt;name&gt;Doctor1&lt;/name&gt;&lt;gender&gt;M&lt;/gender&gt;&lt;langF1&gt;&lt;a title="French" href="/default.aspx?id=67"&gt;French&lt;/a&gt;&lt;/langF1&gt;&lt;/Phys&gt;&lt;/root&gt; */
  
  /* once all the node has been "temporarily" changed, update the table columns for the provider */
  Update [myDb].[dbo].[myTable]
  Set [chtml] = cast(cast(@xml as nvarchar(max)) as ntext) //change the type to `ntext` data type for the table.
  Where [content_title] = @ProviderName
  
  END
  </code></pre>
  
  <p>How can I resolve the two issues I am having:</p>
  
  <ul>
  <li><p>/* ERROR for above is:  "XQuery: Replacing the value of a node with
  an empty sequence is allowed only if '()' is used as the new value
  expression. The new value expression evaluated to an empty sequence
  but it is not '()'." instead of changing the gender to "F" */</p></li>
  <li><p>/* THE ABOVE CHANGE <code>chtml</code> to: 
  "Doctor1M"
  instead of
  Doctor1MFrench */</p></li>
  </ul>