I hope this isn't a duplicate question. I searched high and low on this site for my issue and I didn't find anything, so here's my question...

I'm using XQuery to parse out an XML document into a SQL server result set. Due to some repeating node name/value pairs, I'm getting duplicates. Why am I getting these duplicates?

Any help or assistance is greatly appreciated.

XML:

<root>
  <record>
    <recordid>1</recordid>
    <tests>
      <test name="Food" value="Apple" />
      <test name="Drink" value="Water" />
    </tests>
  </record>
  <record>
    <recordid>2</recordid>
    <tests>
      <test name="Food" value="Banana" />
      <test name="Drink" value="Orange Juice" />
    </tests>
    <tests>
      <test name="Food" value="Steak" />
      <test name="Drink" value="Beer" />
    </tests>
  </record>
</root>

SQL:

DECLARE
    @XML XML = '<root><record><recordid>1</recordid><tests><test name="Food" value="Apple" /><test name="Drink" value="Water" /></tests></record><record><recordid>2</recordid><tests><test name="Food" value="Banana" /><test name="Drink" value="Orange Juice" /></tests><tests><test name="Food" value="Steak" /><test name="Drink" value="Beer" /></tests></record></root>'

DECLARE @XMLTable AS TABLE (
    MyXML XML
)   
INSERT INTO @XMLTable(MyXML)
VALUES(@XML)

SELECT
    rec.c.value('recordid[1]', 'INT') AS RecordId,
    t.c.value('test[@name="Food"][1]/@value[1]', 'VARCHAR(15)') AS Food,
    t.c.value('test[@name="Drink"][1]/@value[1]', 'VARCHAR(15)') AS Drink
FROM @XMLTable AS x
CROSS APPLY MyXML.nodes('/root/record') rec(c)
OUTER APPLY MyXML.nodes('/root/record/tests') t(c)

Results:

RecordId    Food            Drink
----------- --------------- ---------------
1           Apple           Water
1           Banana          Orange Juice
1           Steak           Beer
2           Apple           Water
2           Banana          Orange Juice
2           Steak           Beer

Expected Results:

RecordId    Food            Drink
----------- --------------- ---------------
1           Apple           Water
2           Banana          Orange Juice
2           Steak           Beer

3 Answers

2
Shnugo On Best Solutions

The best way to solve this, was actually this:

SELECT
    rec.value('recordid[1]', 'INT') AS RecordId,
    t.value('test[@name="Food"][1]/@value[1]', 'VARCHAR(15)') AS Food,
    t.value('test[@name="Drink"][1]/@value[1]', 'VARCHAR(15)') AS Drink
FROM @XMLTable AS x
CROSS APPLY MyXML.nodes('/root/record') A(rec)
OUTER APPLY rec.nodes('tests') b(t);

The difference to your own solution is: The second APPLY uses the output of the first APPLY to dive deeper into the returned XML fragment.

You can read it as:

  • Dive into the XML and return each <record> in a single row.
  • Now (think it row wise) take the returned <record> and open the <tests>. This is a relativ path, hence no slashes.
  • the OUTER will allow for empty <tests>

And a general hint: Backward navigation (A Xpath with ../) is a known performance killer. There is a great answer by Mikael Eriksson explaining the background

3
Kirill Polishchuk On

Something like this will do:

SELECT
    rec.c.value('../recordid[1]', 'INT') AS RecordId,
    rec.c.value('test[@name="Food"][1]/@value[1]', 'VARCHAR(15)') AS Food,
    rec.c.value('test[@name="Drink"][1]/@value[1]', 'VARCHAR(15)') AS Drink
FROM @XMLTable 
CROSS APPLY MyXML.nodes('/root/record/tests') rec(c)
0
iamtheratio On

I was able to achieve the expected results by adding a WHERE clause to my original script.

SQL:

SELECT
    rec.c.value('recordid[1]', 'INT') AS RecordId,
    t.c.value('test[@name="Food"][1]/@value[1]', 'VARCHAR(15)') AS Food,
    t.c.value('test[@name="Drink"][1]/@value[1]', 'VARCHAR(15)') AS Drink
FROM @XMLTable AS x
CROSS APPLY MyXML.nodes('/root/record') rec(c)
CROSS APPLY MyXML.nodes('/root/record/tests') t(c)
WHERE rec.c.value('recordid[1]', 'INT') = t.c.value('../recordid[1]', 'INT')

Results:

RecordId    Food            Drink
----------- --------------- ---------------
1           Apple           Water
2           Banana          Orange Juice
2           Steak           Beer