Hi Tom,

Actually this question is intended for Sean Dillon (XML guru)
for this very usefull script but trigger is trimmed from the right side so it should look like this.

CREATE OR REPLACE TRIGGER xmldocs_bifert
before insert on xmldocs
for each row
begin
if (:new.xmldoc is null) then
return;
end if;
--
select xmltype.getstringval(:new.xmldoc.extract('/Record/Type/text()')),
xmltype.getstringval(:new.xmldoc.extract('/Record/Source/text()')),
xmltype.getstringval(:new.xmldoc.extract('/Record/Instrument/text()'))
into :new.thetype, :new.source, :new.instrument
from dual;
--
for i in ( select xmltype.extract(value(t), '/F/N/text()' ).getstringval() as N,
xmltype.extract(value(t), '/F/V/text()' ).getstringval() as V
from table( xmlsequence( :new.xmldoc.extract('/Record/F'))) t
)
loop
insert into fvals values (i.N, i.V);
end loop;
end xmldocs_bifert;
/

Anyway, thanks.

Very helpful. Thank you.

First I would like to thank for such a nice site, learnt a lot from this GREAT site. Not sure if this is the correct thread, I am not getting a chance to ask in a new question.

Need your help on the following.

I will have to generate a RDF report based on the XML information stored in a CLOB column.
Following is the structure of the XML CLOB.
Could you please suggest how can I load the XML in a table,so that,
we could easily query the table rows to print the RDF report.
How do I load the XML in a table ? what are the possible table columns I will have to keep ?

I am very new to XML tech. Please help.

<SHOW_SHIPMENT_005>
<CNTROLAREA>
<BSR>
<VERB value="SHOW">SHOWN</VERB>
<NOUN value="SHIPMENT">SHIPMENT</NOUN>
<REVISION value="005">005</REVISION>
</BSR>
<SENDER>
<LOGICALID>GLEN-STAMFORD</LOGICALID>
<COMPONENT>ASN</COMPONENT>
<TASK>SHOW</TASK>
<REFERENCEID>2002-62949-1673*110664-1</REFERENCEID>
<CONFIRMATION/>

<LANGUAGE>ENG</LANGUAGE>
<CODEPAGE/>
<AUTHID>STAMFORD</AUTHID>
</SENDER>
<DATETIME qualifier="CREATION">
<YEAR>2004</YEAR>
<MONTH>02</MONTH>
<DAY>12</DAY>
<HOUR>10</HOUR>
<MINUTE>51</MINUTE>
<SECOND>01</SECOND>
<SUBSECOND>0000</SUBSECOND>
<TIMEZONE>+0000</TIMEZONE>

</DATETIME>
</CNTROLAREA>
<DATAAREA>
<SHOW_SHIPMENT>
<SHIPMENT>
<DATETIME qualifier="DOCUMENT">
<YEAR>2004</YEAR>
<MONTH>02</MONTH>
<DAY>12</DAY>
<HOUR>10</HOUR>
<MINUTE>51</MINUTE>
<SECOND>01</SECOND>
<SUBSECOND>0000</SUBSECOND>

<TIMEZONE>+0000</TIMEZONE>
</DATETIME>
<DATETIME qualifier="SHIP">
<YEAR>2004</YEAR>
<MONTH>01</MONTH>
<DAY>05</DAY>
<HOUR>12</HOUR>
<MINUTE>00</MINUTE>
<SECOND>00</SECOND>
<SUBSECOND>0000</SUBSECOND>
<TIMEZONE>+0000</TIMEZONE>
</DATETIME>
<DATETIME qualifier="DELIVSCHED">

<YEAR>2004</YEAR>
<MONTH>01</MONTH>
<DAY>05</DAY>
<HOUR>12</HOUR>
<MINUTE>00</MINUTE>
<SECOND>00</SECOND>
<SUBSECOND>0000</SUBSECOND>
<TIMEZONE>+0000</TIMEZONE>
</DATETIME>
<QUANTITY qualifier="NETWEIGHT">
<VALUE>45415</VALUE>
<NUMOFDEC/>
<SIGN/>

