CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxquery
Jim
Jim

Reputation: 6881

Trouble querying xml with separate columns per child node

Given an XML column with data like shown below, I'd like to write a query that has one record for each ChangedColumn element in the XML, and one column for each child Name / OldValue / NewValue element.

DECLARE @t XML = '
<ChangeEvent>
   <ChangedColumn>
      <Name>MyColumn</Name>
      <OldValue>SomeOldValue</OldValue>
      <NewValue>SomeNewValue</NewValue>
   </ChangedColumn>
   <ChangedColumn>
      <Name>SomeOtherColumn</Name>
      <OldValue>1</OldValue>
      <NewValue>2</NewValue>
   </ChangedColumn>
</ChangeEvent>
'

So the results would look something like this...

Name             OldValue       NewValue 
MyColumn         SomeOldValue   SomeNewValue 
SomeOtherColumn  1              2

The query below will give me all the data mashed together in one column, but I want the values of each child node of the ChangedColumn element to be in separate columns, preferably with the column name matching the element name.

SELECT node.value('.', 'VARCHAR(MAX)')
FROM @t.nodes('/ChangeEvent/ChangedColumn') t(node)

Any ideas how I can fix this query to give the results I need?

Upvotes: 0

Views: 83

Answers (2)

Jim
Jim

Reputation: 6881

GarethD's answer is probably better, but while I was waiting I did figure out another way to go about it, like shown below...

SELECT 
  node.query('./Name') as Name, 
  node.query('./OldValue') as OldValue, 
  node.query('./NewValue') as NewValue
FROM @t.nodes('/ChangeEvent/ChangedColumn') t(node)

This includes the XML tags in with the values, so I definitely prefer GarethD's answer. However, I posted this just in case this helps anyone else if they need to see more than one way to solve the problem.

I don't know if performance is worse this way or not, but I'd assume it would probably be worse.

Upvotes: 0

GarethD
GarethD

Reputation: 69829

You could specify the node name in your .value method:

SELECT  Name = node.value('Name[1]', 'VARCHAR(MAX)'),
        OldValue = node.value('OldValue[1]', 'VARCHAR(MAX)'),
        NewValue = node.value('NewValue[1]', 'VARCHAR(MAX)')
FROM    @t.nodes('/ChangeEvent/ChangedColumn') t(node);

Upvotes: 1

Related Questions