11 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 XMLType method transform(). This process conforms to the W3C XSL Transformations 1.0 Recommendation.

XMLType instance can be transformed in the following ways:

  • Using Oracle SQL function XMLtransform or XMLType method transform() in the database.

  • Using Oracle XML Developer's Kit transformation options in the middle tier, such as XSLT Processor for Java.

See Also:

SQL Function XMLTRANSFORM and XMLType Method TRANSFORM()

Figure 11-1 shows the syntax of Oracle 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 11-1 XMLTRANSFORM Syntax

Description of Figure 11-1 follows
Description of "Figure 11-1 XMLTRANSFORM Syntax"

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

Figure 11-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.

See Also:

Figure 1-1, "XMLType Storage and Oracle XML DB Repository" in Chapter 1, "Introduction to Oracle XML DB"

Figure 11-2 Using XMLTRANSFORM

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

XMLTRANSFORM and XMLType.transform(): Examples

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

Example 11-1 sets up an XML schema and tables that are 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.

Example 11-1 Registering an XML Schema and Inserting XML Data

BEGIN
  -- Delete the schema, if it already exists.
  DBMS_XMLSCHEMA.deleteSchema('http://www.example.com/schemas/ipo.xsd',4);
END;
/
BEGIN
  -- Register the schema
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.example.com/schemas/ipo.xsd',
    SCHEMADOC => '<schema targetNamespace="http://www.example.com/IPO"
                          xmlns="http://www.w3.org/2001/XMLSchema"
                          xmlns:ipo="http://www.example.com/IPO">
                    <!-- annotation>
                      <documentation>
                       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>',
    LOCAL     => TRUE,
    GENTYPES  => TRUE);
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 11-2 shows how to retrieve a style sheet using SQL function XMLtransform and DBURIType. See Chapter 20, "Accessing Data Through URIs" for information about DBURIType.

Example 11-2 Retrieving a Style Sheet using XMLTRANSFORM and DBURITYPE

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 XMLSerialize(DOCUMENT XMLtransform(x.xmlcol, y.stylesheet)
                    AS varchar2(1000))
  AS result FROM po_tab x, stylesheet_tab y WHERE y.id = 1;

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 11-3 illustrates the use of a stored style sheet to transform XMLType instances. Unlike Example 11-2, Example 11-3 uses a scalar subquery to retrieve the stored style sheet.

Example 11-3 Retrieving a Style Sheet using XMLTRANSFORM and a Subquery

SELECT XMLSerialize(DOCUMENT
                    XMLtransform(
                      x.xmlcol,
                      (SELECT stylesheet FROM stylesheet_tab WHERE id = 1))
                    AS VARCHAR2(1000))
  AS result FROM po_tab x;

Example 11-4 uses XMLType method transform() to transform an XMLType instance using a transient style sheet.

Example 11-4 Using Method TRANSFORM() with a Transient Style Sheet

SELECT XMLSerialize(
         DOCUMENT
         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>'))
         AS varchar2(1000))
  FROM po_tab x;

Validating XMLType Instances

Often, besides knowing whether a particular XML document is well-formed, you need to know whether it conforms to a given XML schema, that is, whether it is valid with respect to that XML schema.

XML schema-based data that is stored as binary XML it is automatically validated fully whenever it is inserted or updated. This validation does not require building a DOM. It is done using streaming, which is efficient and minimizes memory use.

For XMLType data that is stored object-relationally, full validation requires building a DOM, which can be costly in terms of memory management. For this reason, Oracle XML DB does not automatically perform full validation when you insert or update data that is stored object-relationally.

However, in the process of decomposing XML data to store it object-relationally, Oracle XML DB does automatically perform partial validation, to ensure that the structure of the XML document conforms to the SQL data type definitions that were derived from the XML schema.

If you require full validation for XMLType data stored object-relationally, then consider validating on the client before inserting the data into the database or updating it.

You can use the following to perform full validation and manipulate the recorded validation status of XML documents:

  • Oracle SQL function XMLIsValid and XMLType method IsSchemaValid() – Run the validation process unconditionally. Do not record any validation status. Return:

    • 1 if the document is determined to be valid.

    • 0 if the document is determined to be invalid or the validity of the document cannot be determined.

  • XMLType method SchemaValidate() – Runs the validation process if the validation status is 0, which it is by default. Sets the validation status to 1 if the document is determined to be valid. (Otherwise, the status remains 0.)

  • XMLType method isSchemaValidated() returns the recorded validation status of an XMLType instance.

  • XMLType method setSchemaValidated() sets (records) the validation status of an XMLType instance.

Note that the validation status indicates knowledge of validity, as follows:

  • 1 means that the document is known to be valid.

  • 0 means that validity of the document is unknown. The document might have been shown to be invalid during a validation check, but that invalidity is not recorded. A recorded validation status of 0 indicates only a lack of knowledge about the document's validity.

See Also:

Validating XML Data Stored as XMLType: Examples

The examples in this section illustrate how to use Oracle SQL function XMLIsValid and XMLType methods isSchemaValid() and schemaValidate() to validate XML data being stored as XMLType in Oracle XML DB.

Example 11-5 and Example 11-6 show how to validate an XML instance against an XML schema using PL/SQL method isSchemaValid().

Example 11-5 Validating XML using Method ISSCHEMAVALID() in SQL

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

Example 11-6 Validating XML using Method ISSCHEMAVALID() in PL/SQL

DECLARE
  xml_instance XMLType;
BEGIN
  SELECT x.xmlcol INTO xml_instance FROM po_tab x WHERE id = 1;
  IF xml_instance.isSchemaValid('http://www.example.com/schemas/ipo.xsd') = 0
    THEN raise_application_error(-20500, 'Invalid Instance');
    ELSE DBMS_OUTPUT.put_line('Instance is valid');
  END IF;
END;
/
Instance is valid
 
PL/SQL procedure successfully completed.

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. Example 11-7 illustrates this.

Example 11-7 Validating XML using Method SCHEMAVALIDATE() within Triggers

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 11-8 uses Oracle SQL function XMLIsValid to do the following:

  • Verify that the XMLType instance conforms to the specified XML schema

  • Ensure that the incoming XML documents are valid by using CHECK constraints

Example 11-8 Checking XML Validity using XMLISVALID within CHECK Constraints

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 section "Validating XMLType Instances" facilitate validation checking. Of these, schemaValidate is the only one that raises errors that indicate why validation has failed.