Jay Kimble

So I was just tasked with fixing a routine in a trigger that was capturing the before/after data snapshots as XML.  The problem with the original code was that it wouldn't work with bulk updates. 

So here's a snippet that I came up with to help me see how I could return a resultset from T-SQL (which could be fed into an insert) that contains a single column and then the all of the data as XML.  (NOTE: temp tables are used here simply for the use of example... they are not necessarily the best choice for good T_SQL development)

   1: SELECT *

   2: INTO #i

   3: FROM

   4: (

   5:     SELECT AccountID = 1, UserName = 'Fred'

   6:     union

   7:     SELECT AccountID = 2, UserName = 'Wilma'

   8:     union

   9:     SELECT AccountID = 3, UserName = 'Pebbles'

  10:     union

  11:     SELECT AccountID = 4, UserName = 'Barney'

  12:     union

  13:     SELECT AccountID = 5, UserName = 'Betty'

  14: ) i;

  15:  

  16: SELECT ii.AccountID AS MasterAccount, (SELECT ii.* FOR xml raw) AS xml

  17: FROM #i ii

  18:  

  19: DROP TABLE #i;

The trick is this snippet -

   1: (SELECT ii.* FOR xml raw) AS xml

Basically we are selecting all from the table's alias (which is really the individual row) and are putting it into XML...

The resulting XML for AccountID=1 looks like this:

   1: <row AccountID="1" UserName="Fred"/>

Since I haven't seen an example of this I thought the Google or Windows Live needed an article.

[tags:TSQL,XML,SQLXML]