title: SQL-SERVER Select UPDATE OUTPUT as XML
body: <p>Running on SQL Server 2016.</p>
  
  <p>I have a routine that updates information across servers.  I want to hold a list of any changes that I have been required to make.  I am trying to output the changed columns as XML for basic storage, and would like to do this directly from the OUTPUT generated by the insert/update/delete if possible.</p>
  
  <p>As an example:</p>
  
  <pre><code>DROP TABLE IF EXISTS Test
  CREATE TABLE Test (myKey INT, myValue INT)
  INSERT INTO dbo.Test (myKey, myValue)
  VALUES (1, 1), (2, 2), (3, 3)
  
  UPDATE  dbo.Test
  SET     myValue = myValue + 10
  OUTPUT  Deleted.*
  ,       Inserted.*
  WHERE   myKey &lt; 3
  
  SELECT  *
  FROM    dbo.Test
  FOR     XML AUTO
  
  DROP TABLE dbo.Test
  </code></pre>
  
  <p>I know I can set up a TVP to receive the output and then convert to XML from there, but it seems like I'm taking extra steps to do something that should be quite straight forward. </p>
  
  <pre><code>DROP TABLE IF EXISTS Test
  CREATE TABLE Test (myKey INT, myValue INT)
  INSERT INTO dbo.Test (myKey, myValue)
  VALUES (1, 1), (2, 2), (3, 3)
  
  DECLARE @OutputValues AS TABLE(dMyKey INT, dMyValue INT, iMyKey INT, iMyValue INT)
  
  UPDATE  dbo.Test
  SET     myValue = myValue + 10
  OUTPUT  Deleted.myKey 
  ,       Deleted.myValue 
  ,       Inserted.myKey 
  ,       Inserted.myValue 
          INTO    @OutputValues
  WHERE   myKey &lt; 3
  
  SELECT  *
  FROM    @OutputValues
  FOR     XML AUTO
  
  DROP TABLE dbo.Test 
  </code></pre>
  
  <p>While this second piece of code does achieve the sort of output I am looking for, going via a TVP seems to be a bit wasteful.</p>
  
  <p>If I can format the output from the original code directly as XML I feel that would be a better solution.  However, I can't see any way of doing so.</p>
  
  <p>Many Thanks.</p>