schema structures, xml, xsu

From: will <william.hulse_at_cliffordchance.com>
Date: 20 Sep 2002 07:56:03 -0700
Message-ID: <f7fb2ae2.0209200656.2e7658db_at_posting.google.com>

hello, hope you can help me with the following.

I have a xml document stored as a clob of the following format.

<ROWSET>
<ROW num="1">

   <CUSTOMERID>1044</CUSTOMERID>
   <FIRSTNAME>Paul</FIRSTNAME>
   <LASTNAME>Astoria</LASTNAME>
   <DETAIL_SET>
    <DETAIL_RECORD>


       <STREET>123 Cherry Lane</STREET>

       <CITY>SF</CITY>

       <STATE>CA</STATE>


       <ZIP>94132</ZIP>

    </DETAIL_RECORD>
    <DETAIL_RECORD>

<STREET>123 Cherry Lane</STREET>
<CITY>SF</CITY>
<STATE>CA</STATE>
<ZIP>94132</ZIP>

    </DETAIL_RECORD>
    <DETAIL_RECORD>

<STREET>123 Cherry Lane</STREET>
<CITY>SF</CITY>
<STATE>CA</STATE>
<ZIP>94132</ZIP>

    </DETAIL_RECORD>
  </DETAIL_SET>
 </ROW>
</ROWSET>

the data to a certain extent can be thought of a having a master - detail relationship with 1 or more detail_record in the detail_set, in the above case 1 master 3 detail.

The aim here is to use xsu to insert the above data into an underlying data structure. A target view with an instead-of trigger has been created which sits on top of a master and a detail table, the detail table being comprised of a detail type.

Insert commands of the type:  

INSERT INTO head_detail_VW VALUES ('another source system','another source instance','tomorrow','258965896','barclays','london','10','123.365',DETAIL_TYPE('546','64748','4 grosvenor sq','london','','','','','today','eur','123.65','5','cheque','3','london','2589','me','my house','','','','','','ugly','2','3','hello','dont know','hooray','oh'));

        INSERT INTO head_detail_VW VALUES ('source system','source

instance','tomorrow','258965896','barclays','london','10','123.365',DETAIL_TYPE('586','69948','4
grosvenor sq','london','','','','','today','eur','123.65','5','cheque','3','london','2589','me','my
house','','','','','','ugly','2','3','hello','dont

know','hooray','oh'));

        INSERT INTO head_detail_VW VALUES ('source system','source

instance','tomorrow','258965896','barclays','london','10','123.365',DETAIL_TYPE('586','68748','4
grosvenor sq','london','','','','','today','eur','123.65','5','cheque','3','london','2589','me','my
house','','','','','','ugly','2','3','hello','dont

know','hooray','oh'));

work well distributing the data to the underlying tables. Although using DBMS_XMLSave.insertXML(myTargetView, xmldoc); does not seem to work, not passing any info to the detail type.

What would the format of the generated insert statement be for the above xml structure, are they header/ detail_type etc

hope this kinda makes sense
any help with this would be greatly apprecited- cheers Will Received on Fri Sep 20 2002 - 16:56:03 CEST