Dear Tom,
I am new to XML and don't know Java and, unfortunately, do not have time to learn
it right now as I need to have a little prototype done of this in a couple
of days. I did a lot of reading on your site and reviewed many of the OTN
references but I was not able to connect to my problem, probably because
I don't know enough. I am using Oracle 9i release 2 at patch 4 on a Windows 2000 server.
I also want to do this all in PL/SQL. Here is my requirement:
1) I download an XML file from the Internet and save it to a network drive.
2) I also have users who may, if the website is down, enter the data via
a .NET application, which assembles it into an XML document that gets saved to the file.
3) I then put the XML document into a table (I have done this step with using a CLOB and a column of XMLType). I then need to write a PL/SQL procedure that does some preliminary validation
on some of the fields. For example, issue date must occur before maturity
date, auction date must occur after issue date, etc.
This procedure will be called by both the .NET application to
do the preliminary validation and will also be executed as a batch process
against those files that are downloaded from the Internet.
4) Ultimately, once the preliminary validation is complete the data from the XML file needs to
be loaded into legacy database tables. The existing tables do not have field names that
correspond anywhere near the XML field names as we used to type most of the data in
manually. But I do not have to go directly to the end table. I am perfectly
happy creating a table that corresponds to the XML fields and then inserting the relevant
data into the end tables from the table that corresponds to the XML fields is that is easier.
I am including an example of the type of XML document I am dealing with.
What is a good way to do this given that I want to do this in PL/SQL? I have played with a number of test cases (registering the
schema, creating an XSLT, etc. but I have not been having much luck).
Any help would be greatly appreciated. Thank you.
<?xml version="1.0" encoding="UTF-8" ?>
- <bpd:AICAuctionData xmlns:bpd="</code>
http://www.publicdebt.treas.gov/" xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
http://publicdebt.treas.gov/ http://publicdebt.treas.gov/xsd/AICAuction.xsd" > <code>
- <AuctionAnnouncement>
<SecurityTermWeekYear>4-WEEK</SecurityTermWeekYear>
<SecurityTermDayMonth>29-DAY</SecurityTermDayMonth>
<SecurityType>BILL</SecurityType>
<CUSIP>912795RN3</CUSIP>
<AnnouncementDate>2004-10-25</AnnouncementDate>
<AuctionDate>2004-10-26</AuctionDate>
<IssueDate>2004-10-28</IssueDate>
<MaturityDate>2004-11-26</MaturityDate>
<OfferingAmount>20.0</OfferingAmount>
<CompetitiveTenderAccepted>Y</CompetitiveTenderAccepted>
<NonCompetitiveTenderAccepted>Y</NonCompetitiveTenderAccepted>
<TreasuryDirectTenderAccepted>N</TreasuryDirectTenderAccepted>
<AllTenderAccepted>N</AllTenderAccepted>
<TypeOfAuction>SINGLE PRICE</TypeOfAuction>
<CompetitiveClosingTime>11:30</CompetitiveClosingTime>
<NonCompetitiveClosingTime>11:00</NonCompetitiveClosingTime>
<NetLongPositionReport>7000000000</NetLongPositionReport>
<MaxCompetitiveBid>35</MaxCompetitiveBid>
<MaxSingleBid>35</MaxSingleBid>
<CompetitiveBidDecimals>3</CompetitiveBidDecimals>
<AllocationPercentageDecimals>2</AllocationPercentageDecimals>
<MinCompetitiveAmount>1000</MinCompetitiveAmount>
<MinNonCompetitiveAmount>1000</MinNonCompetitiveAmount>
<MultiplesToBid>1000</MultiplesToBid>
<MinToIssue>1000</MinToIssue>
<MultiplesToIssue>1000</MultiplesToIssue>
<MatureSecurityAmount>19001.0</MatureSecurityAmount>
<OutstandingAmount>46624</OutstandingAmount>
<SOMAIncluded>N</SOMAIncluded>
<FederalReserveHoldings>14991.0</FederalReserveHoldings>
<FIMAIncluded>Y</FIMAIncluded>
<ForeignInstitutionalHoldings />
<PayDownNewCashIndicator>NC</PayDownNewCashIndicator>
<PayDownNewCashAmount>999.0</PayDownNewCashAmount>
<Series />
<InterestRate />
<FirstInterestPaymentDate />
<StandardInterestPayment />
<FrequencyInterestPayment />
<StrippableIndicator />
<MinStripAmount />
<CorpusCUSIP />
<TINTCUSIP1 />
<TINTCUSIP2 />
<ReOpeningIndicator />
<OriginalIssueDate />
<BackDated />
<BackDatedDate />
<LongShortNormalCoupon />
<LongShortCouponFirstInterestPayment />
<Callable />
<CallDate />
<InflationIndexSecurity />
<ReferenceCPIDated />
<IndexRatioOnIssueDate />
<CPIBasePeriod />
<TIINConversionFactor />
<AccruedInterest />
<DatedDate>2004-05-27</DatedDate>
</AuctionAnnouncement>
</bpd:AICAuctionData>
I asked Sean Dillon, my XML Technologist, to take a look at this and here's what he had to say:
---
Hi Lisa,
This should be relatively easy to accomplish, aside from the .NET app :). Let's take it a step at a time.
First, getting the XML into the database. There's no reason why you should go to the web, download the XML and save it off to some file on the hard drive, then put it into the database w/ some process. Why not just tell the database the URL and have it go and retrive the XML directly? You can do this w/ HTTPUri's:
----------------------------------------
sdillon@GRUVDB> declare
2 l_url httpuritype;
3 l_xml xmltype;
4 begin
5 l_url := httpuritype.createuri('</code>
http://asktom.oracle.com/pls/ask/ask_tom.newest.rss' ;
6 l_xml := l_url.getxml();
7 --
8 dbms_output.put_line(substr(l_xml.extract('/').getstringval(),1,250));
9 end;
10 /
<rss version="0.91">
<channel>
<title>Ask Tom MOST RECENT ARTICLES</title>
<link> http://asktom.oracle.com </link>
<description>These are the questions on Ask Tom that have the most comments</description>
<language>en-us</language>
PL/SQL procedure successfully completed.
----------------------------------------
I assume the url changes from time to time, so you could simply pass the url into a function that retrieves the XML document and saves it as a new row in a table w/ an XMLType column. You might store the timestamp of the request, the url of the xml request, and the content at the time of retrieval (with the thought that it might change over time).
That's ONE way to get the XML into your database w/o having to d/l it manually, store it into the db through a proc or whatnot.
Next you mentioned you have some business rules you want to check before you put the data in. You can do this through the same procedure you use to retrieve the XML, or better yet you can do it through an insert trigger. (as you'll also be inserting XML from a .NET application that users can use to put XML into the table as well).
In this example, I'll create the table and add a trigger that checks the version of the RSS I'm inserting. I'll call the asktom rss links two times, one for version .91 and one for version 2. In the validation, if I get anything other than v2 rss, the insert will bail.
-----------------------------------
sdillon@GRUVDB> create table xmldocs( x xmltype )
2 /
Table created.
sdillon@GRUVDB> create or replace trigger xmldocs_bifer
2 before insert
3 on xmldocs
4 for each row
5 declare
6 l_rssver number;
7 l_xml xmltype := :new.x;
8 begin
9 l_rssver := l_xml.extract('/rss/@version').getNumberVal();
10 if l_rssver <> '0.91' then
11 raise_application_error(-20001,'Wrong RSS version!');
12 end if;
13 end xmldocs_bifer;
14 /
Trigger created.
sdillon@GRUVDB> insert into xmldocs
2 values (httpuritype.createuri(' http://asktom.oracle.com/pls/ask/ask_tom.newest.rss' .getxml())
3 /
1 row created.
sdillon@GRUVDB> -- we'll need this commit later
sdillon@GRUVDB> commit;
Commit complete.
sdillon@GRUVDB> insert into xmldocs
2 values (httpuritype.createuri(' http://asktom.oracle.com/pls/ask/ask_tom.newest.rss?p_version=2' .getxml())
3 /
values (httpuritype.createuri(' http://asktom.oracle.com/pls/ask/ask_tom.newest.rss?p_version=2' .getxml())
*
ERROR at line 2:
ORA-20001: Wrong RSS version!
ORA-06512: at "DHS.XMLDOCS_BIFER", line 7
ORA-04088: error during execution of trigger 'DHS.XMLDOCS_BIFER'
-----------------------------------
Now, as for loading the data into a relational table. You can do this any number of ways. You can either query the document for xpath expressions and insert the data into the table based on the results, or you can simply use DOM to parse the document and insert the values based on the results you get. You can search asktom for an example of using DOM to extract data from an XML document, as well as doing xpath queries.
-----------------------------------
sdillon@GRUVDB> create table xmldoc_values(
2 title varchar2(100),
3 link varchar2(100),
4 descr clob,
5 lang varchar2(100),
6 copyr varchar2(100)
7 )
8 /
Table created.
sdillon@GRUVDB> --now query the record we inserted (and committed) earlier
sdillon@GRUVDB> begin
2 for i in (select xmltype.extract(x,'/rss/channel/title/text()').getStringVal() title,
3 xmltype.extract(x,'/rss/channel/link/text()').getStringVal() link,
4 xmltype.extract(x,'/rss/channel/description/text()').getStringVal() descr,
5 xmltype.extract(x,'/rss/channel/language/text()').getStringVal() lang,
6 xmltype.extract(x,'/rss/channel/copyright/text()').getStringVal() copyright
7 from xmldocs) loop
8 insert into xmldoc_values( title, link, descr, lang, copyr )
9 values (i.title, i.link, i.descr, i.lang, i.copyright);
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
sdillon@GRUVDB> select * from xmldoc_values
2 /
TITLE
---------
LINK
---------
DESCR
---------
LANG
---------
COPYR
---------
Ask Tom MOST RECENT ARTICLES
http://asktom.oracle.com <code>
These are the questions on Ask Tom that have the most comments
en-us
Copyright 1999-2003, Oracle Corporation
-----------------------------
Hope that helps!
_smd_
Sean Dillon