|
|
 top
|
  Subject: hierarchical XML into tables
Author: Minollo I.
Date: 11 Jul 2007 11:52 PM
|
Welcome!
Do you need to store the content of the XML document in RDBMS tables? If that's the case, DataDirect XQuery (available as a deployment component from http://www.xquery.com, but also bundled in Stylus Studio for development purposes) supports the ability to update your database from an XQuery. One of the examples shows how to shred a simple XML document into two tables; given this XML:
<?xml version="1.0"?>
<new-users>
<user>
<id>Marc</id>
<first-name>Marc</first-name>
<last-name>Peppino</last-name>
<date>1998-01-10</date>
<holdings>
<holding>
<ticker>PRGS</ticker>
<quantity>500</quantity>
</holding>
</holdings>
</user>
<user>
<id>Barbara</id>
<first-name>Barbara</first-name>
<last-name>Smith</last-name>
<date>2005-01-01</date>
<holdings>
<holding>
<ticker>GOOG</ticker>
<quantity>1500</quantity>
</holding>
<holding>
<ticker>PRGS</ticker>
<quantity>2342</quantity>
</holding>
</holdings>
</user>
</new-users>
...this XQuery will "shred" its content in two tables, "users" and "holdings":
for $newUser in doc("new-users.xml")/new-users/user
return (
ddtek:sql-insert("users",
"userid", $newUser/id, "firstname", $newUser/first-name,
"lastname", $newUser/last-name, "membersince", $newUser/date),
for $holding in $newUser/holdings/holding
return
ddtek:sql-insert("holdings", "userid", $newUser/id,
"stockticker", $holding/ticker, "shares", $holding/quantity)
|
|
|
|
next
sample(9).xml