SQL Server Xpath to get text value for specific xml element

Question

I have a table updates, each row has an id column and a XML column called changes. The XML column for each row will have values like:

<changes>
  <update>
    <field>assignedto</field>
    <oldvalue>c713826</oldvalue>
    <newvalue>Trainer</newvalue>
  </update>
  <update>
     <field>status</field>
     <oldvalue>inprogress</oldvalue>
     <newvalue>query</newvalue>
  </update>
  <update>
     <field>note</field>
     <oldvalue />
     <newvalue>January 2016 or 2017?</newvalue>
  </update>
</changes>

I want to find the value of newvalue and oldvalue where field = "AssignedTo" for each record.

The AssignedTo field will only ever appear once in the XML for each record.

So far I've used

where [changes].exist('/changes/update/field/text() [contains(.,"assignedto")]')

To return only records with the AssignedTo field but I'm having difficulty selecting the matching old and new values.

Andrey Korneyev · Accepted Answer · 2016-05-26 15:54:39Z

3

You can select the data from your xml like this:

declare @data xml
select @data = '<changes>
  <update>
    <field>assignedto</field>
    <oldvalue>c713826</oldvalue>
    <newvalue>Trainer</newvalue>
  </update>
  <update>
     <field>status</field>
     <oldvalue>inprogress</oldvalue>
     <newvalue>query</newvalue>
  </update>
  <update>
     <field>note</field>
     <oldvalue />
     <newvalue>January 2016 or 2017?</newvalue>
  </update>
</changes>'

select 
    T.C.value('oldvalue[1]', 'nvarchar(max)') as oldvalue,
    T.C.value('newvalue[1]', 'nvarchar(max)') as newvalue
from @data.nodes('changes/update') as T(C)
where T.C.value('field[1]', 'nvarchar(max)') = 'assignedto'

In the case of selecting from table's column it will be something like

select *
from dbo.updates as U
     outer apply 
     (
        select 
            T.C.value('oldvalue[1]', 'nvarchar(max)') as oldvalue,
            T.C.value('newvalue[1]', 'nvarchar(max)') as newvalue
        from U.[changes].nodes('changes/update') as T(C)
        where T.C.value('field[1]', 'nvarchar(max)') = 'assignedto'
    ) as CALC
where 
     CALC.oldvalue is not null 
     or CALC.newvalue is not null

edited May 26, 2016 at 15:54

answered May 26, 2016 at 15:47

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jammy Over a year ago

Was just about to ask how to apply that to a table but you beat me to it. Thanks

Collectives™ on Stack Overflow

SQL Server Xpath to get text value for specific xml element

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related