SSDN - Inserting and selecting data into DB by XQuery

Options

Chat

Help

News

Not Logged in

Conferences

	Stylus Studio Feature Requests (1192)
	Stylus Studio Technical Forum (14621)
	Website Feedback (249)
	XSLT Help and Discussion (7625)
	XQuery Help and Discussion (2017)
	Issue with Processing Instruct... (2)
	problem converting json to XML... (2)
	Problem base64 decoding string... (3)
	Problems posting multipart for... (5)
	trouble with download of price... (2)
	Problem with http-post not bei... (3)
	path problem, xps_file:writeAl... (9)
	Xquery update support? (2)
	problem with Stylus studio try... (5)
	adding dtd reference to xml ou... (4)
	xquery escaping ambarsand when... (3)
	Whitespace problem when return... (5)
	Problem with namespace prefix ... (5)
	Sending via SFTP returns unexp... (1)
	Query and Sftp clent (4)
	xquery and try - catch (3)
	Query + ddtek:http-post optio... (5)
	Example files referenced in do... (3)
	Automatic Error Detection and ... (3)
	Working with result of ddtek:h... (2)
	[1-20] [21-40] [41-60] Next
	Stylus Studio FAQs (159)
	Stylus Studio Code Samples & Utilities (364)
	Stylus Studio Announcements (113)

Topic

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

top

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:

Site Map | Privacy Policy | Terms of Use | Trademarks