title: Sql Xquery How to Replace text in Update Query
body: <p>Table is named as <code>MasterTable</code></p>
  
  <p>Columns</p>
  
  <p><code>ID</code> type <code>BIGINT</code>,</p>
  
  <p><code>Name</code> type <code>VARCHAR(200)</code> (stores <code>xml</code> type data for some reasons)</p>
  
  <p><code>Name</code> contains data structured as</p>
  
  <p><code>&lt;en-US&gt;SomeEnglishText&lt;/en-US&gt;&lt;it-IT&gt;SomeItalicText&lt;/it-IT&gt;</code></p>
  
  <p>When I need to <code>Update</code> the <code>Master</code> Table then at that time I Need to cast the <code>Varchar</code> to <code>xml</code> then conditionally <strong>update / replace</strong> the <code>value</code> part of particular tag i.e either <code>en-US / it-IT</code>.</p>
  
  <p>Also there are chances that <strong>No data/tags</strong> are there in <code>Name</code> column so I think at the time of Inserting data it would <code>Insert</code> empty tag elements in the table like <code>&lt;en-US&gt;&lt;/en-US&gt;&lt;it-IT&gt;&lt;/it-IT&gt;</code>, so the <code>update</code> query <strong>must</strong> handle <strong>empty value</strong> in tag elements namely <code>en-US/it-IT</code>.</p>
  
  <p>I am trying to do it like following update query.</p>
  
  <pre><code>DECLARE @Str VARCHAR(200)
  
  SET @Str = 'Test Text'
  
  UPDATE [MasterTable]
  SET [Name] = cast([MasterTable].[Name] as xml).modify('replace value of (en-US/text())[1] with sql:variable("@Str")')
  WHERE [ID]=18
  </code></pre>
  
  <p>I getting following error when running the query </p>
  
  <blockquote>
    <p>Illegal use of xml data type method 'modify'. A non-mutator method is expected in this context.</p>
  </blockquote>