1.1 - 8.10 - XMLParser Example: Sibling and Sibling_Delimiter - 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

xml_input1
xid xmldocument
1 <bookstore>

: <owner>Billy</owner>

: <book category="ASTRONOMY">

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

: <author>Carl Sagan</author>

: <author>Ann Druyan</author>

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

: <year edition="2">1981</year>

: <price>

: <member>49.99</member>

: <public>60.00</public>

: </price>

: <reference>

: <title>Comet</title>

: </reference>

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

: </book>

: <book category="CHILDREN">

: <author>Judy Blume</author>

:

: <price>

: <member>99.99</member>

: <public>108.00</public>

: </price>

: </book>

: </bookstore>

2 <setTopRpt xsi:noNamespaceSchemaLocation="Set%20Top%2020Report%20.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance">

: <settopid type="string" length="5">ST789</settopid>

: <accountid type="string">8728</accountid>

: <zipcode type="string">94025</zipcode>

: <reportstamp type="dateTime">2009-10-03T12:52:06</reportstamp>

: <temperature>

: <read type="bigDecimal">46</read>

: </temperature>

: <storage>

: <used type="bigDecimal">98</used>

: <used type="bigDecimal">199</used>

: <used type="bigDecimal">247</used>

: <total type="bigDecimal">300</total>

: </storage>

: <feed>

: <feedstamp type="dateTime">2009-10-03T12:52:06</feedstamp>

: </feed>

: </setTopRpt>

SQL Call

SELECT * FROM XMLParser(
  ON xml_input1
  USING
  TextColumn('xmldocument')
  Nodes('price/member')
  Sibling('author','year','title')
  SiblingDelimiter(';')
  Accumulate('xid')
) AS dt ;

Output

The parent node, price, has two child nodes, member and public. However, the Nodes syntax element specifies only member; therefore, only its value is output. Title, author, and year are siblings of price. The first document has multiple author and year siblings, so the values of those siblings are separated by the specified delimiter, semicolon (;).

 xid out_nodeid out_parent_node author                year      title  member 
 --- ---------- --------------- --------------------- --------- ------ ------ 
   1          1 price           Carl Sagan;Ann Druyan 1980;1981 Cosmos 49.99 
   1          2 price           Judy Blume                             99.99

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.