XMLTYPE Data Type

Oracle 9i introduced a new data type, XMLTYPE, to facilitate native handling of XML data in the database. XMLTYPE has built-in member functions that operate on XML content. For example, you can use XMLTYPE functions to create, extract, and index XML datastored in Oracle 9i database. XMLTYPE data type can be used as the data type of columns in tables and views. XMLTYPE data type uses following storage methods:

Large objects (LOBs). LOB storage maintains content accuracy to the original XML (white spaces and all). Here the XML documents are stored composed as whole documents like files. XML stored as a whole document in the database and retrieve it as a whole document. You do not need to perform piece-wise updates on XML documents.
Structured storage (tables and views). Structured storage maintains DOM (Document Object Model) fidelity.

ODAC can work with fields of XMLTYPE data type. For example,suppose we have following table with XMLTYPE field:

CREATE TABLE xml_tab(
  ID NUMBER(10),
  XMLField XMLTYPE
)

After creating TOraQuery and execution of the next SELECT statement

SELECT * FROM xml_tab

TOraXMLField object will be created for XMLTYPE type which holds retrieved XML document. XMLTYPE fields are cached in ODAC on opening a table. Update of XMLTYPE fields will be posted to the server after the execution of corresponding DML query.

You can update or append XMLTYPE fields to the table the following way:

Edit; // or Insert, Append;
TOraXMLField(FieldByName('X')).AsXML.AsString :=
  '<root> <node1>v1</node1> <node2 name1=''222''>v2</node2> <node1>v3</node1> </root>';
Post;

Or you can write:

Edit; // or Insert, Append;
TOraXMLField(FieldByName('X')).AsString :=
  '<root> <node1>v1</node1> <node2 name1=''222''>v2</node2> <node1>v3</node1> </root>';
Post;

You can use TOraXMLField.AsXML property to get XMLTYPE document as an TOraXML object. Using this object you can query XMLTYPE data and extract its portions by calling TOraXML.Exists and TOraXML.Extract functions. Both these functions use a subset of the W3C XPath recommendation to navigate through the document. XMLTYPE uses the built-in Oracle XML parser and processor, and that's why it provides better performance and scalability when used inside the server. TOraXML.Transform function takes in XMLTYPE instance and XSLT stylesheet. It applies the stylesheet to the XML document and returns a transformed XML instance.

You can treat the XMLTYPE as a parameter in DML statements as shown below.

Close;
SQL.Text := 'UPDATE xml_tab SET  XMLField = :XMLField WHERE ID = 101';
with Params[0].AsXML do begin
  OCISvcCtx := OraSession1.OCISvcCtx;
  AsString := '<test></test>';
end;
Execute;

Or you can create the XMLTYPE value from TOraLob.

with Params[0].AsXML do begin
  OraLob := TOraLob.Create(OraSession1.OCISvcCtx);
  try
    OraLob.CreateTemporary(ltClob);
    OraLob.Write(0, Length('<test_lob></test_lob>'), PChar('<test_lob></test_lob>'));
    OraLob.WriteLob;
    AllocObject(OraSession1.OCISvcCtx, OraLob);
  finally
    OraLob.Free;
  end;
end;
Execute;

XML Schema is a schema definition language written in XML. It can be used to describe the structure and other various semantics of conforming instance documents. When using Oracle XML DB, you must first register your XML schema. Than you can use the XML schema URLs while creating XMLTYPE tables, columns, and views. You can use XML schema to declare which elements and attributes can be used and what kinds of element nesting, and data types are allowed in the XML documents that are being stored or processed. For example, user can create XML document based on the following schema, create table with XMLTYPE field and initialize the field value.

declare
doc varchar2(1000) :=
'<schema targetNamespace="http://www.oracle.com/PO.xsd"
  xmlns:po="http://www.oracle.com/PO.xsd"
  xmlns="http://www.w3.org/2001/XMLSchema">
  <complexType name="PurchaseOrderType">
    <sequence>
      <element name="PONum" type="decimal"/>
      <element name="Company">
        <simpleType>
          <restriction base="string">
            <maxLength value="100"/>
          </restriction>
        </simpleType>
      </element>
    </sequence>
  </complexType>
  <element name="PurchaseOrder" type="po:PurchaseOrderType"/>
</schema>';
begin
  dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;
/
create table po_tab(
  id number,
  po sys.XMLTYPE
)

XMLTYPE column po
  XMLSCHEMA "http://www.oracle.com/PO.xsd"
  element "PurchaseOrder";

insert into po_tab values(
  1,
  XMLTYPE(
    '<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.oracle.com/PO.xsd">
      <PONum>1001</PONum>
      <Company>Oracle Corp</Company>
    </PurchaseOrder>'
  )
);

ODAC supports schema-based XML documents processing. You can open TOraQuery and get TOraXML object with schema-based document the same way as for LOB-based XMLTYPE. You can get value document calling TOraXMLField.AsString or TOraXML.AsString. Extract, Exists, Transform, GetSchema, Validate, and IsSchemaBased functions of TOraXML are available for this XML document type.

with OraQuery1 do begin
  RetDoc := TOraXML.Create();
  RetDoc.OCISvcCtx := OraSession1.OCISvcCtx;
  try
    with TOraXMLField(FieldByName('XMLTYPE')).AsXML do begin
      GetSchema(RetDoc, SchemaURL, RootElem);
      Str := RetDoc.AsString;
    end;
  finally
    RetDoc.Free;
  end;
end;

XMLTYPE Data Type

See Also