SQL XPath Query

Question

Given an XML data structure of:

<root>
<a attr="abcd">
 <b> test1</b>
 <b> test2 </b>
 <b> test3 </b>
</a>
<a attr="zyx">
 <b> test1 </b>
 <b> test2 </b>
 <b> test3 </b>
</a>
<a attr="gjui">
 <b>test1 </b>
</a>
</root>

I need to normalize this data out:

  abcd   test1
  abcd   test2
  abcd   test3
  zyx    test1
  zyx    test2
  zyx    test1
  gjui   test1

The issue I am encountering is where there is more than one child for the root.

SELECT xtab.my_attr, xtab.my_attr_values
FROM my_table jx, xmltable('/root/a' 
PASSING jx.field_xml
COLUMNS my_attr path '@attr', 
my_attr_values path '/a/b')xtab;

Returns

abcd   NULL
zyx    NULL
gjui   test1

This isn't even valid XML. Please update with a correct sample. — OldProgrammer, May 14 '14 at 01:07
See http://stackoverflow.com/questions/13947416/how-to-use-xpath-with-a-variable-in-oracle-xmltable — helderdarocha, May 14 '14 at 02:47

score 0 · Accepted Answer · answered May 14 '14 at 04:36

Traversing the children and pulling in the parent node for the child resolved this.

SELECT xtab.my_attr, xtab.my_attr_values
FROM my_table jx, xmltable('for $i in /root/a/b
return element r {
$i/../@attr,
$i/.
}' 
PASSING jx.field_xml
COLUMNS my_attr path '@attr', 
my_attr_values path 'b')xtab;

SQL XPath Query

1 Answers1