Skip Headers
Oracle® XML DB Developer's Guide
11g Release 1 (11.1)

B28369-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Transforming and Validating XMLType Data

This chapter describes the SQL functions and XMLType APIs for transforming XMLType data using XSLT style sheets. It also explains the various functions and APIs available for validating the XMLType instance against an XML schema.

This chapter contains these topics:

Transforming XMLType Instances

XML documents have structure but no format. To add format to the XML documents you can use Extensible Stylesheet Language (XSL). XSL provides a way of displaying XML semantics. It can map XML elements into other formatting or mark-up languages such as HTML.

In Oracle XML DB, XMLType instances or XML data stored in XMLType tables, columns, or views in Oracle Database, can be (formatted) transformed into HTML, XML, and other mark-up languages, using XSL style sheets and the XMLType method transform(). This process conforms to the W3C XSL Transformations 1.0 Recommendation.

XMLType instance can be transformed in the following ways:

SQL Function XMLTRANSFORM and XMLType Method transform()

Figure 10-1 shows the syntax of SQL function XMLtransform. This function takes as arguments an XMLType instance and an XSLT style sheet. The style sheet can be an XMLType instance or a VARCHAR2 string literal. It applies the style sheet to the instance and returns an XMLType instance.

Figure 10-1 XMLtransform Syntax

Description of Figure 10-1 follows
Description of "Figure 10-1 XMLtransform Syntax"

You can alternatively use XMLType method transform() as an alternative to SQL function XMLtransform; it has the same functionality.

Figure 10-2 shows how XMLtransform transforms an XML document by using an XSLT style sheet. It returns the processed output as XML, HTML, and so on, as specified by the XSLT style sheet. You typically use XMLtransform when retrieving or generating XML documents stored as XMLType in the database.

Figure 10-2 Using XMLTRANSFORM

Description of Figure 10-2 follows
Description of "Figure 10-2 Using XMLTRANSFORM"

XMLTRANSFORM and XMLType.transform(): Examples

The examples in this section illustrate how to use SQL function XMLtransform and XMLType method transform() to transform XML data stored as XMLType to various formats.

Example 10-1 Registering XML Schema and Inserting XML Data

This example sets up the XML schema and tables needed to run other examples in this chapter. (The call to deleteSchema here ensures that there is no existing XML schema before creating one. If no such schema exists, then deleteSchema produces an error.)

BEGIN
  -- Delete the schema, if it already exists; otherwise, this produces an error.
  DBMS_XMLSCHEMA.deleteSchema('http://www.example.com/schemas/ipo.xsd',4);
END;
/
BEGIN
-- Register the schema
DBMS_XMLSCHEMA.registerSchema('http://www.example.com/schemas/ipo.xsd',
'<schema targetNamespace="http://www.example.com/IPO"
         xmlns="http://www.w3.org/2001/XMLSchema"
         xmlns:ipo="http://www.example.com/IPO">
  <!-- annotation>
   <documentation xml:lang="en">
    International Purchase order schema for Example.com
    Copyright 2000 Example.com. All rights reserved.
   </documentation>
  </annotation -->
  <element name="purchaseOrder" type="ipo:PurchaseOrderType"/>
  <element name="comment" type="string"/>
  <complexType name="PurchaseOrderType">
   <sequence>
    <element name="shipTo"     type="ipo:Address"/>
    <element name="billTo"     type="ipo:Address"/>
    <element ref="ipo:comment" minOccurs="0"/>
    <element name="items"      type="ipo:Items"/>
   </sequence>
   <attribute name="orderDate" type="date"/>
  </complexType>
  <complexType name="Items">
   <sequence>
    <element name="item" minOccurs="0" maxOccurs="unbounded">
     <complexType>
      <sequence>
       <element name="productName" type="string"/>
       <element name="quantity">
        <simpleType>
         <restriction base="positiveInteger">
          <maxExclusive value="100"/>
         </restriction>
        </simpleType>
       </element>
       <element name="USPrice"    type="decimal"/>
       <element ref="ipo:comment" minOccurs="0"/>
       <element name="shipDate"   type="date" minOccurs="0"/>
      </sequence>
      <attribute name="partNum" type="ipo:SKU" use="required"/>
     </complexType>
    </element>
   </sequence>
  </complexType>
  <complexType name="Address">
   <sequence>
    <element name="name"    type="string"/>
    <element name="street"  type="string"/>
    <element name="city"    type="string"/>
    <element name="state"   type="string"/>
    <element name="country" type="string"/>
    <element name="zip"     type="string"/>
   </sequence>
  </complexType>
  <simpleType name="SKU">
   <restriction base="string">
    <pattern value="[0-9]{3}-[A-Z]{2}"/>
   </restriction>
  </simpleType>
</schema>',
   TRUE, TRUE, FALSE);
