Environment: Oracle 9iR2 / Oracle HTTP Server / mod_plsql


Guys a little help if possible. Our app runs using pl/sql exclusively.

We wil be given an XML schema defintion and XL documents cotaining the
data. The schema defitions starts like:

<?xml version="1.0" encoding="UTF-8" ?>
<!-- edited with XML Spy v4.4 U (http://www.xmlspy.com) by Dick Lemm
(Adnovate) -->
<!-- edited with Microsoft Development Environment by Donald Lubbers
(Hemels van der Hart) -->
<!--W3C Schema generated by XML Spy v4.4 U (http://www.xmlspy.com)-->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="JOBS">
....
....


The first thing we need to do I think is validate the XML document
against the XML schema definition. What procedure should we use to do
that?

If we pss this phase then we want to get the data out of the XML
document and feed it into the APIs we already built.

I have seen dbms_xmlsave which seems to take XML data and store it in a
table - I have played with this and it seems to work for simple cases.
But part of the XML schema defines data items that would sit in a child
table such as... can we still use dbms_xmlsave?

If not I *think* an alternative approach is to parse the XML to retrieve
the data values - which procedure would we use to do that? And would we
be saying 'give me the data value that corresponds to field1'?


Finally as I understand it (which may be wrong of course!) XML schema
defitiions supercede DTDs? I have seen references in Oracle manuals to
DTDs but lss to schema definitions - can the oracle procedures handle
both?

Really appreciate some assistance here!

Many thanks

--

jeremy

Jeremy - comments in-line and generic in the sense I am assuming
interaction with the database directly (and not through mod_plsql,
Oracle http server which you are using). The Oracle book of relevance
is B10790-01.pdf.

>
> The first thing we need to do I think is validate the XML document
> against the XML schema definition. What procedure should we use to do

> that?

When you populate a table column with XML data stored in a table column
of type XMLTYPE, by default the XML is not fully validated upon insert
or update. You will have to code this yourself. The idea is that before
the XML is persisted it has already been validated - why perform an
unnecessary grunt job and pay the performance cost for doing so. If you
must validate the XML within the database upon insert or update, have a
look at the member procedure SCHEMAVALIDATE. Basically you will need to
code an insert/update trigger to invoke .SCHEMAVALIDATE on the
:NEW.xmltypetablecolumn. As I have stated above, there is a performance
penalty for doing this and sometimes it is a very significant one!

If you are storing your XML in a lob of some flavour, things become a
little more complex.

>
> If we pss this phase then we want to get the data out of the XML
> document and feed it into the APIs we already built.
>

The APIs you have already built, they are built using the same data
represented relationally? If so, search the document referenced above
for XMLTYPE VIEWs

>
> I have seen dbms_xmlsave which seems to take XML data and store it in
a
> table - I have played with this and it seems to work for simple
cases.
> But part of the XML schema defines data items that would sit in a
child
> table such as... can we still use dbms_xmlsave?
>

Here is a verbatim extract from chapter 11/document referred to above:
"DBMS_XMLSAVE is a wrapper around a Java vlass, DMBS_XMLSTORE is
implemented in C inside the database. This should significantly improve
performance". Note the Oracle docs drawing the readers attention to
potential performance issues.

Re this comment about a child table. Yes you can still use
DBMS_XMLSAVE. But before you embark further you will need to understand
that with XMLDB, FK and check constraints are not implemented as might
first think. You may find that there is considerable effort required on
your behalf to code up these FK and check constraints. Having done this
in the past, I also suggest you read up the options open to you for
indexing the XML content beforehand as there might be some surprises
for you there. Of course you are not prevented from implementing FK and
other contraints outside the XML per-se.

Rather than using DBMS_XMLSAVE etc, you may just find it easier and
flexible to insert etc directly manipulating an XMLTYPE table column
just as already do with NUMBERs, VARCHAR2s, DATEs, xLOBs etc.

>
> If not I *think* an alternative approach is to parse the XML to
retrieve
> the data values - which procedure would we use to do that? And would
we
> be saying 'give me the data value that corresponds to field1'?

For the most part I always find it easier to use an XPATH expression
and the EXTRACT function. The exact syntax will depend on your XML
schema defn but something not unlike this should do the trick.

SELECT
EXTRACT(x.yourCoDetails,'Company/Employees/Employee/NHSNumber/text()').GETSTRINGVAL()
AS NHSNO
FROM tblEmployee x
WHERE 1=EXISTSNODE(x.yourCoDetails,'Company/Employees/Employee');

>
>
> Finally as I understand it (which may be wrong of course!) XML schema

> defitiions supercede DTDs? I have seen references in Oracle manuals
to
> DTDs but lss to schema definitions - can the oracle procedures handle

> both?
>

Restricted support for DTDs; read the few sentences on page 5-9 in the
document referenced above.

Regards
Mike

TESSELLA Michael.OShea@tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429

In article <1110114217.643912.293250@z14g2000cwz.googlegroups .com>,
Michael O'Shea says...
>
> Jeremy - comments in-line and generic in the sense I am assuming
> interaction with the database directly (and not through mod_plsql,
> Oracle http server which you are using). The Oracle book of relevance
> is B10790-01.pdf.




<SNIP>

Thanks for all these comments Mike you have been very helpful


--

jeremy