1.0 - 8.00 - XMLRelation Example 1: OutputType ('fulldata') - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

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.

xmlrelation_input
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