Extract elements from XML in SQL

Question

I'm trying to figure out how to pull nested XML elements and turn it into a table result in SQL. A WorkItem always has a group, but Group fields can be nested N times. Is there an easy way to pull JUST the Fields out of an XML field that looks like this:

declare @xml XML = '
<WorkItem>
  <Group Name="Base" >
    <Field FieldId="361" Name="Assigned To"  />
    <Field FieldId="362" Name="Stuff"  />
    <Group Name="Detail">
      <Field FieldId="363" Name="Assigned To 2" />
    </Group>
  </Group>
</WorkItem>'

declare @handle int
declare @status int

exec @status = sp_xml_preparedocument @handle output, @xml
select *
from openxml(@handle, 'WorkItem/Group/Field')
with (
    FieldId int,
    Name varchar(max)
)
exec sp_xml_removedocument @handle

What I'm getting:

361,Assigned To
362,Stuff

What I'm expecting:

361,Assigned To
362,Stuff
363,Assigned To 2

Thanks!

I copied and pasted your sample to rextester (+1 for a ready-to-use sample data), but I can't reproduce the problem. — Zohar Peled
– Zohar Peled, Commented Aug 16, 2017 at 15:40
Btw: FROM OPENXML with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more (rare exceptions exist). Rather use the appropriate methods the XML data type provides. — Gottfried Lesigang
– Gottfried Lesigang, Commented Aug 17, 2017 at 9:00

Ruslan K. · Accepted Answer · 2017-08-17 07:09:58Z

5

I think it's better to use XQuery:

DECLARE @xml XML = '
<WorkItem>
  <Group Name="Base" >
    <Field FieldId="361" Name="Assigned To"  />
    <Field FieldId="362" Name="Stuff"  />
    <Group Name="Detail">
      <Field FieldId="363" Name="Assigned To 2" />
    </Group>
  </Group>
</WorkItem>';


SELECT 
    n.value('@FieldId', 'int') FieldId, 
    n.value('@Name', 'varchar(250)') Name
FROM 
    @xml.nodes('/WorkItem//Field') xml(n);

Output:

FieldId     Name
----------- --------------------
361         Assigned To
362         Stuff
363         Assigned To 2

edited Aug 17, 2017 at 7:09

answered Aug 16, 2017 at 15:51

Ruslan K.

2,0011 gold badge16 silver badges20 bronze badges

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

4 Comments

Dan Champagne Over a year ago

This works, however I cut and pasted wrong in my XML. The Group elements should have been nested.

Ruslan K. Over a year ago

@DanChampagne, ok. I have edited my answer. Check it.

Dan Champagne Over a year ago

Brilliant! Thank you so much! It worked perfectly :)

Ruslan K. Over a year ago

@DanChampagne, glad to help you :)

Collectives™ on Stack Overflow

Extract elements from XML in SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related