Some newbie xml questions

From: Davey, Alan <Alan.Davey_at_encodasystems.com>
Date: Mon, 11 Oct 2004 07:23:03 -0500
Message-ID: <746DCE6FEC476842B2229E15EBFC1B9E0B4484@mempexc0013.encodasystem s.net>

=0D

-----Original Message-----
From: Davey, Alan=0D
Sent: Friday, October 08, 2004 3:41 PM
To: 'oracle-l-bounce_at_freelists.org'
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)
/

Then populated with this simple xml file: <PurchaseOrder=0D

xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =0D
xsi:noNamespaceSchemaLocation=3D"http://www.oracle.com/xdb/purchaseOrder.x sd">
<Reference>ADAMS-20011127121040988PST</Reference>
<Actions>

<Action>

      <User>SCOTT</User>
      <Date xsi:nil=3D"true"/>

</Action>
</Actions>
<Reject/>
<Requestor>Julie P. Adams</Requestor>
<User>ADAMS</User>
<CostCenter>R20</CostCenter>
<ShippingInstructions>

    <name>Julie P. Adams</name>
    <address>300 Oracle Parkway, Redwood Shores, CA 94065</address>
    <telephone>650 506 7300</telephone>

</ShippingInstructions>
<SpecialInstructions>Ground</SpecialInstructions>
<LineItems>

<LineItem ItemNumber=3D"1">

      <Description>The Ruling Class</Description>
      <Part Id=3D"715515012423" UnitPrice=3D"39.95" Quantity=3D"2"/>

</LineItem>
<LineItem ItemNumber=3D"2">

      <Description>Diabolique</Description>
      <Part Id=3D"037429135020" UnitPrice=3D"29.95" Quantity=3D"3"/>

</LineItem>
<LineItem ItemNumber=3D"3">

      <Description>8 1/2</Description>
      <Part Id=3D"037429135624" UnitPrice=3D"39.95" Quantity=3D"4"/>

</LineItem>
</LineItems>

</PurchaseOrder>

Now, I want to start querying the data. I can extract the description using the following sql:
adavey_at_QA26> 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_at_QA26> /

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;

xmldoc.schemaValidate();
end;

and received:
declare
*
ERROR at line 1:

ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00343: "Date" is not nillable
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 8

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?

Thanks,

Alan Davey

"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

Received on Mon Oct 11 2004 - 07:37:29 CDT