Input
| 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 ORDER BY 1,2;
Output
The parent node, price, has two child nodes, member and public. However, the Nodes argument 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 |