title: Querying XML column in SQL Server
body: <p>I'm running SQL Server 2008 R2, and have a table that I audit update statements.</p>
  
  <pre><code>create table STG_Participant_16_Month
  (
  Serial                  int,
  ID                      varchar(10),
  StartTime               datetime, 
  FinishTime              datetime, 
  ChildID                 varchar(10),
  childIndex              int,
  Record_State            varchar(15),
  Duplicate_flag          varchar(1)
  );
  </code></pre>
  
  <p>When table X is updated, it inserts a record into an audit table:</p>
  
  <pre><code>select * 
  into STG_Participant_16_Month_AUDIT 
  from STG_Participant_16_Month;
  
  alter table STG_Participant_16_Month_AUDIT
  add audit_user                       varchar(30),
      audit_action                     varchar(1),
      audit_date                       datetime,
      columns_updated                  xml;
  </code></pre>
  
  <p>I create a record and do an update:</p>
  
  <pre><code>insert into STG_Participant_16_Month
  ( Serial, ID, StartTime, FinishTime, ChildID, childIndex,
    Record_State, Duplicate_flag )
  values
  (  90, 'ID', getdate(), getdate(), 'ChildID', 1,
    'LOADED', 'N');
  
  update STG_Participant_16_Month set serial=99, ID='xx', childIndex=99  where serial=90;
  </code></pre>
  
  <p>I see output as follows:</p>
  
  <pre><code>&lt;Fields&gt;
    &lt;Field Name="Serial" /&gt;
    &lt;Field Name="ID" /&gt;
    &lt;Field Name="childIndex" /&gt;
  &lt;/Fields&gt;
  </code></pre>
  
  <p>How do I create a query which just shows the text values?</p>
  
  <pre><code>Serial
  ID
  childIndex
  </code></pre>