[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

Getting started loading XML data into a Oracle table

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

0
talahi
Asked:
talahi
  • 5
  • 4
1 Solution
 
sdstuberCommented:
I'm not sure what you're looking for.

Can you post your expected output, if necessary build a larger xml example to demonstrate
0
 
talahiAuthor 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.
0
 
talahiAuthor 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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
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.
0
 
talahiAuthor 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?  
0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
since you want additional help, I'm clicking the "object" button to keep the question open
0
 
talahiAuthor 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.
0
 
sdstuberCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now