<UOM>LB</UOM>
</QUANTITY>
<QUANTITY qualifier="TOTWEIGHT">
<VALUE>45415</VALUE>
<NUMOFDEC/>
<SIGN/>
<UOM>LB</UOM>
</QUANTITY>
<DOCUMENTID>2002-62949-1673*110664-1</DOCUMENTID>
<SHIPPERID/>
<NOTES index="1">HOLLY</NOTES>
<USERAREA>
<CARRIERCODE/>

<QUANTITY qualifier="OTHER">
<VALUE>45415</VALUE>
<NUMOFDEC>4</NUMOFDEC>
<SIGN/>
<UOM>LB</UOM>
</QUANTITY>
<ATTRIBUTE9>105165*110664-1</ATTRIBUTE9>
</USERAREA>
<PARTNER>
<PARTNRID/>
<PARTNRTYPE>ShipFrom</PARTNRTYPE>
<NAME index="1">core LTD</NAME>
<PARTNRIDX>STAMFORD</PARTNRIDX>

<ADDRESS>
<CITY/>
<COUNTRY/>
<POSTALCODE/>
<STATEPROVN/>
</ADDRESS>
</PARTNER>
<PARTNER>
<PARTNRID/>
<PARTNRTYPE>ShipTo</PARTNRTYPE>
<NAME index="1"/>
<PARTNRIDX>CITY_10538</PARTNRIDX>
<ADDRESS>

<ADDRLINE index="1"/>
<ADDRLINE index="2"/>
<CITY/>
<COUNTRY/>
<POSTALCODE/>
<STATEPROVN/>
</ADDRESS>
</PARTNER>
<SHIPITEM>
<QUANTITY qualifier="ITEM">
<VALUE>45415</VALUE>
<NUMOFDEC/>
<SIGN>+ </SIGN>

<UOM>LB</UOM>
</QUANTITY>
<ITEM/>
<ITEMX/>
<NOTES index="1">MT HOLLY SC</NOTES>
<DOCUMNTREF>
<DOCTYPE>PurchaseOrder</DOCTYPE>
<DOCUMENTID>110008888</DOCUMENTID>
<PARTNRID/>
<PARTNRTYPE/>
<DESCRIPTN/>
<DOCUMENTRV/>
<LINENUM>2</LINENUM>

<SCHLINENUM>1</SCHLINENUM>
<USERAREA>
<DOCUMENTRELEASE/>
</USERAREA>
</DOCUMNTREF>
<DOCUMNTREF>
<DOCTYPE>PackingSlip</DOCTYPE>
<DOCUMENTID>COEE^1318</DOCUMENTID>
<PARTNRID/>
<PARTNRTYPE/>
<DESCRIPTN/>
<DOCUMENTRV/>
<LINENUM/>

<SCHLINENUM/>
<USERAREA/>
</DOCUMNTREF>
</SHIPITEM>
<DOCUMNTREF>
<DOCTYPE>BillOfLading</DOCTYPE>
<DOCUMENTID>105555</DOCUMENTID>
<PARTNRID/>
<PARTNRTYPE/>
<DESCRIPTN/>
<DOCUMENTRV/>
<LINENUM/>
<SCHLINENUM/>

<USERAREA/>
</DOCUMNTREF>
<DOCUMNTREF>
<DOCTYPE>PackingSlip</DOCTYPE>
<DOCUMENTID>COEE^1318</DOCUMENTID>
<PARTNRID/>
<PARTNRTYPE/>
<DESCRIPTN/>
<DOCUMENTRV/>
<LINENUM/>
<SCHLINENUM/>
<USERAREA/>
</DOCUMNTREF>

</SHIPMENT>
</SHOW_SHIPMENT>
</DATAAREA>
</SHOW_SHIPMENT_005>

Hi Tom,

He is referring to Oracle Reports.
When reports are saved it has the extension of .rdf.

Hi Tom,

This is a data model design question...

Say I have a star schema and we have report pages on different levels of group by on the data. Now we want to let the user to add notes to any level, or even the most details level - the fact record itself.

E.g. when the user is viewing a summary report group by regions, he can add a note for that page. If he is viewing one single order line detail, he can add a note to that order line as well.


I am thinking using XML maybe good idea for this, storing the "group by" path...

