-- --Original Message-- --
From: Davey, Alan=0D
Sent: Friday, October 08, 2004 3:41 PM
To: 'oracle-l-bounce@(protected) '
Subject: some newbie xml questions
Hi,
I 'm trying to work through the xml examples in the not-so FM and finding
I have more questions than answers. This is on Oracle 9.2.0.5 running
on Windows.
I 've created a simple table:
create table test_po (
po_id integer not null,
po_xml xmltype)
/
Now, I want to start querying the data. I can extract the description
using the following sql:
adavey@(protected) > ed
Wrote file afiedt.buf
1 select po_id, extract(value(d), '//text() ').getstringval()
description
2 from test_po p,
table(xmlsequence(extract(p.po_xml, '/PurchaseOrder/*//Description '))) d
3* where p.po_id =3D 1
adavey@(protected) > /
PO_ID DESCRIPTION
-- ---- -- -- ---- ---- ---- --
1 The Ruling Class
1 Diabolique
1 8 1/2
So far so good. But no matter what I try, I can 't seem to figure out
how to extract ItemNumber, "Part Id ", Quantity, or UnitPrice. Do the
extract and path strings not work properly for elements without proper
tags around them?? Am I going to have to extract at say the LineItems
level and then use instr/substr to extract this information? Blech.
Other questions: =0D
Does the validation methods work when validating an xml file against a
schema definition (xsd) file? I registered the purchaseOrder.xsd that
was part of this example and recreated the table using the xmltype store
as object relational ... syntax. =0D
CREATE TABLE TEST_PO
(
PO_ID INTEGER,
PO_XML XMLTYPE
)
XMLTYPE COLUMN PO_XML STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://www.oracle/xsd/purchaseOrder.xsd "
ELEMENT "PurchaseOrder "
I loaded this xml document and then in pl/sql did:
declare
xmldoc xmltype;
begin
select po_xml into xmldoc
from test_po
where po_id =3D 1;
Its possible that the xsd file in the Oracle docs has errors, so I will
have to look at it in more detail.
What are some of the performance issues that you have seen in regards to
XML files? For an upcoming project, I will be dealing with xml files
~17MB in size for an upcoming project. How do I index the po_xml column
to better handle information retrieval? Do I create function based
indexes using the extract() methods for those paths that I will be going
after?
"This information in this e-mail is intended solely=0D
for the addressee and may contain information=0D
which is confidential or privileged. Access to this
e-mail by anyone else is unauthorized. If you=0D
are not the intended recipient, or believe that=0D
you have received this communication in error,=0D
please do not print, copy, retransmit,=0D
disseminate, or otherwise use the information.=0D
Also, please notify the sender that you have=0D
received this e-mail in error, and delete the=0D
copy you received. "
--
http://www.freelists.org/webpage/oracle-l
