|
|
 next
|
  Subject: 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
|
|
|
|
top