Get all values from an XML column

Question

I have a COTS application that has an audit table with an XML formatted column in it. I am trying to parse all the data so I can write an SSRS report to be customer facing. The application writes the original data and the changed value to the XML column when the system saves. This means that the column may contain one or many values and may be one of many types of data modified.

I am wanting to end up with a query that will show what data was changed and to what.

1. Can I write a query that would return all the values that exist in the column so in worst case scenario I could explicitly write out every value that has happened so far?

2. Is it possible to have it just dynamically pull the data while identifying what the data is?

Example XML Entry:

<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>

The transaction count above just indicates it was changed but isn't tracked. So I've just been doing a CASE statement that if it existed return a value and if not leave it NULL. In all the entries I've looked at the old value is always before the new value.

Each cell can have one or many entries and there is no way to tell what they will be ahead of time.

I started down the path looking in the cells and just calling each possible instance to extract the data.

XML.value('(LogMessage/Fields/Disabled/OldValue)[1]','varchar(5)') AS 'Old_Disabled'
XML.value('(LogMessage/Fields/Disabled/NewValue)[1]','varchar(5)') AS 'New_Disabled'

Then I was going to try to do some sort of concatenation logic when not null using TSQL.

I used this piece of code that returned all the values, but since it stripped all the tag information I couldn't determine what was changed

XML.value('(LogMessage/Fields)[1]','varchar(max)') AS 'Raw_Data'

The above string against the example would return this (all values,no spaces, no indication of what the value represents): ContractorEmployeeTRUEFALSE10/31/201812/31/2019

If there is a way to modify the above code to return something along the lines of this it would be great, but it would need to be dynamic.

PersonnelType Old: Contractor New: Employee

Disabled Old: True New: False

Expiration Old: 10/31/2018 NEW: 12/31/2019

Even this would be awesome:

PersonnelType Contractor, Employee

Disabled True, Yes

Expiration 10/31/2018, 12/31/2019

Answer 1

You did not state your expected output clearly, but this seems to be rather easy. XML can deal with generic structures quite well:

DECLARE @xml XML=
'<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>';

--The query will use .nodes() with a path to /*.
--This will return all elements below <Fields>, however they are named
--The query will return the element's name (local-name(.)) together with the two nested elements for old and new value:

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @xml.nodes('/LogMessage/Fields/*') A(fld);

The result

FieldName           OldValue    NewValue
-----------------------------------------
TransactionCount    NULL        NULL
PersonnelType       Contractor  Employee 
Disabled            TRUE        FALSE 
Expiration          10/31/2018  12/31/2019 

UPDATE

The same against a table's column:

DECLARE @mockup TABLE(ID INT IDENTITY,YourXml XML)
INSERT INTO @mockup VALUES
('<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>');

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @mockup m
OUTER APPLY m.YourXml.nodes('/LogMessage/Fields/*') A(fld)