XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
search
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Home » Boards » Stylus Studio Developer Network » XQuery Help and Discussion » Inserting and selecting data into DB by XQuery
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
+ Stylus Studio Technical Forum (14621)
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
- XQuery Help and Discussion (2017)
-> + Help to write Xquery (2)
-> + XQuery java ddtek:serialize-to... (2)
-> + EDI to XML Automation (2)
-> + Generating XQUERY to use in Or... (2)
-> + Stylus is not easy to learn XQ... (2)
-> - java.lang.OutOfMemoryError (1)
-> + how to save to xml format (2)
-> + Formating a date in xquery (2)
-> + two xml statements when mappin... (5)
-> + XQuery (2)
-> + saxon-license.lic Not Found (2)
-> + hands off conversion to final ... (2)
-> + fn:substring in XQuery to Orac... (4)
-> + Using Saxon with XQuery to ins... (4)
-> + Can't SAVE or DO a SAVE AS Fro... (2)
-> + Saxon Unexpected token < e o f... (2)
-> - Inserting and selecting data i... (2)
-> ->Inserting and selecting d...
-> - convert sql statement into xml... (1)
-> + need help in converting this a... (2)
-> + IS IT POSSIBLE TO DO AN XQUERY... (2)
-- Previous [61-80] [81-100] [101-120] Next
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
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

   
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.