title: To update XML data type column, repeatedly using a key, prefer approach that avoids the use of cursors
body: <p>I have a table with an ID (int type) and a blob of XML data (1 XML type column).
  I have 10 rows in the table, and I need to update each XML blob/row seperately. 
  Is there a way to do this:</p>
  
  <p>Instead of:</p>
  
  <pre><code>    --Works ok to update XML by explicitly entering XML, or one-by-one: 
          declare @XMLNODE table (id int identity, doc xml)
          insert @XMLNODE (doc) values (   
          '
           &lt;Root&gt;
              &lt;Elements&gt;
                &lt;Items&gt;
                  &lt;OldItem&gt;
  
                    &lt;ID&gt;1&lt;/ID&gt;
                    &lt;Show Pointer="yes" /&gt;
                    &lt;Display Pointer="Display"&gt;
                      &lt;Detail1&gt;some Details&lt;/Detail1&gt;
                    &lt;/DisplayDetails&gt;
  
                  &lt;/OldItem&gt;
                &lt;/Items&gt;
              &lt;/Elements&gt;
              &lt;/Root&gt;'
          )
  
    --This is just an XML variable to get the output, but not related to the ID in table:      
      DECLARE @XMLOutput XML
      SELECT @XMLOutput = ( 
                  SELECT  a.value('(ID)[1]','int') as ID,
                  a.value('(Show/@Pointer)[1]', 'varchar(5)') AS ShowItem,
                  a.value('Display[1]/@Pointer[1]="Display"', 'varchar(10)') as DisplayDetails, -- Set to 'true' or 'false'
                  a.value('DisplayDetails[1][@Pointer[1]="Display"]/Detail1[1]', 'varchar(max)') as Detail1
                  FROM    @XMLNODE t
                  cross apply
                  t.doc.nodes('//OldItem') x(a)
                  FOR XML PATH ('Items'),
                  ROOT('Elements')
  
          )
  </code></pre>
  
  <p>Instead, I want to do something like this: </p>
  
  <pre><code>Declare @tempTable table(ID int not null, XMLData xml)
  INSERT INTO @tempTable 
  SELECT OriginalTable.ID, 
     (--Perform above XML modification per row for each ID and put into a new table) 
  </code></pre>
  
  <p>--Do this per id to populate @tempTable with ID, XML not just return XML.
  Any ideas on getting this to work (is it possible even using a cursor).</p>