DBA Hub

📋Steps in this guide1/3

Validate an XML Document Against an XML Schema (XSD) in an Oracle Database

This article gives some simple examples of validating XML documents against a XML Schema (XSD) in an Oracle database.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

Register an XML Schema

First we need to register the schema using the procedure. This has a number of overloads, allowing you to specify the XML schema using a , , , , or types. The parameter list is quite important. By default the procedure will create database types and object tables, allowing documents to be shredded into object tables. For complex XSD documents you might get thousands of objects created in your database schema. For a basic document validation this is unnecessary and messy, so check you are using the correct settings. We can check the schema details using the view. With the schema registered, we can now validate XML documents against it. The procedure can be used to un-register the schema. The parameter allows you to drop any dependent objects.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
DECLARE
  l_schema  CLOB;
BEGIN

  l_schema := '<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- definition of simple elements -->
<xs:element name="orderperson" type="xs:string"/>
<xs:element name="name" type="xs:string"/>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
<xs:element name="title" type="xs:string"/>
<xs:element name="note" type="xs:string"/>
<xs:element name="quantity" type="xs:positiveInteger"/>
<xs:element name="price" type="xs:decimal"/>

<!-- definition of attributes -->
<xs:attribute name="orderid" type="xs:string"/>

<!-- definition of complex elements -->
<xs:element name="shipto">
  <xs:complexType>
    <xs:sequence>
      <xs:element ref="name"/>
      <xs:element ref="address"/>
      <xs:element ref="city"/>
      <xs:element ref="country"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

<xs:element name="item">
  <xs:complexType>
    <xs:sequence>
      <xs:element ref="title"/>
      <xs:element ref="note" minOccurs="0"/>
      <xs:element ref="quantity"/>
      <xs:element ref="price"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

<xs:element name="shiporder">
  <xs:complexType>
    <xs:sequence>
      <xs:element ref="orderperson"/>
      <xs:element ref="shipto"/>
      <xs:element ref="item" maxOccurs="unbounded"/>
    </xs:sequence>
    <xs:attribute ref="orderid" use="required"/>
  </xs:complexType>
</xs:element>

</xs:schema>';

   DBMS_XMLSCHEMA.registerSchema(schemaurl       => 'my_schema.xsd', 
                                 schemadoc       => l_schema,
                                 local           => TRUE,
                                 gentypes        => FALSE,
                                 gentables       => FALSE,
                                 enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none); 

END;
/

SELECT schema_url FROM user_xml_schemas;

SCHEMA_URL
--------------------------------------------------------------------------------
my_schema.xsd

SQL>

BEGIN 
  DBMS_XMLSCHEMA.deleteschema(
    schemaurl     => 'my_schema.xsd',
    delete_option => DBMS_XMLSCHEMA.delete_cascade_force); 
END; 
/
2

Validate XML Document (SCHEMAVALIDATE)

In the following PL/SQL example, we create an from some XML in a , then call the member procedure to test the XML against the XML schema. To see an example of a validation failure, rename the "name" tag to "name1". This is not part of the XML schema, so it will fail the validation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
DECLARE
  l_xml      CLOB;
  l_xmltype  XMLTYPE;
BEGIN

  l_xml := '<?xml version="1.0" encoding="UTF-8"?>
<shiporder orderid="889923">
  <orderperson>John Smith</orderperson>
  <shipto>
    <name>Ola Nordmann</name>
    <address>Langgt 23</address>
    <city>4000 Stavanger</city>
    <country>Norway</country>
  </shipto>
  <item>
    <title>Empire Burlesque</title>
    <note>Special Edition</note>
    <quantity>1</quantity>
    <price>10.90</price>
  </item>
  <item>
    <title>Hide your heart</title>
    <quantity>1</quantity>
    <price>9.90</price>
  </item>
</shiporder>';

  l_xmltype := XMLTYPE(l_xml, 'my_schema.xsd');
  l_xmltype.schemavalidate;

END;
/

PL/SQL procedure successfully completed.

SQL>

DECLARE
  l_xml      CLOB;
  l_xmltype  XMLTYPE;
BEGIN

  l_xml := '<?xml version="1.0" encoding="UTF-8"?>
<shiporder orderid="889923">
  <orderperson>John Smith</orderperson>
  <shipto>
    <
name1
>Ola Nordmann</
name1
>
    <address>Langgt 23</address>
    <city>4000 Stavanger</city>
    <country>Norway</country>
  </shipto>
  <item>
    <title>Empire Burlesque</title>
    <note>Special Edition</note>
    <quantity>1</quantity>
    <price>10.90</price>
  </item>
  <item>
    <title>Hide your heart</title>
    <quantity>1</quantity>
    <price>9.90</price>
  </item>
</shiporder>';

  l_xmltype := XMLTYPE(l_xml, 'my_schema.xsd');
  l_xmltype.schemavalidate;

END;
/

DECLARE
*
ERROR at line 1:
ORA-30937: No schema definition for 'name1' (namespace '##local') in parent
'/shiporder/shipto'
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 29


SQL>
3

Validate XML Document (XMLISVALID)

An alternative is to use the function. Create a table to hold the XML we used in the previous tests. Now we can use the function in SQL to test against the registered XML Schema. It returns a "1" if the XML is valid, and "0" if it isn't. For more information see: - DBMS_XMLSCHEMA - XMLTYPE - XMLISVALID - An XSD Example - All XML Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
CREATE TABLE t1 (
  id NUMBER,
  xml XMLTYPE
);

INSERT INTO t1 VALUES (1, '<?xml version="1.0" encoding="UTF-8"?>
<shiporder orderid="889923">
  <orderperson>John Smith</orderperson>
  <shipto>
    <name>Ola Nordmann</name>
    <address>Langgt 23</address>
    <city>4000 Stavanger</city>
    <country>Norway</country>
  </shipto>
  <item>
    <title>Empire Burlesque</title>
    <note>Special Edition</note>
    <quantity>1</quantity>
    <price>10.90</price>
  </item>
  <item>
    <title>Hide your heart</title>
    <quantity>1</quantity>
    <price>9.90</price>
  </item>
</shiporder>');


INSERT INTO t1 VALUES (2, '<?xml version="1.0" encoding="UTF-8"?>
<shiporder orderid="889923">
  <orderperson>John Smith</orderperson>
  <shipto>
    <name1>Ola Nordmann</name1>
    <address>Langgt 23</address>
    <city>4000 Stavanger</city>
    <country>Norway</country>
  </shipto>
  <item>
    <title>Empire Burlesque</title>
    <note>Special Edition</note>
    <quantity>1</quantity>
    <price>10.90</price>
  </item>
  <item>
    <title>Hide your heart</title>
    <quantity>1</quantity>
    <price>9.90</price>
  </item>
</shiporder>');

COMMIT;

SELECT id,
       XMLISVALID(xml, 'my_schema.xsd') AS is_valid
FROM   t1;

        ID   IS_VALID
---------- ----------
         1          1
         2          0

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!