The xml below
<userdetails>
<username>Fred</username>
<password>pass123</password>
</userdetails>
can be represented by the following XSD
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="userdetails">
<xs:complexType>
<xs:sequence>
<xs:element name="username" type="xs:string"/>
<xs:element name="password" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
The xsd can be registered with the oracle database by making a call to dbms_xmlschema.registerSchema.
In the example below we have named this xsd as xsd1.xsd
BEGIN
dbms_xmlschema.registerSchema(schemaURL => 'xsd1.xsd',
schemaDoc => '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="userdetails">
<xs:complexType>
<xs:sequence>
<xs:element name="username" type="xs:string"/>
<xs:element name="password" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>');
END;
/
The registered schema can be viewed in the database by accessing the data dictionary all_xml_schemas
SELECT schema
FROM all_xml_schemas
WHERE schema_url = 'xsd1.xsd'
/
SCHEMA
----------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="295" oraxdb:schemaURL="xsd1.xsd" oraxdb:schemaOwner="MYSCHEMA" oraxdb:numProps="3">
<xs:element name="userdetails" oraxdb:propNumber="2515" oraxdb:global="true" oraxdb:SQLName="userdetails"
oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA" oraxdb:memType="258" oraxdb:defaultTable="userdetails194_TAB" oraxdb:defaultTableSchema="MYSCHEMA">
<xs:complexType oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA">
<xs:sequence>
<xs:element name="username" type="xs:string" oraxdb:propNumber="2513" oraxdb:global="false"
oraxdb:SQLName="username" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
<xs:element name="password" type="xs:string" oraxdb:propNumber="2514" oraxdb:global="false"
oraxdb:SQLName="password" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
To validate the xml shown below
<userdetails>
<username>Fred</username>
<password>pass123</password>
</userdetails>
we need to add a reference to the xsd we registered earlier and called xsd1.xsd.
To the outer node add the reference xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
We can then make a call to XMLTYPE.schemaValidate to validate the xml against the schema
DECLARE
l_xml XMLTYPE ;
BEGIN
l_xml := XMLTYPE('<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<username>Fred</username>
<password>pass123</password>
</userdetails>');
XMLTYPE.schemaValidate(l_xml);
END;
/
PL/SQL procedure successfully completed.
The successful execution of the pl/sql indicates the xml was valid. Adding an element that is not defined in the xsd (element newelement) will fail schema validation resulting in an exception being raised
DECLARE
l_xml XMLTYPE ;
BEGIN
l_xml := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<username>Fred</username>
<password>pass123</password>
<newelement>dummy value</newelement>
</userdetails>');
XMLTYPE.schemaValidate(l_xml);
END;
/
DECLARE
*
ERROR at line 1:
ORA-30937: No schema definition for 'newelement' (namespace '##local') in parent '/userdetails'
ORA-06512: at "SYS.XMLTYPE", line 345
ORA-06512: at line 14
If we don't have an XSD but already have a type representation in the database Oracle allows us to generate an XSD based
upon that type
Our original XML
<userdetails>
<username>Fred</username>
<password>pass123</password>
</userdetails>
can be represented in the database by the following type
CREATE TYPE user_details_typ AS OBJECT
(
"username" VARCHAR2(20) ,
"password" VARCHAR2(30)
)
/
Type created.
Note, the attributes username and password have been enclosed in double quotes to preserve their case.
To generate the XSD a call to dbms_xmlschema.generateschema is required.
SELECT DBMS_XMLSCHEMA.generateschema('MYSCHEMA', 'USER_DETAILS_TYP', 'userdetails') FROM DUAL ;
This will result in the following XSD being created
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">
<xsd:element name="userdetails" type="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA"/>
<xsd:complexType name="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA" xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="username" xdb:SQLName="username" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="password" xdb:SQLName="password" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="30"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
The parameters passed into dbms_xmlschema.generateschema were:
- 'MYSCHEMA' - the name of the schema the object (user_details_typ) resides
- 'USER_DETAILS_TYP' - the name of the object
- 'userdetails' - the name of the top node
An issue with creating types where the attributes are enclosed in double quotes such as
CREATE TYPE user_details_typ AS OBJECT
(
"username" VARCHAR2(20) ,
"password" VARCHAR2(30)
)
/
is that in order to reference the type in pl/sql the attributes have to be enclosed in double quotes to preserve their case
DECLARE
l_user_details user_details_typ ;
BEGIN
l_user_details := user_details_typ (NULL, NULL) ;
l_user_details."username" := 'Fred' ;
END ;
/
PL/SQL procedure successfully completed.
Failing to use double quotes will result in an error
DECLARE
l_user_details user_details_typ ;
BEGIN
l_user_details := user_details_typ (NULL, NULL) ;
l_user_details.username := 'Fred' ;
END ;
/
ERROR at line 8:
ORA-06550: line 8, column 19:
PLS-00302: component 'USERNAME' must be declared
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored
Alternatively the object could be created without double quotes and the resulting xml could be tranformed using a style sheet.
The code below transforms xml with uppercase attribute names to xml with lower case attribute names using the xml method transform.
DECLARE
l_xml XMLTYPE ;
l_xslt XMLTYPE ;
l_transformed_xml XMLTYPE ;
BEGIN
-- the original xml
l_xml := XMLTYPE ('<USERDETAILS>
<USERNAME>Fred</USERNAME>
<PASSWORD>pass123</PASSWORD>
</USERDETAILS>') ;
dbms_output.put_line ('Original xml' || CHR(10)||'---------------------------') ;
dbms_output.put_line (l_xml.getclobval) ;
-- stylesheet to transform all the element names to lower case
l_xslt := XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" encoding="UTF-8" version="1.0" />
<xsl:template match="*">
<xsl:variable name="vElement"
select="translate(name(), ''ABCDEFGHIJKLMNOPQRSTUVWXYZ'',''abcdefghijklmnopqrstuvwxyz'')" />
<xsl:element name="{$vElement}">
<xsl:apply-templates />
</xsl:element>
</xsl:template>
</xsl:stylesheet>') ;
-- apply the xslt to the original xml
l_transformed_xml := l_xml.transform (l_xslt) ;
dbms_output.put_line ('Transformed xml'|| CHR(10)||'---------------------------') ;
dbms_output.put_line (l_transformed_xml.getclobval) ;
END ;
/
Original xml
---------------------------
<USERDETAILS>
<USERNAME>Fred</USERNAME>
<PASSWORD>pass123</PASSWORD>
</USERDETAILS>
Transformed xml
---------------------------
<userdetails>
<username>Fred</username>
<password>pass123</password>
</userdetails>
PL/SQL procedure successfully completed.
