XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Simon TusonSubject: 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


Posttop
Ivan PedruzziSubject: 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

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! 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

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
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.