8 Transforming and Validating XMLType Data

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 function, transform(). This process conforms to the W3C XSL Transformations 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.

  
  

  Note: The PL/SQL package DBMS_XSLPROCESSOR provides a convenient and efficient way of applying a single style sheet to multiple documents. The performance of this package will be better than transform() because the style sheet will be parsed only once.

  
  
  
  

  See Also: Chapter 3, " Using Oracle XML DB", the section, "XSL Transformation" "PL/SQL XSLT Processor for XMLType (DBMS_XSLPROCESSOR)" Appendix D, " XSLT Primer " Oracle XML Developer's Kit Programmer's Guide, the chapter on XSQL Pages Publishing Framework

  
  

XMLTransform() and XMLType.transform()

Figure 8-1 shows the XMLTransform() syntax. The XMLTransform() function takes as arguments an XMLType instance and an XSLT style sheet (which is itself an XMLType instance). It applies the style sheet to the instance and returns an XMLType instance.

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

Figure 8-2 shows how XMLTransform() transforms the XML document by using the XSLT style sheet passed in. It returns the processed output as XML, HTML, and so on, as specified by the XSLT style sheet. You typically are required to use XMLTransform() when retrieving or generating XML documents stored as XMLType in the database.

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

Figure 8-1 XMLTransform() Syntax

Description of the illustration XMLTransform.gif

Figure 8-2 Using XMLTransform()

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. (The call to deleteSchema is to ensure that there is no existing schema before creating one. If no such schema exists, then deleteSchema produces an error.)

CONNECT scott/tiger

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>'));

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

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; 
 
-- 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>

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

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 "validated" status of XML documents, the following functions and SQL operator are provided:

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

MEMBER PROCEDURE schemaValidate

MEMBER FUNCTION isSchemaValidated return NUMBER deterministic

MEMBER PROCEDURE setSchemaValidated(flag IN BINARY_INTEGER := 1)

member function isSchemaValid(schurl IN VARCHAR2 := NULL, elem IN VARCHAR2 :=
    NULL) return NUMBER deterministic

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.

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

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;

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;
/

• 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(object_value) = 1))
   XMLSchema "http://www.example.com/schemas/ipo.xsd" element "purchaseOrder";