Getting started loading XML data into a Oracle table

Posted on 2009-02-17
Last Modified: 2013-12-18
I'm getting started in XML for Oracle tables and I did the following by creating a table, inserted one record from a simplified XML file, and queried the data.  The initial question was going to be 'How do I index each of these record?' but reading through this 'Oracle XML DB
Developer's Guide' I assume this is the wrong approach and I should be loading the entire XML file (XLS also?)?  At this point I just need to be pointed in the right direction.  I'd prefer to do it all just using SQL and PL/SQL without Java.  I'm using Oracle 10.2.  So are there specific dbms_ procedures or packages I should focus on to do this 'easily'?

1.  I created an Oracle table,

SQL> create table table_xml_data (record_id number, col_xml_data xmltype);

Table created.

2.  Inserted data,

insert into table_xml_data values (1,xmltype( '<?xml version="1.0" encoding="UTF-8" ?>
   <digital record= "0712" fm_date = "11/22/07" to_date = "12/20/07" >
   <state ID= "TX" state = "Texas" >
   <city> "Dallas" </city>
   </digital>  '));

1 row created.

SQL> select extractValue(col_xml_data,'/digital/state/city') from table_xml_data;


SQL> select extractValue(col_xml_data,'/digital/state/@ID') from table_xml_data;

XML file starts as follows, <?xml version="1.0" encoding="UTF-8">

XSL file  starts as follows, 

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="" xmlns:fo="">

	<xsl:output method="xml" indent="yes"/>

	<xsl:template match="/">

		<xsl:if test="@template_to_use='save'">

			<xsl:call-template name="save"/>



	<xsl:variable name="c_flag" select="@c_flag"/>

	<xsl:variable name="m_flag" select="@m_flag"/>

	<xsl:variable name="d_flag" select="@d_flag"/>

	<xsl:template name="save">

		<xsl:element name="digital">

			<xsl:for-each select="state"> ...

Open in new window

Question by:talahi
    LVL 73

    Expert Comment

    I'm not sure what you're looking for.

    Can you post your expected output, if necessary build a larger xml example to demonstrate

    Author Comment

    I'm trying to load a very large XML file into a table and I'm not sure of the correct way to do this.

    If I try to insert the entire file I get an error saying its too big.

    insert into table_xml_data values (xmltype( '<?xml version="1.0" encoding="UTF-8" ?>  ...'));

    Does that mean I have to find some way to parse it by individual XML records and then insert the data into the table.  If I do have to parse it into individual records is there a dbms_ procedure to help do this?  I'm starting this with no XML background except what I learn above.

    Author Comment

    I was wrong to try and cancel this request.  I thought I saw someone who had a similar question answered but the answer didn't relate to my question.   I'm still looking for a way to insert a very large XML file into a table so it can be searched.  I assume the XML file needs to be parsed into individual records and these records inserted into the table.  What is the most common way to do this?
    LVL 73

    Expert Comment

    Can you upgrade to 11g?  Prior to 11g there is a 64K limit on the size of a clob that may be parsed into an xmltype.

    Author Comment

    Tried it on 11g but got the same type error 'ORA-01704: sting literal too long' 'literial is longer than 4000 character'.

    created table as,

    create table xml_test_doc of XMLType;

    In my readings there is a topic 'Registering and Implementing Schema-Typed Data'.  Is this an area I should focus on to do this task?  
    LVL 73

    Expert Comment

    oh, I'm sorry, I was misunderstanding the problem.

    You can load a clob variable with a large string
    but no single string literal can be more than 4000 characters within a sql statement
    you can use 32K strings in pl/sql

    build a clob variable in pieces by appending text in 32K chunks

    then use the xmltype constructor to turn the clob into an xmltype object
    LVL 73

    Accepted Solution

    since you want additional help, I'm clicking the "object" button to keep the question open

    Author Closing Comment

    I found most of what I needed on my own but since you were the only one who responded I'll give you the credit and points since I'll probably need more help in this area reading the data out.
    LVL 73

    Expert Comment

    ok, but if you didn't get your answer could have kept the question going by responding with the results of what I suggested or questions if you didn't understand

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle Query - Remove one of 2 duplicate rows 7 53
    Oracle View 13 36
    oracle 10g table containing BLOBS 3 33
    Extracting Specific String 11 49
    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now