Input
The input table, xmlrelation_input, contains an xml document that has these hierarchical nodes: School at level 1, Dept at level 2, Class at level 3, and Student and Grade at level 4.
| xid | xmldocument |
|---|---|
| 1 | <School name="UCLA"> : <Dept name="EE"> : <Class A="grad" B="undergrad"> : <Student>Harry</Student> : <Grade>A+</Grade> : </Class> : </Dept> : </School> |
SQL Call
SELECT * FROM XMLRelation(
ON xmlrelation_input
USING
TextColumn('xmldocument')
DocIDColumns('xid')
MaxDepth(3)
OutputType('fulldata')
) AS dt ;
Output
The output table shows the elements, attributes, values, and ids for each node.
xid out_nodeid d0element d0attributes d0value_col d0id d1element d1attributes d1value_col d1id d2element d2attributes d2value_col d2id d3element d3attributes d3value_col d3id --- ---------- --------- ------------ ----------- ---- --------- ------------ ----------- ---- --------- ------------------ ----------- ---- --------- ------------ ----------- ---- 1 4 School name=UCLA 1 Dept name=EE 2 Class A=grad,B=undergrad 3 Student Harry 4 1 5 School name=UCLA 1 Dept name=EE 2 Class A=grad,B=undergrad 3 Grade A+ 5 1 3 School name=UCLA 1 Dept name=EE 2 Class A=grad,B=undergrad 3 NULL NULL NULL NULL 1 2 School name=UCLA 1 Dept name=EE 2 NULL NULL NULL NULL NULL NULL NULL NULL 1 1 School name=UCLA 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
Download a zip file of all examples and a SQL script file that creates their input tables.