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
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Robert HlinkaSubject: XML/SQL update more tables
Author: Robert Hlinka
Date: 15 Sep 2006 07:36 AM
Hello,

I have a question if it is possible to do INSERT/UPDATE to more than one table at once using your SQL/XML functionality.

Here is situation description:
I have 2 tables in DB.

T_CUST (SID INTEGER PRIMARY KEY, NAME VARCHAR2(20))
and
T_ADDR (SID INTEGER PRIMARY KEY, STREET VARCHAR2(40), CUST_SID INTEGER
CONSTRAINT FK_T_CUSTADDR
FOREIGN KEY (CUST_SID)
REFERENCES T_CUST(SID)
)

And I have XML document like this:
<row>
<SID>3.0</SID>
<NAME>IBM</NAME>
<ADDRESS><STR_NM>Street 1</STR_NM></ADDRESS>
<ADDRESS><STR_NM>Street 2</STR_NM></ADDRESS>
</row>

Is is possible to INSERT/UPDATE both tables at once using UPDATE tab in DBtoXML ??
How should the statement look like?
If not, is there any other way how to INSERT data from XML document to DB tables using Stylus Studio?

Thank you for any advice.

Robert Hlinka

PS:
Reverse operation is possible by this statemnt
SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "SID",t.SID),
XMLELEMENT(name "NAME",t.NAME),
(
SELECT
XMLELEMENT(name "ADDRESS",
XMLELEMENT(name "Street",a.STNM)
)
FROM T_ADDR a
WHERE a.CUST_SID=t.SID
)
)
FROM T_CUST t

Posttop
Ivan PedruzziSubject: XML/SQL update more tables
Author: Ivan Pedruzzi
Date: 22 Sep 2006 01:33 PM
Hi Robert,

Sorry if took us so long to answer.

The problem you are posing doesn't have an easy solution.

Be able to update multiple tables from a single update statement when identity columns are involved is very difficult because the transaction isolation prevents to see the changes until they are committed.

If you can't see the changes you can't update the relationships, so for instance you are not able to set the order.customerID to the customer.ID indentity you are creating.

You may try to configure a trigger on update or store the aggregated data in a temporary table then use a store procedure to normalize it back.

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

 
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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.