Reputation: 6881
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
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
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