XMLParser Example: Expressions in Nodes and Ancestor | Teradata Vantage - XMLParser Example: Expressions in Nodes and Ancestor - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
9.02
9.01
2.0
1.3
Published
February 2022
Language
English (United States)
Last Update
2022-02-10
dita:mapPath
rnn1580259159235.ditamap
dita:ditavalPath
ybt1582220416951.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

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 syntax element specifies that any node whose value ends with 'store' is an ancestor. The Nodes syntax element 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 ;

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

Download a zip file of all examples and a SQL script file that creates their input tables.