END;
/

-- Create table to hold XML instance documents
DROP TABLE po_tab;

CREATE TABLE po_tab (id NUMBER, xmlcol XMLType) 
 XMLType COLUMN xmlcol
 XMLSCHEMA "http://www.example.com/schemas/ipo.xsd"
 ELEMENT "purchaseOrder";

INSERT INTO po_tab 
  VALUES(1, XMLType(
              '<?xml version="1.0"?>
               <ipo:purchaseOrder
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns:ipo="http://www.example.com/IPO"
                 xsi:schemaLocation="http://www.example.com/IPO
                                     http://www.example.com/schemas/ipo.xsd"
                 orderDate="1999-12-01">
                 <shipTo>
                   <name>Helen Zoe</name>
                   <street>121 Broadway</street>
                   <city>Cardiff</city>
                   <state>Wales</state>
                   <country>UK</country>
                   <zip>CF2 1QJ</zip>
                 </shipTo>
                 <billTo>
                   <name>Robert Smith</name>
                   <street>8 Oak Avenue</street>
                   <city>Old Town</city>
                   <state>CA</state>
                   <country>US</country>
                   <zip>95819</zip>
                 </billTo>
                 <items>
                   <item partNum="833-AA">
                     <productName>Lapis necklace</productName>
                     <quantity>1</quantity>
                     <USPrice>99.95</USPrice>
                     <ipo:comment>Want this for the holidays!</ipo:comment>
                     <shipDate>1999-12-05</shipDate>
                   </item>
                 </items>
               </ipo:purchaseOrder>'));

Example 10-2 Using XMLTRANSFORM and DBURITYPE to Retrieve a Style Sheet

DBURIType is described in Chapter 20, "Accessing Data Through URIs".

DROP TABLE stylesheet_tab;

CREATE TABLE stylesheet_tab(id NUMBER, stylesheet XMLType);

INSERT INTO stylesheet_tab 
  VALUES (1, 
          XMLType(
            '<?xml version="1.0" ?>
             <xsl:stylesheet version="1.0" 
                             xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
               <xsl:template match="*">
                 <td>
                   <xsl:choose>
                     <xsl:when test="count(child::*) > 1">
                       <xsl:call-template name="nested"/>
                     </xsl:when>
                     <xsl:otherwise>
                       <xsl:value-of select="name(.)"/>:<xsl:value-of 
                                                          select="text()"/>
                     </xsl:otherwise>
                   </xsl:choose>
                 </td>
               </xsl:template>
               <xsl:template match="*" name="nested" priority="-1" mode="nested2">
                 <b>
                   <!-- xsl:value-of select="count(child::*)"/ -->
                   <xsl:choose>
                     <xsl:when test="count(child::*) > 1">
                       <xsl:value-of select="name(.)"/>:<xsl:apply-templates 
                                                          mode="nested2"/>
                     </xsl:when>
                     <xsl:otherwise>
                       <xsl:value-of select="name(.)"/>:<xsl:value-of 
                                                          select="text()"/>
                     </xsl:otherwise>
                   </xsl:choose>
                 </b>
               </xsl:template>
             </xsl:stylesheet>'));

SELECT 
  XMLtransform(x.xmlcol, 
               DBURIType('/XDB/STYLESHEET_TAB/ROW
                            [ID=1]/STYLESHEET/text()').getXML()).getStringVal()
  AS result
  FROM po_tab x; 
 

This produces the following output (pretty-printed here for readability):

RESULT
---------------------------------------------------------
<td>
  <b>ipo:purchaseOrder:
    <b>shipTo:
      <b>name:Helen Zoe</b>
      <b>street:100 Broadway</b>
      <b>city:Cardiff</b>
      <b>state:Wales</b>
      <b>country:UK</b>
      <b>zip:CF2 1QJ</b>
    </b>
    <b>billTo:
      <b>name:Robert Smith</b>
      <b>street:8 Oak Avenue</b>
      <b>city:Old Town</b>
      <b>state:CA</b>
      <b>country:US</b>
      <b>zip:95819</b>
    </b>
    <b>items:</b>
  </b>
</td>

Example 10-3 Using XMLTRANSFORM and a Subquery to Retrieve a Style Sheet

This example illustrates the use of a stored style sheet to transform XMLType instances. Unlike the previous example, this example uses a scalar subquery to retrieve the stored style sheet:

SELECT XMLtransform(x.xmlcol,
    (SELECT stylesheet FROM stylesheet_tab WHERE id = 1)).getStringVal()
     AS result
   FROM po_tab x;

Example 10-4 Using Method transform() with a Transient Style Sheet

This example uses XMLType method transform() to transform an XMLType instance using a transient style sheet:

SELECT x.xmlcol.transform(XMLType(
'<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="*">
    <td>
    <xsl:choose>
      <xsl:when test="count(child::*) > 1">
        <xsl:call-template name="nested"/>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="name(.)"/>:<xsl:value-of select="text()"/>
      </xsl:otherwise>
    </xsl:choose>
    </td>
</xsl:template>
<xsl:template match="*" name="nested" priority="-1" mode="nested2">
    <b>
    <!-- xsl:value-of select="count(child::*)"/ -->
    <xsl:choose>
      <xsl:when test="count(child::*) > 1">
        <xsl:value-of select="name(.)"/>:<xsl:apply-templates mode="nested2"/>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="name(.)"/>:<xsl:value-of select="text()"/>
      </xsl:otherwise>
    </xsl:choose>
    </b>
</xsl:template>
</xsl:stylesheet>'
)).getStringVal()
FROM po_tab x;

Validating XMLType Instances

Often, besides knowing that a particular XML document is well-formed, it is necessary to know if a particular document conforms to a specific XML schema, that is, is VALID with respect to a specific XML schema.

By default, the database checks to ensure that XMLType instances are well-formed. In addition, for schema-based XMLType instances, the database performs few basic validation checks. Because full XML schema validation (as specified by the W3C) is an expensive operation, when XMLType instances are constructed, stored, or retrieved, they are not also fully validated.

To validate and manipulate the validation status of XML documents, the following functions and procedures are provided:

XMLIsValid

PL/SQL function XMLIsValid checks if the input instance conforms to a specified XML schema. It does not change the validation status of the XML instance. If an XML schema URL is not specified and the XML document is schema-based, the conformance is checked against the own schema of the XMLType instance. If any of the arguments are specified to be NULL, then the result is NULL. If validation fails, then 0 is returned and no errors are reported explaining why the validation has failed.

Syntax

XMLIsValid (XMLType_inst [, schemaurl [, elem]])

Parameters:

  • XMLType_inst - The XMLType instance to be validated against the specified XML schema.

  • schurl - The URL of the XML schema against which to check conformance.

  • elem - Element of a specified schema, against which to validate. This is useful when we have a XML schema which defines more than one top level element, and we want to check conformance against a specific one of these elements.

schemaValidate

PL/SQL procedure schemaValidate validates an XML instance against its XML schema if it has not already been done. For non-schema-based documents an error is raised. If validation fails an error is raised otherwise, then the document status is changed to VALIDATED.

Syntax

MEMBER PROCEDURE schemaValidate

isSchemaValidated

PL/SQL function isSchemaValidated returns the validation status of the XMLType instance and tells if a schema-based instance has been validated against its schema. It returns 1 if the instance has been validated against the schema, 0 otherwise.

Syntax

MEMBER FUNCTION isSchemaValidated return NUMBER deterministic

setSchemaValidated

PL/SQL procedure setSchemaValidated sets the VALIDATION state of the input XML instance.

Syntax

MEMBER PROCEDURE setSchemaValidated(flag IN BINARY_INTEGER := 1)

Parameters:

flag, 0 - not validated; 1 - validated; The default value is 1.

isSchemaValid

PL/SQL function isSchemaValid checks if the input instance conforms to a specified XML schema. It does not change the validation status of the XML instance. If an XML schema URL is not specified and the XML document is schema-based, then the conformance is checked against the own schema of the XMLType instance. If the validation fails, then exceptions are thrown with the reason why the validation has failed.

Syntax

MEMBER FUNCTION isSchemaValid(schurl IN VARCHAR2 := NULL,
                              elem IN VARCHAR2 := NULL)
  RETURN NUMBER DETERMINISTIC

Parameters:

schurl - The URL of the XML schema against which to check conformance.

elem - Element of a specified schema, against which to validate. This is useful when we have an XML schema which defines more than one top level element, and we want to check conformance against a specific one of these elements.

Validating XML Data Stored as XMLType: Examples

The following examples illustrate how to use XMLType methods isSchemaValid() and schemaValidate(), as well as PL/SQL function XMLIsValid, to validate XML data being stored as XMLType in Oracle XML DB.

Example 10-5 Using Method isSchemaValid()

SELECT x.xmlcol.isSchemaValid('http://www.example.com/schemas/ipo.xsd',
               'purchaseOrder')
    FROM po_tab x;

Example 10-6 Validating XML Using Method isSchemaValid()

The following PL/SQL example validates an XML instance against XML schema PO.xsd:

DECLARE 
  xmldoc XMLType; 
BEGIN 
  -- Populate xmldoc (for example, by fetching from table). 
  -- Validate against XML schema 
  xmldoc.isSchemaValid('http://www.oracle.com/PO.xsd');
  IF xmldoc.isSchemaValid = 1 THEN  -- 
  ELSE -- 
  END IF; 
END;

Example 10-7 Using Method schemaValidate() Within Triggers

XMLType method schemaValidate() can be used within INSERT and UPDATE TRIGGERS to ensure that all instances stored in the table are validated against the XML schema:

DROP TABLE po_tab;

CREATE TABLE po_tab OF XMLType 
  XMLSCHEMA "http://www.example.com/schemas/ipo.xsd" ELEMENT "purchaseOrder";

CREATE TRIGGER emp_trig BEFORE INSERT OR UPDATE ON po_tab FOR EACH ROW

DECLARE 
  newxml XMLType;
BEGIn
  newxml := :new.OBJECT_VALUE;
  XMLTYPE.schemavalidate(newxml);
END;
/

Example 10-8 Using PL/SQL Function XMLISVALID Within CHECK Constraints

This example uses PL/SQL function XMLIsValid to:

DROP TABLE po_tab;

CREATE TABLE po_tab OF XMLType 
   (CHECK (XMLIsValid(OBJECT_VALUE) = 1))
   XMLSCHEMA "http://www.example.com/schemas/ipo.xsd" ELEMENT "purchaseOrder";

Note:

The validation functions and procedures described in the preceding section facilitate validation checking. Of these, isSchemaValid is the only one that throws errors that indicate why the validation has failed.