We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Getting started loading XML data into a Oracle table

talahi
talahi asked
on
Medium Priority
993 Views
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>
   </state>
   </digital>  '));
   
   commit;

1 row created.

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

EXTRACTVALUE(COL_XML_DATA,'/DIGITAL/STATE/CITY')
----------------------------------------------------------------------------------------------------
"Dallas"



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

EXTRACTVALUE(COL_XML_DATA,'/DIGITAL/STATE/@ID')  
--------------------------------------------------------------------------------
TX
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="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
	<xsl:output method="xml" indent="yes"/>
	<xsl:template match="/">
		<xsl:if test="@template_to_use='save'">
			<xsl:call-template name="save"/>
		</xsl:if>
	</xsl:template>
	<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

Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Commented:
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

Commented:
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?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Commented:
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?  
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
since you want additional help, I'm clicking the "object" button to keep the question open

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.