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 ORDER BY 1, 2;
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 | 1 | School | name=UCLA | 1 | 1 | ||||||||||||
| 1 | 2 | School | name=UCLA | 1 | Dept | name=EE | 1 | 2 | |||||||||
| 1 | 3 | School | name=UCLA | 1 | Dept | name=EE | 1 | 2 | Class | A=grad,B=undergrad | 3 | ||||||
| 1 | 4 | School | name=UCLA | 1 | Dept | name=EE | 1 | 2 | Class | A=grad,B=undergrad | 3 | Student | Harry | 4 | |||
| 1 | 5 | School | name=UCLA | 1 | Dept | name=EE | 1 | 2 | Class | A=grad,B=undergrad | 3 | Grade | A+ | 5 |