XQuery vs OpenXML in SQL Server

Question

I have this XML in a SQL Server table:

<root>
  <meetings>
    <meeting>
      <id>111</id>
      <participants>
        <participant><name>Smith</name></participant>
        <participant><name>Jones</name></participant>
        <participant><name>Brown</name></participant>
      </participants>
    </meeting>
    <meeting>
      <id>222</id>
      <participants>
        <participant><name>White</name></participant>
        <participant><name>Bloggs</name></participant>
        <participant><name>McDonald</name></participant>
      </participants>
    </meeting>
  </meetings>
</root>

And want a result set like this:

MeetingID    Name
111          Smith
111          Jones
111          Brown
222          White
222          Bloggs
222          McDonald

This is easy using select from openxml but I failed using XQuery. Can someone help me there, and maybe also give pros and cons for either method?

marc_s · Accepted Answer · 2010-08-04 13:38:40Z

5

Once you've fixed your invalid XML (the <name> elements need to be ended with a </name> end tag), you should be able to use this:

SELECT 
    Meetings.List.value('(id)[1]', 'int') AS 'Meeting ID',
    Meeting.Participant.value('(name)[1]', 'varchar(50)') AS 'Name'
FROM
    @input.nodes('/root/meetings/meeting') AS Meetings(List)
CROSS APPLY
    Meetings.List.nodes('participants/participant') AS Meeting(Participant)

Basically, the first call to .nodes() gives you a pseudo-table of all <meeting> nodes, from which I extract the meeting ID.

The second .nodes() call on that <meeting> tag digs deeper into the <participants>/<participant> list of subnodes and extracts the name from those nodes.

answered Aug 4, 2010 at 13:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

2 Comments

Graeme Over a year ago

Thanks, exactly what I needed. I do find openXML syntax easier to understand though.

marc_s Over a year ago

@Graeme: really? I find OpenXML very unintuitive and quite messy, actually.

kadalamittai · Accepted Answer · 2010-08-09 15:08:31Z

1

This may give you the XQuery based Table based output.

(: Assume $x is your Xml Content. so :)
let $x := Assign your Xml Content.
let $d1:= <table border="1"><tr><td>Meeting</td><td> Participant</td></tr>
  { for $p in $x//meeting/participants/participant 
              return element{'tr'} {
                    element{'td'} {$p/parent::*/parent::*/id/text()},
                    element{'td'} {data($p)}                        
                }
          }

   </table>

answered Aug 9, 2010 at 15:08

kadalamittai

2,1761 gold badge16 silver badges20 bronze badges

Collectives™ on Stack Overflow

XQuery vs OpenXML in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related