1.1 - 8.10 - XMLRelation Example: OutputType ('fulldata') - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
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 ;

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 from the attachment in the left sidebar.