Re: Loading a XML file into DB tables

The first thing to ask is how you want to represent your XML in the database:

- as normal relational data normalised into a set of tables;
- as object-relational data in one or more tables;
- as XML in one column of type sys.XMLtype (form 9i onwards);

If the XML-dtd is complicated, the first two options can become quite involved. The XML SQL Utility is rather basic; it doesn't even handle XML-attributes. Anyway, if this is what you want you can often make yout job easier by first performing an XSLT transformation to make your XML simpler.

I found that quite often you don't need to normalize your XML data. You want to store it in the database because it is safe there, but you just want to retrieve data from it, and not necessarily through SQL. In that case the third option is very easy to use. Loading can be done through SQL*Loader. In the sys.XMLtype column the XML is stored as a parse tree (roughly) and all elements and attributes can be retrieved through XPath expressions. Actually you can even do this in PL/SQL. So if table T contains an XMLtype column XMLdata, you would write something like
select XMLdata.extract('XPath-expression').getStringVal() from T;

All this info can be found in the Oracle 9i manuals

Hth
rob van lopik

"Sunder" <sunder.nr_at_in.bosch.com> wrote in message news:ambus2$hbt$1_at_ns2.fe.internet.bosch.com...
> Hello All,
>
> I was able to find sample code loading simple xml file into a DB
> table (loading data into the emp table). However, I have to load a fairly
> complex xml file into my database. Does anybody have experiece with this?
> Can anybody let me know where to find help on the dbms_xmlquery package ?
> Can anybody provide sample code ?
>
> Thanks,
> Sunder
>
>
Received on Thu Sep 19 2002 - 12:34:23 CDT