XML xquery retrieve the descendent nodes value from a xml string

Question

I'm reading a XML document using XQuery in SQL Server. This is my XML:

  declare @xml as xml='
  <contactdata>
    <contacts>
      <groupkey>24</groupkey>
      <groupname>Test Group One</groupname>
      <contact>
        <contactkey>100</contactkey>
        <contactname>Test Contact One</contactname>
      </contact>
      <contact>
        <contactkey>111</contactkey>
        <contactname>Test Contact Two</contactname>
      </contact>
    </contacts>
    <contacts>
      <groupkey>26</groupkey>
      <groupname>Test Group Two</groupname>
       <contact>
        <contactkey>101</contactkey>
        <contactname>Test Contact Six</contactname>
      </contact>
    </contacts>
  </contactdata>';

In order to retrieve the each contact details under every group, I'm using this XQuery syntax

select 
    c.value('(groupkey)[1]','int'),
    c.value('(groupname)[1]','nvarchar(max)'),
    c.value('(contact/contactkey)[1]','int'),
    c.value('(contact/contactname)[1]','nvarchar(max)')
from
@xml.nodes('contactdata/contacts') as Contacts(c)

But this will return only the first contact under each group.

Output2

But I need this output

Output

Please help.

marc_s marc_s · Accepted Answer · 2014-01-02T11:08:16

Just change your query to this:

select 
    c.value('(../groupkey)[1]','int'),
    c.value('(../groupname)[1]','nvarchar(max)'),
    c.value('(contactkey)[1]','int'),
    c.value('(contactname)[1]','nvarchar(max)')
from
    @xml.nodes('contactdata/contacts/contact') as Contacts(c)

You need to get a list of all <contact> elements - and then extract that necessary data from those XML fragments. To get the groupkey and groupname, you can use the ../ to navigate "up the tree" one level to the parent node.

XML xquery retrieve the descendent nodes value from a xml string

1 Answers