My goal is to create relational views over the XML data.
I am having difficulty with XMLExists and XMLQuery syntax.
I am working through the examples in Oracle XML Developers Guide 11g release 2 (11.2) adapted for my schema and instance.
The error is ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name:
Any suggestions on how to navigate the element path for XMLExists, XMLQuery, and XMLCast?
This is a follow on question to
2212262
PHARMA@scidev> select * from v$version;
BANNER
-------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
-- The XMLDB is installed and working.
VAR SCHEMAURL VARCHAR2(700)
--
begin
:SCHEMAURL := 'http://lims.drugbank.ca/docs/drugbank.xsd';
end;
/
DECLARE
V_XMLSCHEMA XMLTYPE := xmltype(bfilename('XMLPHARMA', 'drugbank.xsd'),nls_charset_id('AL32UTF8'));
BEGIN
DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XMLSCHEMA);
DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDOM(V_XMLSCHEMA);
DBMS_XMLSCHEMA_ANNOTATE.setSQLType(V_XMLSCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'BondType',DBMS_XDB_CONSTANTS.XSD_ELEMENT,'references','CLOB',TRUE);
DBMS_XMLSCHEMA_ANNOTATE.setSQLType(V_XMLSCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'PartnerType',DBMS_XDB_CONSTANTS.XSD_ELEMENT,'references','CLOB',TRUE);
DBMS_XMLSCHEMA_ANNOTATE.setSQLType(V_XMLSCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'DrugType',DBMS_XDB_CONSTANTS.XSD_ELEMENT,'general-references','CLOB',TRUE);
DBMS_XMLSCHEMA_ANNOTATE.setSQLType(V_XMLSCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'SequenceType',DBMS_XDB_CONSTANTS.XSD_ELEMENT,'chain','CLOB',TRUE);
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => :SCHEMAURL,
SCHEMADOC => V_XMLSCHEMA,
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => TRUE);
END;
/
CREATE TABLE drugs_xmltype OF XMLTYPE
XMLSCHEMA "http://lims.drugbank.ca/docs/drugbank.xsd"
ELEMENT "drugs"
/
SELECT table_name FROM user_xml_tables;
TABLE_NAME
------------------------------
DRUGS_XMLTYPE
1 row selected.
SELECT COUNT(*) FROM user_nested_tables;
COUNT(*)
---------------
36
1 row selected.
INSERT INTO drugs_xmltype
VALUES(XMLType(bfilename('XMLPHARMA', 'drugbank.xml'),nls_charset_id('AL32UTF8')))
;
1 row created.
commit;
Commit complete.
desc drugs_xmltype
Name Null? Type
----------------------------------------------------------------------------- -------- ----------------------------------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://lims.drugbank.ca/docs/drugbank.xsd" Element "drugs") STORAGE Object-relational TYPE "drugs1609_T"
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'PHARMA',
estimate_percent => 100
);
END;
/
schema document generated by XMLDB
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" targetNamespace="http://drugbank.ca" xmlns="http://drugbank.ca" elementFormDefault="qualified" attributeFormDefault="unqualified" xdb:storeVarrayAsTable="true" xdb:flags="2105639" xdb:schemaURL="http://lims.drugbank.ca/docs/drugbank.xsd" xdb:schemaOwner="PHARMA" xdb:numProps="177">
...snip
<xs:complexType name="DrugType" xdb:maintainDOM="false" xdb:SQLType="DrugType1543_T" xdb:SQLSchema="PHARMA">
<xs:sequence>
<xs:element name="drugbank-id" type="xs:string" xdb:propNumber="5488" xdb:global="false" xdb:SQLName="drugbank-id" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:MemInline="true" xdb:SQLInline="true" xdb:JavaInline="true"/>
<xs:element name="name" type="xs:string" xdb:propNumber="5489" xdb:global="false" xdb:SQLName="name" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:MemInline="true" xdb:SQLInline="true" xdb:JavaInline="true"/>
...snip
<xs:element name="drugs" xdb:defaultTable="" xdb:propNumber="5446" xdb:global="true" xdb:SQLName="drugs" xdb:SQLType="drugs1609_T" xdb:SQLSchema="PHARMA" xdb:memType="258" xdb:defaultTableSchema="PHARMA">
<xs:complexType xdb:maintainDOM="false" xdb:SQLType="drugs1609_T" xdb:SQLSchema="PHARMA">
<xs:sequence>
<xs:element name="drug" type="DrugType" minOccurs="0" maxOccurs="unbounded" xdb:propNumber="5443" xdb:global="false" xdb:SQLName="drug" xdb:SQLType="DrugType1543_T" xdb:SQLSchema="PHARMA" xdb:memType="258" xdb:MemInline="false" xdb:SQLInline="true" xdb:JavaInline="false" xdb:SQLCollType="drug1610_COLL" xdb:SQLCollSchema="PHARMA"/>
instance document
<drugs xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://drugbank.ca"
xs:schemaLocation="http://drugbank.ca http://lims.drugbank.ca/docs/drugbank.xsd">
<drug type="biotech" created="2005-06-13 07:24:05 -0600" version="3.0" updated="2010-11-25 15:36:58 -0700">
<drugbank-id>DB00001</drugbank-id>
...snip
XMLTable is working with XMLNamespaces for 1 row per drug, nested tables with fields, and nested tables with only one field
the XMLNamespaces example is from this thread
9365771
CREATE OR REPLACE VIEW pharma.drugs_vw AS
SELECT d.*
FROM drugs_xmltype dx, XMLTable(XMLNamespaces(default 'http://drugbank.ca'),
'/drugs/drug'
PASSING dx.OBJECT_VALUE COLUMNS
drugbank_id VARCHAR2(20) PATH 'drugbank-id',
drug_name VARCHAR2(50) PATH 'name',
description VARCHAR2(4000) PATH 'description',
cas_number VARCHAR2(20) PATH 'cas-number',
general_references VARCHAR2(4000) PATH 'general-references',
synthesis_reference VARCHAR2(4000) PATH 'synthesis-reference',
indication VARCHAR2(4000) PATH 'indication',
pharmacology VARCHAR2(4000) PATH 'pharmacology',
mechanism_of_action VARCHAR2(4000) PATH 'mechanism-of-action',
toxicity VARCHAR2(4000) PATH 'toxicity',
biotransformation VARCHAR2(4000) PATH 'biotransformation',
absorption VARCHAR2(4000) PATH 'absorption',
half_life VARCHAR2(4000) PATH 'half-life',
protein_binding VARCHAR2(4000) PATH 'protein-binding',
route_of_elimination VARCHAR2(4000) PATH 'route-of-elimination',
volume_of_distribution VARCHAR2(4000) PATH 'volume-of-distribution',
clearance VARCHAR2(4000) PATH 'clearance'
) d
ORDER BY d.drugbank_id
;
PHARMA@scidev> select drugbank_id, drug_name from drugs_vw where rownum <= 5;
DRUGBANK_ID DRUG_NAME
-------------------- --------------------------------------------------
DB00001 Lepirudin
DB00002 Cetuximab
DB00003 Dornase Alfa
DB00004 Denileukin diftitox
DB00005 Etanercept
5 rows selected.
Elapsed: 00:00:00.89
CREATE OR REPLACE VIEW pharma.packager_vw AS
SELECT dt.drugbank_id, dt.cas_number, pack.*
FROM drugs_xmltype dx,
XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/drugs/drug'
PASSING dx.OBJECT_VALUE COLUMNS
drugbank_id VARCHAR2(20) PATH 'drugbank-id',
cas_number VARCHAR2(20) PATH 'cas-number',
packager XMLTYPE PATH 'packagers/packager') dt,
XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/packager'
PASSING dt.packager COLUMNS
packager_name VARCHAR2(30) PATH 'name',
packager_url VARCHAR2(30) PATH 'url') pack
ORDER BY dt.drugbank_id
;
PHARMA@scidev> select drugbank_id, packager_name from packager_vw where rownum <= 5;
DRUGBANK_ID PACKAGER_NAME
-------------------- ------------------------------
DB00001 Bayer Healthcare
DB00001 Berlex Labs
DB00002 Cardinal Health
DB00002 ImClone Systems Inc.
DB00002 Catalent Pharma Solutions
CREATE OR REPLACE VIEW pharma.secondary_accession_number_vw AS
SELECT dt.drugbank_id, dt.cas_number, sa.*
FROM drugs_xmltype dx,
XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/drugs/drug'
PASSING dx.OBJECT_VALUE COLUMNS
drugbank_id VARCHAR2(20) PATH 'drugbank-id',
cas_number VARCHAR2(20) PATH 'cas-number',
secondary_accession_number XMLTYPE PATH 'secondary-accession-numbers/secondary-accession-number') dt,
XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/secondary-accession-number'
PASSING dt.secondary_accession_number COLUMNS
secondary_accession_number VARCHAR2(20) PATH '/') sa
ORDER BY dt.drugbank_id
;
PHARMA@scidev> select drugbank_id, secondary_accession_number from secondary_accession_number_vw where rownum <= 5;
DRUGBANK_ID SECONDARY_ACCESSION_
-------------------- --------------------
DB00001 BTD00024
DB00001 BIOD00024
DB00002 BTD00071
DB00002 BIOD00071
DB00003 BTD00001
5 rows selected.
Elapsed: 00:00:00.34
This code generates the error
ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (drugs)
SELECT OBJECT_VALUE
FROM drugs_xmltype
WHERE XMLExists('/drugs/drug[drugbank-id="DB00001"]' PASSING OBJECT_VALUE);
-- ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (drug)
SELECT OBJECT_VALUE
FROM drugs_xmltype
WHERE XMLExists('/drug[drugbank-id="DB00001"]' PASSING OBJECT_VALUE);
-- ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (drug)
SELECT OBJECT_VALUE
FROM drugs_xmltype
WHERE XMLExists('drug[drugbank-id="DB00001"]' PASSING OBJECT_VALUE);
-- ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (drugs)
SELECT XMLQuery('/drugs/drug/drugbank-id'
PASSING OBJECT_VALUE RETURNING CONTENT)
FROM drugs_xmltype;
-- ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (XMLDATA)
SELECT XMLQuery('/XMLDATA/drug/drugbank-id'
PASSING OBJECT_VALUE RETURNING CONTENT)
FROM drugs_xmltype;
-- ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (drugs)
SELECT XMLCast(XMLQuery('/drugs/drug/drugbank-id' PASSING OBJECT_VALUE RETURNING CONTENT)
AS VARCHAR2(10)) "DBID"
FROM drugs_xmltype;
-- ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (drug)
SELECT XMLCast(XMLQuery('/drug/drugbank-id' PASSING OBJECT_VALUE RETURNING CONTENT)
AS VARCHAR2(10)) "DBID"
FROM drugs_xmltype;
-- here the XMLTable works (returns the whole XML file), but the XMLExists fails
-- ORA-00936: missing expression on the DEFAULT, which is a reserved word
-- I assume that XMLNamespaces is not allowed for XMLExists.
SELECT dbid.COLUMN_VALUE
FROM drugs_xmltype dx, XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/drugs/drug'
PASSING dx.OBJECT_VALUE) dbid
WHERE XMLEXISTS(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '$d/drugs/drug[drugbank-id="DB00014"]' PASSING OBJECT_VALUE as "d");
;