Do you think this a good idea? (Give that the notes will be sparse compared to the # of rows in fact).

Tom,

I tried to manipulate the code above to utilize...but the insert statement doesn't seem to like all of the special characters in my XML file below. I'm sending the XML packet to Oracle via ColdFusion.

Here's the error I get once the stored procedure is called.

[Macromedia][Oracle JDBC Driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column ORA-06512: at "SASSOH.LOADXML", line 3 ORA-06512: at line 1

Thanks in advance for you help.

Jim

-------------------------------------------------------


CREATE TABLE JOBS
(
ID NUMBER,
HOST VARCHAR2(100 BYTE),
SEARCH VARCHAR2(100 BYTE),
LINK VARCHAR2(4000 BYTE),
POSTDATE DATE,
TITLE VARCHAR2(4000 BYTE),
COMPANY VARCHAR2(4000 BYTE),
LOCATION VARCHAR2(100 BYTE),
D_UPD DATE,
STATE VARCHAR2(100 BYTE),
CITY VARCHAR2(100 BYTE),
TITLE_NOLINK VARCHAR2(100 BYTE),
COMPANY_NOLINK VARCHAR2(100 BYTE),
XMLDOC SYS.XMLTYPE
)

CREATE OR REPLACE PROCEDURE LoadXML (cfxml in varchar) AS
begin
insert into JOBS (xmldoc) values (xmltype.createxml(cfxml));
end;
/

CREATE OR REPLACE TRIGGER jobs_bifert
before insert on jobs
for each row
begin
if (:new.xmldoc is null) then
return;
end if;
select xmltype.getstringval(:new.xmldoc.extract('/row/date/text()')),
xmltype.getstringval(:new.xmldoc.extract('/row/title/text()')),
xmltype.getstringval(:new.xmldoc.extract('/row/company/text()')),
xmltype.getstringval(:new.xmldoc.extract('/row/location/text()'))
into :new.postdate, :new.title, :new.company, :new.location
from dual;

end loop;
/

<?xml version="1.0" encoding="iso-8859-1">
<recordset>
<row>
<date><![CDATA[Jul 25]]></date>
<title><![CDATA[<a href="</code> http://jobsearch.monster.com:80/getjob.asp?JobID=27427955&AVSDM=2005%2D07%2D25+08%3A11%3A26&Logo=1&q=php&cy=US&sort=dt" >SENIOR

HTML/CSS/JAVASCRIPT DEVELOPER</a>]]></title>
<company><![CDATA[RCG Information Technology]]></company>
<location><![CDATA[<a onClick="popMMLL('FL','Orlando'); return false;" href="

http://jobsearch.monster.com:80/385" >FL-Orlando</a>]]></location>

<myfile><![CDATA[<a href="

http://my.monster.com/JobFile/JobFile.aspx?action=ADD&JobID=27427955&jobTitle=SENIOR+HTML%2FCSS%2FJAVASCRIPT+DEVELOPER&fjs=1&q=php&cy=US&sort=dt" ></a>]]></myfile> <code>
</row>
</recordset>

Hi Tom , 
   Thanks for the wonderful service you provide. I am trying to parse the xml below to insert into multiple tables.  
- <LOAN_APPLICATION MISMOVersionID="2.3.1">
  <BORROWER BorrowerID="B001" JointAssetLiabilityReportingType="Jointly" _FirstName="Fred" _HomeTelephoneNumber="5551211111" _LastName="Flintstone" _PrintPositionType="Borrower" _SSN="111111111" _UnparsedName="Fred Flintstone" /> 
  <EMPLOYER CurrentEmploymentMonthsOnJob="1" CurrentEmploymentTimeInLineOfWorkYears="20" CurrentEmploymentYearsOnJob="15" EmploymentBorrowerSelfEmployedIndicator="N" EmploymentPositionDescription="Butcherb1" _City="Beverly Hills" _Name="Bedrock Quarry" _PostalCode="90210" _State="CA" _StreetAddress="100 Quartz Way" _TelephoneNumber="5551112121" /> 
  <EMPLOYER CurrentEmploymentMonthsOnJob="0" CurrentEmploymentTimeInLineOfWorkYears="5" CurrentEmploymentYearsOnJob="5" EmploymentBorrowerSelfEmployedIndicator="Y" EmploymentPositionDescription="Owner/Super Modelb1" _City="Beverly Hills" _Name="Taradacdyl Burgers" _PostalCode="90210" _State="CA" _StreetAddress="200 Model Way" _TelephoneNumber="5551112121" /> 
- <BORROWER BorrowerID="B002" JointAssetLiabilityReportingType="Jointly" _FirstName="Wilma" _HomeTelephoneNumber="5551211111" _LastName="Flintstone" _PrintPositionType="CoBorrower" _SSN="111111111" _UnparsedName="Wilma Flintstone">
  <_RESIDENCE BorrowerResidencyBasisType="Own" BorrowerResidencyDurationMonths="0" BorrowerResidencyDurationYears="5" BorrowerResidencyType="Current" _City="Beverly Hills" _PostalCode="90210" _State="CA" _StreetAddress="100 Front Street" /> 
  <_RESIDENCE BorrowerResidencyBasisType="Own" BorrowerResidencyDurationMonths="0" BorrowerResidencyDurationYears="5" BorrowerResidencyType="Current" _City="Beverly Hills" _PostalCode="90210" _State="CA" _StreetAddress="100 Front Street" /> 
  <CURRENT_INCOME IncomeType="Base" _MonthlyTotalAmount="10000.00" /> 
  <CURRENT_INCOME IncomeType="Base" _MonthlyTotalAmount="6000.00" /> 
  <DECLARATION AlimonyChildSupportObligationIndicator="N" BankruptcyIndicator="N" BorrowedDownPaymentIndicator="N" CitizenshipResidencyType="USCitizen" CoMakerEndorserOfNoteIndicator="N" HomeownerPastThreeYearsType="Yes" IntentToOccupyType="Yes" LoanForeclosureOrJudgementIndicator="N" OutstandingJudgementsIndicator="N" PartyToLawsuitIndicator="N" PresentlyDelinquentIndicator="N" PriorPropertyTitleType="JointWithSpouse" PriorPropertyUsageType="PrimaryResidence" PropertyForeclosedPastSevenYearsIndicator="N" /> 
  <EMPLOYER CurrentEmploymentMonthsOnJob="1" CurrentEmploymentTimeInLineOfWorkYears="20" CurrentEmploymentYearsOnJob="15" EmploymentBorrowerSelfEmployedIndicator="N" EmploymentPositionDescription="Butcher" _City="Beverly Hills" _Name="Bedrock Quarry" _PostalCode="90210" _State="CA" _StreetAddress="100 Quartz Way" _TelephoneNumber="5551112121" /> 
  <EMPLOYER CurrentEmploymentMonthsOnJob="0" CurrentEmploymentTimeInLineOfWorkYears="5" CurrentEmploymentYearsOnJob="5" EmploymentBorrowerSelfEmployedIndicator="Y" EmploymentPositionDescription="Owner/Super Model" _City="Beverly Hills" _Name="Taradacdyl Burgers" _PostalCode="90210" _State="CA" _StreetAddress="200 Model Way" _TelephoneNumber="5551112121" /> 
  </BORROWER>
  </LOAN_APPLICATION>

I am able to get the borrower information, using the table and XMlSequence combination. 

  Begin
    FOR Borrrow in
        ( select 
          extractvalue(value(i),'/BORROWER/@BorrowerID') Borrid
          FROM
              IMPORT p ,
              TABLE( xmlsequence( extract(xmltype(p.loanapp), '/LOAN_APPLICATION/BORROWER') ) ) i
           where id= p_id
        )
  Loop
   -- Insert into the tables here for individual values here

  End loop;
How do you use multiple nesting to insert the data pertaining to a particular borrower. I am unable to find a solution for this problem.


Declare 
l_xml xmltype;
begin 
for i in ( 
 select  rownum, 
  value(borr)   data ,  -- This should contain all the information for each borrowe Correct ??? 
 extractvalue( value(borr),'/BORROWER/@BorrowerID') ID 
 FROM
 PF_MINDBOXIMPORT p ,
 TABLE(XMLSEQUENCE(extract(xmltype(p.loanapp), '/LOAN_APPLICATION/BORROWER' ))) borr
 where p.id= 217 )
Loop 
   dbms_output.put_line( i.ID);
 /* here I need to loop again for any multiple tags for the borrower above  i.data is already a V array so why dosen't the code below fail*/
   for j in (  select extractvalue     (i.data,'/EMPLOYER/@EmploymentPositionDescription') desc  from dual  ) 
   Loop 
    -- Insert data for the particular borrower 
    dbms_output.put_line( i.desc );
   end loop;
end loop;
end;
/
SQL> /
 TABLE(XMLSEQUENCE(extract(xmltype(p.loanapp), '/LOAN_APPLICATION/BORROWER' ))) borr
   *
ERROR at line 10:
ORA-06550: line 14, column 84:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 14, column 13:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 29:
PLS-00103: Encountered the symbol "DESC" when expecting one of the following:
<an identifier> <a double-quoted delimited-identifier> delete
exists prior <a single-quoted SQL string>
The symbol "<an identifier> was inserted before


I am trying various combinations of extract , extractvalue functions to get the individual xml segment to loop again for the employment ingformation and I'm unable to achieve 
it.
 
Your suggestions will be of great help.

Thanks in advance.
Raghu
 

was wondering if anyone can help me out or if there is a way to do this in xml.
I have a xml of the form -

<RESPONSE ResponseDateTime="2006-11-16T11:46:37">
<KEY _Name="CustomerNumber" _Value="7303881"/>
<KEY _Name="TransactionNumber" _Value="001010021"/>
<KEY _Name="Type" _Value="UNDERWRITING_RESPONSE1.1"/>
<KEY _Name="CompanyID" _Value="00006"/>
</RESPONSE>

I need to loop thru the KEY elements to parse the attributes whose Values depend
on the Name.
in other words -
when CustomerNumber then 7303881
when TransactionNumber then 001010021
when Type then RESPONSE
when CompanyID then 00006.

I started this out with, but doesn't seem to do anything-

declare
xml XMLTYPE;
v_node_name varchar2(60);
v_cust_nbr varchar2(60);
v_TransactionNumber varchar2(60);
v_Type varchar2(60);
v_CompanyID varchar2(60);

begin
loop
v_node_name := xml.extract('/RESPONSE/KEY/@_Name').getstringval();
case when v_node_name = 'LoanNumber' then
v_cust_nbr := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
when v_node_name = 'TransactionNumber' then
v_TransactionNumber := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
when v_node_name = 'Type' then
v_Type := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
when v_node_name = 'CompanyID' then
v_CompanyID := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
end case;
end loop;
end;

Hi Tom,

I have trillions of records in table with XMLType column. It contents some descriptive parts like news. Now I want to search particular string in the node, extract take very huge time (response time is high). Is it possible to search from the XML with very huge node?

e.g. Would like to search for test in below string, just example however the data will be huge in source.

<?xml version="1.0"?>
<Record>
<Type>UPDATE</Type>
<Source>I was interested in using the multi-table insert feature in 9i but am stumped by the
restriction that the subquery can't contain a sequence. I use a sequence to assign a
Primary Key in one of the tables that is the target of the multi-table insert. Can you
suggest a workaround? Will calling a user-defined function that returns the test nextval of
the sequence work in the subquery?
</Source>
<Instrument>NEWS</Instrument>
</Record>


<?xml version="1.0"?>
<Record>
<Type>UPDATE</Type>
<Source>What about the situtation where the targets of the multi-table insert have a sequence value in
common between them? In the following example, I'd like the s_id column value to be identical for
each row inserted into both tables:</Source>
<Instrument>NEWS</Instrument>
</Record>


<?xml version="1.0"?>
<Record>
<Type>UPDATE</Type>
<Source>You want to look into the XMLType in 9i R2. There are some cool features you
can use to accomplish what you want to do. XMLSEQUENCE is a cool feature that
will break down many nodes into individual xmltypes, and then the TABLE
function will turn those into rows. This will make sense in the code sample
below. Basically, I'd use a table to insert the XML document into, and then
have an on insert trigger manage breaking out the values</Source>
<Instrument>NEWS</Instrument>
</Record>

Urgent response ..........

Thanks in advance.