CODEWITHSUNDEEP
sqlsql-serverxml
Dan Champagne
Dan Champagne

Reputation: 920

Extract elements from XML in SQL

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!

Upvotes: 2

Views: 200

Answers (1)

Ruslan K.
Ruslan K.

Reputation: 1981

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

Upvotes: 5

Related Questions