Re: Parsing XML and validating XML

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 6 Mar 2005 05:03:37 -0800
Message-ID: <1110114217.643912.293250@z14g2000cwz.googlegroups.com>

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_at_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 Received on Sun Mar 06 2005 - 07:03:37 CST