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
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 (2016)
-> + left/right presentation (3)
-> + new to stylus - connecting to ... (2)
-> + XQuery problem with namespaces (4)
-> + .conv and xquery problem (3)
-> + Can pass text into CONV file (2)
-> + doc-available not working as e... (4)
-> + ddtekjava write to file only e... (5)
-> + arrayindexoutofbounds with ddt... (7)
-> + XSD: Structure definition depe... (2)
-> + Fatal Error whitespace expecte... (3)
-> + Executing a pipeline sequence ... (2)
-> - Data Transfer (1)
-> + Specifying the SEF File Locati... (4)
-> + Multiply numbers... (2)
-> + Using Generated Java-Code... (5)
-> + Mapper does not show all nodes... (5)
-> - Xul tree search using XQuery X... (1)
-> + converting XML to CSV (2)
-> - datadirect licenses (1)
-> + generate a uuid (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.