<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_schemasSELECT 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 raisedDECLARE
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 typeOur 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
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 ignoredAlternatively 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.
 
|
 
|
Hi ,
ReplyDeleteWe have some questions on this.
we have 1,00,000 records like below.
Fred
pass123
</userdetails
We can not add the 1,00,000 records in the oracle xml type function. so we have xml file for 1,00,000 recrods. How to validate these 1,00,000 records with respect XSD.
Please let us know how to validate.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9525415800346730520
ReplyDelete