6
Transforming and Validating XMLType Data

This chapter describes the SQL functions and XMLType APIs for transforming XMLType data using XSLT stylesheets. It also explains the various functions and APIs available for validating the XMLType instance against an XML schema. It contains the following sections:

• Transforming XMLType Instances
• XMLTransform() Examples
• Validating XMLType Instances
• Validating XML Data Stored as XMLType: Examples

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 Oracle9i database, can be (formatted) transformed into HTML, XML, and other mark-up languages, using XSL stylesheets and XMLType's function, transform(). This process conforms to W3C's XSLT 1.0 recommendation.

XMLType instance can be transformed in the following ways:

• Using the XMLTransform() SQL function (or the transform() member function of XMLType) in the database
• Using XDK transformation options in the middle tier, such as XSLT Processor for Java.

  See Also: Chapter 26, "Oracle XML DB Basic Demo", the section, "8.3 Transforming PurchaseOrder Using XSLT" Appendix D, "XSLT Primer" Oracle9i XML Developer's Kits Guide - XDK, the chapter on XSQL Pages Publishing Framework

XMLTransform() and XMLType.transform()

Figure 6-1 shows the XMLTransform() syntax. The XMLTransform() function takes as arguments an XMLType instance and an XSL stylesheet (which is itself an XMLType instance). It applies the stylesheet to the instance and returns an XMLType instance.

Note: You can also use the syntax, XMLTYPE.transform(). This is the same as XMLtransform().

Figure 6-2 shows how XMLTransform() transforms the XML document by using the XSL stylesheet passed in. It returns the processed output as XML, HTML, and so on, as specified by the XSL stylesheet. You typically need to use XMLTransform() when retrieving or generating XML documents stored as XMLType in Oracle9i database.

See Also: Figure 1-1, "Oracle XML DB Architecture: XMLType Storage and Repository" in Chapter 1, "Introducing Oracle XML DB"

Figure 6-1 XMLTransform() Syntax

Text description of the illustration XMLTransform.gif

Figure 6-2 Using XMLTransform()

Text description of the illustration adxdb008.gif

XMLTransform() Examples

Use the following code to set up the XML schema and tables needed to run the examples in this chapter:

--register schema
begin
dbms_xmlschema.deleteSchema('http://www.example.com/schemas/ipo.xsd',4);
end;
/
begin
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="+{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 xsi:type="ipo:Address">
        <name>Helen Zoe</name>
        <street>121 Broadway</street>
        <city>Cardiff</city>
        <state>Wales</state>
        <country>UK</country>
        <zip>CF2 1QJ</zip>
    </shipTo>
    <billTo xsi:type="ipo:Address">
        <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>'));

The following examples illustrate how to use XMLTransform() to transform XML data stored as XMLType to HTML, XML, or other languages.

Example 6-1 Transforming an XMLType Instance Using XMLTransform() and DBUriType to Get the XSL Stylesheet

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('/SCOTT/STYLESHEET_TAB/ROW[ID = 
1]/STYLESHEET/text()').getXML()).getStringVal() 
  AS result
   FROM po_tab x; 
 
-- The preceding statement produces the following output:
-- 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 6-2 Transforming an XMLType Instance Using XMLTransform() and a Subquery SELECT to Retrieve the XSL Stylesheet

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

SELECT XMLTransform(x.xmlcol,
    (select stylesheet from stylesheet_tab where id = 1)).getStringVal() 
     AS result
   FROM po_tab x;

Example 6-3 Transforming XMLType Instances Using Transient Stylesheets and XMLTransform()

This example describes how you can transform XMLType instances using a transient 
stylesheet:

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, Oracle9i does check to make sure that XMLType instances are well-formed. In addition, for schema-based XMLType instances, Oracle9i performs few basic validation checks. Since 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 "validated" status of XML documents, the following functions and SQL operator are provided:

XMLIsValid()

XMLIsValid() is an SQL Operator. It 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 XMLType instance's own schema. If any of the arguments are specified to be NULL, then the result is NULL. If validation fails, 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

schemaValidate is a member procedure. It validates the 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, the document's status is changed to VALIDATED.

Syntax

MEMBER PROCEDURE schemaValidate

isSchemaValidated()

isSchemaValidated() is a member function. It returns the validation status of the XMLType instance and tells if a schema-based instance has been actually 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()

setSchemaValidated() is a member function. It 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 for this parameter is 1.

isSchemaValid()

isSchemaValid() is a member function. It 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 XMLType instance's own schema. If the validation fails, 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 a 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 isSchemaValid(), setSchemaValidated(), and isSchemaValidated() to validate XML data being stored as XMLType in Oracle XML DB.

Example 6-4 Using isSchemaValid()

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

Example 6-5 Validating XML Using 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 6-6 Using schemaValidate() Within Triggers

The schemaValidate() method of XMLType 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.sys_nc_rowinfo$;
   xmltype.schemavalidate(newxml);
END;
/

Example 6-7 Using XMLIsSchemaValid() Within CHECK Constraints

This example uses XMLIsValid() to:

• Verify that the XMLType instance conforms to the specified XML schema
• Ensure that the incoming XML documents are valid by using CHECK constraints

  DROP TABLE po_tab;
  CREATE TABLE po_tab OF XMLTYPe 
     (CHECK (XMLIsValid(sys_nc_rowinfo$) = 1))
     XMLSchema "http://www.example.com/schemas/ipo.xsd" element "purchaseOrder";
  

  Note: The validation functions and operators described in the preceding section, facilitate validation checking. Of these, isSchemaValid() is the only one that throws errors that include why the validation has failed.