SSDN - Inserting and selecting data into DB by XQuery

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Simon Tuson

Subject: Inserting and selecting data into DB by XQuery
Author: Simon Tuson
Date: 22 Oct 2010 12:16 AM

Hi All,

I'm not an XQuery expert so please forgive any bad terminology here.

Essentially what I an trying to do is load in an XML hierarchical index into a database. The loading is simple but I am struggling to insert the correct parent id's from the hierarchy.

Every item in the index has an id in the database (primary key), items also must have the parent id of it direct ancestor. The is best illustrated in the following where we have 3 levels of depth.

XML
<root id="solution" name="solution">
<item d="1" id="I00" pid="solution" name="Infrastructure">
<item d="2" id="BR08" pid="I00" name="Business"/>
<item d="2" id="LI06" pid="I00" name="Information"/>
<item d="2" id="LI04" pid="I00" name="Warehouse"/>
</item>
<item d="1" id="Q00" pid="solution" name="Cross Industry">
<item d="2" id="Q09" pid="Q00" name="Advertising"/>
<item d="2" id="S6" pid="Q00" name="Application"/>
<item d"3" id="AC1" pid="S6" name="configure"/>
<item d"3" id="AC2" pid="S6" name="help"/>
</item>
</item>
</root>

DATABASE COLS

PR KEY | ID | NAME | PID
1 | I00 | Infrstr.....| NULL
2 | BR08 | Busines.... | 1
3 | LI06 | Infor.... | 1
4 | LI04 | Ware.... | 1
5 | Q00 | Cross... | NULL
6 | Q09 | Advetis.... | 5
7 | S6 | Applic..... | 5
8 | AC1 | configu.....| 7
9 | AC2 | help | 7

The primary key is auto calculated.

The PID needs to populated from the value of the PRIMARY KEY of its parent item.

XQUERY

The following works but the I have not idea how to get and insert the primary key

for $MYITEM in /root//item
return
( ddtek:sql-insert("jdbc_mysql_192_168_248_168_3306:tm_m_taxdtls","NAME",$MYITEM/@name,"ID",$MYITEM/@id))

Any help would be greatly appreciated.

Thanks

Ivan Pedruzzi

Subject: Inserting and selecting data into DB by XQuery
Author: Ivan Pedruzzi
Date: 23 Oct 2010 01:21 AM

Simon,

Unfortunately an updating query cannot see its side effects because of the XQuery snapshot semantic.

You could try doing two queries, the first query does the insert and leave the PID empty and the second query updates the PID.

for $MYITEM in /root//item
,$record in collection("tm_m_taxdtls")//tm_m_taxdtls[key = $MYITEM/@id]
,$parent in collection("tm_m_taxdtls")/tm_m_taxdtls[key = $MYITEM/@pid]
where exists($parent)
return
ddtek:sql-update($record, "pid", $parent/id)

Ivan

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.