Input
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 |