title: Shred SQL Server XML with Unicode characters
body: <p>In my previous question 
  <a href="https://stackoverflow.com/questions/15126430/sql-server-xml-string-manipluation">SQL Server XML String Manipluation</a></p>
  
  <p>I was given the answer below (Thanks Mikael Eriksson) to shred an XML document, and strip out unwanted words out characters. I now need to take it a step further, and strip out Unicode characters over 255. When I have those characters in my XML, they get stored in the @T table variable (in the code below) as question marks. How can I get those characters to come through as the actual Unicode characters, so I can strip them out?</p>
  
  <p>I have a function that works nicely to remove the unwanted characters, but since the Unicode comes in as question marks, it doesn't touch them</p>
  
  <pre><code> -- A table to hold the bad words
  declare @BadWords table
  (
    ID int identity,
    Value nvarchar(10)
  )
  
  -- These are the bad ones.
  insert into @BadWords values
  ('one'),
  ('three'),
  ('five'),
  ('hold')
  
  -- XML that needs cleaning
  declare @XML xml = '
  &lt;root&gt;
    &lt;itemone ID="1one1"&gt;1one1&lt;/itemone&gt;
    &lt;itemtwo&gt;2two2&lt;/itemtwo&gt;
    &lt;items&gt;
      &lt;item&gt;1one1&lt;/item&gt;
      &lt;item&gt;2two2&lt;/item&gt;
      &lt;item&gt;onetwothreefourfive&lt;/item&gt;
    &lt;/items&gt;
    &lt;hold&gt;We hold these truths to be self evident&lt;/hold&gt;
  &lt;/root&gt;
  '
  
  -- A helper table to hold the values to modify
  declare @T table
  (
    ID int identity,
    Pos int,
    OldValue nvarchar(max),
    NewValue nvarchar(max),
    Attribute bit
  )
  
  -- Get all attributes from the XML
  insert into @T(Pos, OldValue, NewValue, Attribute)
  select row_number() over(order by T.N),
         T.N.value('.', 'nvarchar(max)'),
         T.N.value('.', 'nvarchar(max)'),
         1
  from @XML.nodes('//@*') as T(N)
  
  -- Get all values from the XML
  insert into @T(Pos, OldValue, NewValue, Attribute)
  select row_number() over(order by T.N),
         T.N.value('text()[1]', 'nvarchar(max)'),
         T.N.value('text()[1]', 'nvarchar(max)'),
         0
  from @XML.nodes('//*') as T(N)
  
  declare @ID int
  declare @Pos int
  declare @Value nvarchar(max)
  declare @Attribute bit
  
  -- Remove the bad words from @T, one bad word at a time
  select @ID = max(ID) from @BadWords
  while @ID &gt; 0
  begin
    select @Value = Value
    from @BadWords
    where ID = @ID
  
    update @T
    set NewValue = replace(NewValue, @Value, '')
  
    set @ID -= 1
  end
  
  -- Write the cleaned values back to the XML
  select @ID = max(ID) from @T
  while @ID &gt; 0
  begin
    select @Value = nullif(NewValue, OldValue),
           @Attribute = Attribute,
           @Pos = Pos
    from @T
    where ID = @ID
  
    print @Attribute
  
    if @Value is not null
      if @Attribute = 1  
        set @XML.modify('replace value of ((//@*)[sql:variable("@Pos")])[1] 
                         with sql:variable("@Value")')
      else
        set @XML.modify('replace value of ((//*)[sql:variable("@Pos")]/text())[1] 
                             with sql:variable("@Value")')
    set @ID -= 1
  end
  
  select @XML
  </code></pre>