This example uses the Nodes and Ancestors syntax elements to show the hierarchy of grandparent, parent, and child nodes.
Input
xid | xml |
---|---|
1 | <School name="UCBerkeley"> : <Dept ID="CS" name="Computer Science"> : <Class A="sophomore" B="Senior"> : <Year> : <Student>Harry</Student> : <Grade>A+</Grade> : </Year> : </Class> : </Dept> : </School> |
SQL Call
SELECT * FROM XMLParser ( ON xml_input2 USING TextColumn ('xml') Nodes ('Class:{A,B}/Year/Student','Year/Grade') Ancestor ('School/Dept') Accumulate ('xid') ) AS dt;
Output
xid out_nodeid out_grandparent_node out_parent_node class_a class_b student grade --- ---------- -------------------- --------------- --------- ------- ------- ----- 1 1 Class Year sophomore Senior Harry A+
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.