1.0 - 8.00 - XMLParser Example 3: Expressions in Nodes and Ancestor - 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

xml_inputs_fuzzy
xid xmldocument
1 <bookstore>

: <owner>Billy</owner><items>

: <bookitem category="ASTRONOMY">

: <title lang="en">Cosmos</title>

: <author>Carl Sagan</author>

: <author>Ann Druyan</author>

: <year edition="1">1980</year>

: <price>

: <member>49.99</member>

: <public>60.00</public>

: </price>

: </bookitem>

: </items>

: </bookstore>

2 <cdstore>

: <owner> Amy </owner>

: <items>

: <cditem category="pop">

: <title lang="en">Breathe</title>

: <author>Yu Quan</author>

: <year>2003</year>

: <price>

: <member>29</member>

: <public>35</public>

: </price>

: <position value="1" locate="east"/>

: </cditem>

: </items>

: </cdstore>

SQL Call

The Ancestor argument specifies that any node whose value ends with 'store' is an ancestor. The Nodes argument specifies that the function is to output the owner of each store and the title, author, and year of each node that starts with a string of lowercase alphabetic characters and ends with 'item'.

SELECT * FROM XMLParser (
  ON xml_inputs_fuzzy
  USING
  TextColumn ('xmlDocument')
  Nodes ('like(%store)/owner', 'regex([a-z]+item)/{title,author,year}')
  Ancestor ('like(%store)')
  Accumulate ('xid')
) AS dt ORDER BY 1,2;

Output

For bookstore and cdstore, the output table has the value of owner; for bookitem and cditem, the output table has the values of title, author, and year. Multiple values are separated by the default delimiter, comma (,).

xid out_nodeid out_parent_node owner title author year
1 1 bookstore Billy      
1 2 bookitem   Cosmos Carl Sagan,Ann Druyan 1980
2 1 cdstore Amy      
2 2 cditem   Breathe Yu Quan 2003