This appendix includes the definition and structure of RESOURCE_VIEW
and PATH_VIEW
and the Oracle XML DB-supplied XML schemas. It also includes a full listing of the purchase-order XML schemas used in various examples, and the C example for loading XML content into Oracle XML DB.
This appendix contains these topics:
Here is the listing for the Oracle XML DB supplied XML schema, XDBResource.xsd
, used to represent Oracle XML DB resources.
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://xmlns.oracle.com/xdb/XDBResource.xsd" version="1.0" elementFormDefault="qualified" xmlns:res="http://xmlns.oracle.com/xdb/XDBResource.xsd"> <simpleType name="OracleUserName"> <restriction base="string"> <minLength value="1" fixed="false"/> <maxLength value="4000" fixed="false"/> </restriction> </simpleType> <simpleType name="ResMetaStr"> <restriction base="string"> <minLength value="1" fixed="false"/> <maxLength value="128" fixed="false"/> </restriction> </simpleType> <simpleType name="SchElemType"> <restriction base="string"> <minLength value="1" fixed="false"/> <maxLength value="4000" fixed="false"/> </restriction> </simpleType> <simpleType name="GUID"> <restriction base="hexBinary"> <minLength value="8" fixed="false"/> <maxLength value="32" fixed="false"/> </restriction> </simpleType> <simpleType name="LocksRaw"> <restriction base="hexBinary"> <minLength value="0" fixed="false"/> <maxLength value="2000" fixed="false"/> </restriction> </simpleType> <simpleType name="LockScopeType"> <restriction base="string"> <enumeration value="Exclusive" fixed="false"/> <enumeration value="Shared" fixed="false"/> </restriction> </simpleType> <complexType name="LockType" mixed="false"> <sequence> <element name="owner" type="string"/> <element name="expires" type="dateTime"/> <element name="lockToken" type="hexBinary"/> </sequence> <attribute name="LockScope" type="res:LockScopeType" /> </complexType> <complexType name="ResContentsType" mixed="false"> <sequence > <any name="ContentsAny" /> </sequence> </complexType> <complexType name="ResAclType" mixed="false"> <sequence > <any name="ACLAny"/> </sequence> </complexType> <complexType name="ResourceType" mixed="false"> <sequence > <element name="CreationDate" type="dateTime"/> <element name="ModificationDate" type="dateTime"/> <element name="Author" type="res:ResMetaStr"/> <element name="DisplayName" type="res:ResMetaStr"/> <element name="Comment" type="res:ResMetaStr"/> <element name="Language" type="res:ResMetaStr"/> <element name="CharacterSet" type="res:ResMetaStr"/> <element name="ContentType" type="res:ResMetaStr"/> <element name="RefCount" type="nonNegativeInteger"/> <element name="Lock" type="res:LocksRaw"/> <element pname="ACL" type="res:ResAclType" minOccurs="0" maxOccurs="1"/> <element name="Owner" type="res:OracleUserName" minOccurs="0" maxOccurs="1"/> <element name="Creator" type="res:OracleUserName" minOccurs="0" maxOccurs="1"/> <element name="LastModifier" type="res:OracleUserName" minOccurs="0" maxOccurs="1"/> <element name="SchemaElement" type="res:SchElemType" minOccurs="0" maxOccurs="1"/> <element name="Contents" type="res:ResContentsType" minOccurs="0" maxOccurs="1"/> <element name="VCRUID" type="res:GUID"/> <element name="Parents" type="hexBinary" minOccurs="0" maxOccurs="1000"/> <any name="ResExtra" namespace="##other" minOccurs="0" maxOccurs="65535"/> </sequence> <attribute name="Hidden" type="boolean"/> <attribute name="Invalid" type="boolean"/> <attribute name="VersionID" type="integer"/> <attribute name="ActivityID" type="integer"/> <attribute name="Container" type="boolean"/> <attribute name="CustomRslv" type="boolean"/> <attribute name="StickyRef" type="boolean"/> </complexType> <element name="Resource" type="res:ResourceType"/> </schema>
This section describes the Oracle XML DB supplied XML schema used to represent Oracle XML DB access control lists (ACLs):
XML schema, acl.xsd,
represents Oracle XML DB access control lists (ACLs):
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://xmlns.oracle.com/xdb/acl.xsd" version="1.0" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xdbacl="http://xmlns.oracle.com/xdb/acl.xsd" elementFormDefault="qualified"> <annotation> <documentation> This XML schema describes the structure of XML DB ACL documents. Note : The following "systemPrivileges" element lists all supported system privileges and their aggregations. See dav.xsd for description of DAV privileges Note : The elements and attributes marked "hidden" are for internal use only. </documentation> <appinfo> <xdb:systemPrivileges> <xdbacl:all> <xdbacl:read-properties/> <xdbacl:read-contents/> <xdbacl:read-acl/> <xdbacl:update/> <xdbacl:link/> <xdbacl:unlink/> <xdbacl:unlink-from/> <xdbacl:write-acl-ref/> <xdbacl:update-acl/> <xdbacl:link-to/> <xdbacl:resolve/> </xdbacl:all> </xdb:systemPrivileges> </appinfo> </annotation> <!-- privilegeNameType (this is an emptycontent type) --> <complexType name = "privilegeNameType"/> <!-- privilegeName element All system and user privileges are in the substitutionGroup of this element. --> <element name = "privilegeName" type="xdbacl:privilegeNameType" xdb:defaultTable=""/> <!-- All system privileges in the XML DB ACL namespace --> <element name = "read-properties" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "read-contents" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "read-acl" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "update" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "link" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "unlink" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "unlink-from" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "write-acl-ref" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "update-acl" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "link-to" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "resolve" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <element name = "all" type="xdbacl:privilegeNameType" substitutionGroup="xdbacl:privilegeName" xdb:defaultTable=""/> <!-- privilege element --> <element name = "privilege" xdb:SQLType = "XDB$PRIV_T" xdb:defaultTable=""> <complexType> <choice maxOccurs="unbounded"> <any xdb:transient="generated"/> <!-- HIDDEN ELEMENTS --> <element name = "privNum" type = "hexBinary" xdb:baseProp="true" xdb:hidden="true"/> </choice> </complexType> </element> <!-- ace element --> <element name = "ace" xdb:SQLType = "XDB$ACE_T" xdb:defaultTable=""> <complexType> <sequence> <element name = "grant" type = "boolean"/> <element name = "principal" type = "string" xdb:transient="generated"/> <element ref="xdbacl:privilege" minOccurs="1"/> <!-- HIDDEN ELEMENTS --> <element name = "principalID" type = "hexBinary" minOccurs="0" xdb:baseProp="true" xdb:hidden="true"/> <element name = "flags" type = "unsignedInt" minOccurs="0" xdb:baseProp="true" xdb:hidden="true"/> </sequence> </complexType> </element> <!-- acl element --> <element name = "acl" xdb:SQLType = "XDB$ACL_T" xdb:defaultTable = "XDB$ACL"> <complexType> <sequence> <element name = "schemaURL" type = "string" minOccurs="0" xdb:transient="generated"/> <element name = "elementName" type = "string" minOccurs="0" xdb:transient="generated"/> <element ref = "xdbacl:ace" minOccurs="1" maxOccurs = "unbounded" xdb:SQLCollType="XDB$ACE_LIST_T"/> <!-- HIDDEN ELEMENTS --> <element name = "schemaOID" type = "hexBinary" minOccurs="0" xdb:baseProp="true" xdb:hidden="true"/> <element name = "elementNum" type = "unsignedInt" minOccurs="0" xdb:baseProp="true" xdb:hidden="true"/> </sequence> <attribute name = "shared" type = "boolean" default="true"/> <attribute name = "description" type = "string"/> </complexType> </element> </schema>';
xdbconfig.xsd
, is the Oracle XML DB supplied XML schema used to configure Oracle XML DB:
<schema targetNamespace="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:xdbc="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" elementFormDefault="qualified"> <element name="xdbconfig" xdb:defaultTable="XDB$CONFIG"> <complexType> <sequence> <!-- predefined XDB properties - these should NOT be changed --> <element name="sysconfig"> <complexType> <sequence> <!-- generic XDB properties --> <element name="acl-max-age" type="unsignedInt" default="1000"/> <element name="acl-cache-size" type="unsignedInt" default="32"/> <element name="invalid-pathname-chars" type="string" default=""/> <element name="case-sensitive" type="boolean" default="true"/> <element name="call-timeout" type="unsignedInt" default="300"/> <element name="max-link-queue" type="unsignedInt" default="65536"/> <element name="max-session-use" type="unsignedInt" default="100"/> <element name="persistent-sessions" type="boolean" default="false"/> <element name="default-lock-timeout" type="unsignedInt" default="3600"/> <element name="xdbcore-logfile-path" type="string" default="/sys/log/xdblog.xml"/> <element name="xdbcore-log-level" type="unsignedInt" default="0"/> <element name="resource-view-cache-size" type="unsignedInt" default="1048576"/> <element name="case-sensitive-index-clause" type="string" minOccurs="0"/> <!-- protocol specific properties --> <element name="protocolconfig"> <complexType> <sequence> <!-- these apply to all protocols --> <element name="common"> <complexType> <sequence> <element name="extension-mappings"> <complexType> <sequence> <element name="mime-mappings" type="xdbc:mime-mapping-type"/> <element name="lang-mappings" type="xdbc:lang-mapping-type"/> <element name="charset-mappings" type="xdbc:charset-mapping-type"/> <element name="encoding-mappings" type="xdbc:encoding-mapping-type"/> <element name="xml-extensions" type="xdbc:xml-extension-type" minOccurs="0"/> </sequence> </complexType> </element> <element name="session-pool-size" type="unsignedInt" default="50"/> <element name="session-timeout" type="unsignedInt" default="6000"/> <element name="allow-anonymous-write" type="boolean" minOccurs="0" default="false"/> </sequence> </complexType> </element> <!-- FTP specific --> <element name="ftpconfig"> <complexType> <sequence> <element name="ftp-port" type="unsignedShort" default="2100"/> <element name="ftp-listener" type="string"/> <element name="ftp-protocol" type="string"/> <element name="logfile-path" type="string" default="/sys/log/ftplog.xml"/> <element name="log-level" type="unsignedInt" default="0"/> <element name="session-timeout" type="unsignedInt" default="6000"/> <element name="buffer-size" default="8192"> <simpleType> <restriction base="unsignedInt"> <minInclusive value="1024"/> <!-- 1KB --> <maxInclusive value="1048496"/> <!-- 1MB --> </restriction> </simpleType> </element> </sequence> </complexType> </element> <!-- HTTP specific --> <element name="httpconfig"> <complexType> <sequence> <element name="http-port" type="unsignedShort" default="8080"/> <element name="http-listener" type="string"/> <element name="http-protocol" type="string"/> <element name="max-http-headers" type="unsignedInt" default="64"/> <element name="max-header-size" type="unsignedInt" default="4096"/> <element name="max-request-body" type="unsignedInt" default="2000000000" minOccurs="1"/> <element name="session-timeout" type="unsignedInt" default="6000"/> <element name="server-name" type="string"/> <element name="logfile-path" type="string" default="/sys/log/httplog.xml"/> <element name="log-level" type="unsignedInt" default="0"/> <element name="servlet-realm" type="string" minOccurs="0"/> <element name="webappconfig"> <complexType> <sequence> <element name="welcome-file-list" type="xdbc:welcome-file-type"/> <element name="error-pages" type="xdbc:error-page-type"/> <element name="servletconfig" type="xdbc:servlet-config-type"/> </sequence> </complexType> </element> <element name="default-url-charset" type="string" minOccurs="0"/> <element name="http2-port" type="unsignedShort" minOccurs="0"/> <element name="http2-protocol" type="string" default="tcp" minOccurs="0"/> <element name="plsql" minOccurs="0"> <complexType> <sequence> <element name="log-level" type="unsignedInt" minOccurs="0"/> <element name="max-parameters" type="unsignedInt" minOccurs="0"/> </sequence> </complexType> </element> </sequence> </complexType> </element> </sequence> </complexType> </element> <element name="schemaLocation-mappings" type="xdbc:schemaLocation-mapping-type" minOccurs="0"/> <element name="xdbcore-xobmem-bound" type="unsignedInt" default="1024" minOccurs="0"/> <element name="xdbcore-loadableunit-size" type="unsignedInt" default="16" minOccurs="0"/> </sequence> </complexType> </element> <!-- users can add any properties they want here --> <element name="userconfig" minOccurs="0"> <complexType> <sequence> <any maxOccurs="unbounded" namespace="##other"/> </sequence> </complexType> </element> </sequence> </complexType> </element> <complexType name="welcome-file-type"> <sequence> <element name="welcome-file" minOccurs="0" maxOccurs="unbounded"> <simpleType> <restriction base="string"> <pattern value="[^/]*"/> </restriction> </simpleType> </element> </sequence> </complexType> <!-- customized error pages --> <complexType name="error-page-type"> <sequence> <element name="error-page" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <choice> <element name="error-code"> <simpleType> <restriction base="positiveInteger"> <minInclusive value="100"/> <maxInclusive value="999"/> </restriction> </simpleType> </element> <!-- Fully qualified classname of a Java exception type --> <element name="exception-type" type="string"/> <element name="OracleError"> <complexType> <sequence> <element name="facility" type="string" default="ORA"/> <element name="errnum" type="unsignedInt"/> </sequence> </complexType> </element> </choice> <element name="location" type="anyURI"/> </sequence> </complexType> </element> </sequence> </complexType> <!-- parameter for a servlet: name, value pair and a description --> <complexType name="param"> <sequence> <element name="param-name" type="string"/> <element name="param-value" type="string"/> <element name="description" type="string"/> </sequence> </complexType> <complexType name="plsql-servlet-config"> <sequence> <element name="database-username" type="string" minOccurs="0"/> <element name="authentication-mode" minOccurs="0"> <simpleType> <restriction base="string"> <enumeration value="Basic"/> <enumeration value="SingleSingOn"/> <enumeration value="GlobalOwa"/> <enumeration value="CustomOwa"/> <enumeration value="PerPackageOwa"/> </restriction> </simpleType> </element> <element name="session-cookie-name" type="string" minOccurs="0"/> <element name="session-state-management" minOccurs="0"> <simpleType> <restriction base="string"> <enumeration value="StatelessWithResetPackageState"/> <enumeration value="StatelessWithFastResetPackageState"/> <enumeration value="StatelessWithPreservePackageState"/> </restriction> </simpleType> </element> <element name="max-requests-per-session" type="unsignedInt" minOccurs="0"/> <element name="default-page" type="string" minOccurs="0"/> <element name="document-table-name" type="string" minOccurs="0"/> <element name="document-path" type="string" minOccurs="0"/> <element name="document-procedure" type="string" minOccurs="0"/> <element name="upload-as-long-raw" type="string" minOccurs="0" maxOccurs="unbounded"/> <element name="path-alias" type="string" minOccurs="0"/> <element name="path-alias-procedure" type="string" minOccurs="0"/> <element name="exclusion-list" type="string" minOccurs="0" maxOccurs="unbounded"/> <element name="cgi-environment-list" type="string" minOccurs="0" maxOccurs="unbounded"/> <element name="compatibility-mode" type="unsignedInt" minOccurs="0"/> <element name="nls-language" type="string" minOccurs="0"/> <element name="fetch-buffer-size" type="unsignedInt" minOccurs="0"/> <element name="error-style" minOccurs="0"> <simpleType> <restriction base="string"> <enumeration value="ApacheStyle"/> <enumeration value="ModplsqlStyle"/> <enumeration value="DebugStyle"/> </restriction> </simpleType> </element> <element name="transfer-mode" minOccurs="0"> <simpleType> <restriction base="string"> <enumeration value="Char"/> <enumeration value="Raw"/> </restriction> </simpleType> </element> <element name="before-procedure" type="string" minOccurs="0"/> <element name="after-procedure" type="string" minOccurs="0"/> <element name="bind-bucket-lengths" type="unsignedInt" minOccurs="0" maxOccurs="unbounded"/> <element name="bind-bucket-widths" type="unsignedInt" minOccurs="0" maxOccurs="unbounded"/> <element name="always-describe-procedure" minOccurs="0"> <simpleType> <restriction base="string"> <enumeration value="On"/> <enumeration value="Off"/> </restriction> </simpleType> </element> <element name="info-logging" minOccurs="0"> <simpleType> <restriction base="string"> <enumeration value="InfoDebug"/> </restriction> </simpleType> </element> </sequence> </complexType> <complexType name="servlet-config-type"> <sequence> <element name="servlet-mappings"> <complexType> <sequence> <element name="servlet-mapping" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="servlet-pattern" type="string"/> <element name="servlet-name" type="string"/> </sequence> </complexType> </element> </sequence> </complexType> </element> <element name="servlet-list"> <complexType> <sequence> <element name="servlet" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="servlet-name" type="string"/> <element name="servlet-language"> <simpleType> <restriction base="string"> <enumeration value="C"/> <enumeration value="Java"/> <enumeration value="PL/SQL"/> </restriction> </simpleType> </element> <element name="icon" type="string" minOccurs="0"/> <element name="display-name" type="string"/> <element name="description" type="string" minOccurs="0"/> <choice> <element name="servlet-class" type="string" minOccurs="0"/> <element name="jsp-file" type="string" minOccurs="0"/> </choice> <element name="servlet-schema" type="string" minOccurs="0"/> <element name="init-param" minOccurs="0" maxOccurs="unbounded" type="xdbc:param"/> <element name="load-on-startup" type="string" minOccurs="0"/> <element name="security-role-ref" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="description" type="string" minOccurs="0"/> <element name="role-name" type="string"/> <element name="role-link" type="string"/> </sequence> </complexType> </element> <element name="plsql" type="xdbc:plsql-servlet-config" minOccurs="0"/> </sequence> </complexType> </element> </sequence> </complexType> </element> </sequence> </complexType> <complexType name="lang-mapping-type"> <sequence> <element name="lang-mapping" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="extension" type="xdbc:exttype"/> <element name="lang" type="string"/> </sequence> </complexType> </element> </sequence> </complexType> <complexType name="charset-mapping-type"> <sequence> <element name="charset-mapping" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="extension" type="xdbc:exttype"/> <element name="charset" type="string"/> </sequence> </complexType> </element> </sequence> </complexType> <complexType name="encoding-mapping-type"> <sequence> <element name="encoding-mapping" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="extension" type="xdbc:exttype"/> <element name="encoding" type="string"/> </sequence> </complexType> </element> </sequence> </complexType> <complexType name="mime-mapping-type"> <sequence> <element name="mime-mapping" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="extension" type="xdbc:exttype"/> <element name="mime-type" type="string"/> </sequence> </complexType> </element> </sequence> </complexType> <complexType name="xml-extension-type"> <sequence> <element name="extension" type="xdbc:exttype" minOccurs="0" maxOccurs="unbounded"> </element> </sequence> </complexType> <complexType name="schemaLocation-mapping-type"> <sequence> <element name="schemaLocation-mapping" minOccurs="0" maxOccurs="unbounded"> <complexType> <sequence> <element name="namespace" type="string"/> <element name="element" type="string"/> <element name="schemaURL" type="string"/> </sequence> </complexType> </element> </sequence> </complexType> <simpleType name="exttype"> <restriction base="string"> <pattern value="[^\*\./]*"/> </restriction> </simpleType> </schema>
This section contains the complete listings of the annotated purchase-order XML schemas used in various examples, particularly in Chapter 3. Example D-2 represents a modified version of Example D-1; the modification is used in Chapter 8 to illustrate XML schema evolution.
Example D-1 Annotated Purchase-Order XML Schema, purchaseOrder.xsd
This is the complete listing of the annotated XML schema presented in Example 3-8.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true"> <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/> <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T"> <xs:sequence> <xs:element name="Reference" type="ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/> <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/> <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/> <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/> <xs:element name="User" type="UserType" minOccurs="1" xdb:SQLName="USERID"/> <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/> <xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/> <xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/> <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T"> <xs:sequence> <xs:element name="Description" type="DescriptionType" xdb:SQLName="DESCRIPTION"/> <xs:element name="Part" type="PartType" xdb:SQLName="PART"/> </xs:sequence> <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/> </xs:complexType> <xs:complexType name="PartType" xdb:SQLType="PART_T"> <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:minLength value="10"/> <xs:maxLength value="14"/> </xs:restriction> </xs:simpleType> </xs:attribute> <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/> <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/> </xs:complexType> <xs:simpleType name="ReferenceType"> <xs:restriction base="xs:string"> <xs:minLength value="18"/> <xs:maxLength value="30"/> </xs:restriction> </xs:simpleType> <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T"> <xs:sequence> <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V"> <xs:complexType xdb:SQLType="actioN_t"> <xs:sequence> <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/> <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T"> <xs:all> <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/> <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/> <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/> </xs:all> </xs:complexType> <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T"> <xs:sequence> <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/> <xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/> <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/> </xs:sequence> </xs:complexType> <xs:simpleType name="moneyType"> <xs:restriction base="xs:decimal"> <xs:fractionDigits value="2"/> <xs:totalDigits value="12"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="quantityType"> <xs:restriction base="xs:decimal"> <xs:fractionDigits value="4"/> <xs:totalDigits value="8"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="UserType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="10"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="RequestorType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="128"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="CostCenterType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="4"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="VendorType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="20"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="PurchaseOrderNumberType"> <xs:restriction base="xs:integer"/> </xs:simpleType> <xs:simpleType name="SpecialInstructionsType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="2048"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="NameType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="20"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="AddressType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="256"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="TelephoneType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="24"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="DateType"> <xs:restriction base="xs:date"/> </xs:simpleType> <xs:simpleType name="CommentsType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="2048"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="DescriptionType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="256"/> </xs:restriction> </xs:simpleType> </xs:schema>
Example D-2 Revised Purchase-Order XML Schema
This is the complete listing of the revised annotated XML schema presented in Example 8-1. Text that is in bold face is additional or significantly different from that in the schema of Example D-1.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0"> <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER" xdb:columnProps= "CONSTRAINT purchaseorder_pkey PRIMARY KEY (XMLDATA.reference), CONSTRAINT valid_email_address FOREIGN KEY (XMLDATA.userid) REFERENCES hr.employees (EMAIL)" xdb:tableProps= "VARRAY XMLDATA.ACTIONS.ACTION STORE AS TABLE ACTION_TABLE ((CONSTRAINT action_pkey PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))) VARRAY XMLDATA.LINEITEMS.LINEITEM STORE AS TABLE LINEITEM_TABLE ((constraint LINEITEM_PKEY primary key (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))) lob (XMLDATA.NOTES) STORE AS (ENABLE STORAGE IN ROW STORAGE(INITIAL 4K NEXT 32K))"/> <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T"> <xs:sequence> <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/> <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/> <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/> <xs:element name="User" type="UserType" xdb:SQLName="USERID"/> <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/> <xs:element name="BillingAddress" type="AddressType" minOccurs="0" xdb:SQLName="BILLING_ADDRESS"/> <xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/> <xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/> <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/> <xs:element name="Notes" type="NotesType" minOccurs="0" xdb:SQLType="CLOB" xdb:SQLName="NOTES"/> </xs:sequence> <xs:attribute name="Reference" type="ReferenceType" use="required" xdb:SQLName="REFERENCE"/> <xs:attribute name="DateCreated" type="xs:dateTime" use="required" xdb:SQLType="TIMESTAMP WITH TIME ZONE"/> </xs:complexType> <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T"> <xs:sequence> <xs:element name="Part" type="PartType" xdb:SQLName="PART"/> <xs:element name="Quantity" type="quantityType"/> </xs:sequence> <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/> </xs:complexType> <xs:complexType name="PartType" xdb:SQLType="PART_T"> <xs:simpleContent> <xs:extension base="UPCCodeType"> <xs:attribute name="Description" type="DescriptionType" use="required" xdb:SQLName="DESCRIPTION"/> <xs:attribute name="UnitCost" type="moneyType" use="required"/> </xs:extension> </xs:simpleContent> </xs:complexType> <xs:simpleType name="ReferenceType"> <xs:restriction base="xs:string"> <xs:minLength value="18"/> <xs:maxLength value="30"/> </xs:restriction> </xs:simpleType> <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T"> <xs:sequence> <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V"> <xs:complexType xdb:SQLType="ACTION_T"> <xs:sequence> <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/> <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T"> <xs:all> <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/> <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/> <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/> </xs:all> </xs:complexType> <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T"> <xs:sequence> <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/> <xs:choice> <xs:element name="address" type="AddressType" minOccurs="0"/> <xs:element name="fullAddress" type="FullAddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/> </xs:choice> <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/> </xs:sequence> </xs:complexType> <xs:simpleType name="moneyType"> <xs:restriction base="xs:decimal"> <xs:fractionDigits value="2"/> <xs:totalDigits value="12"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="quantityType"> <xs:restriction base="xs:decimal"> <xs:fractionDigits value="4"/> <xs:totalDigits value="8"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="UserType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="10"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="RequestorType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="128"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="CostCenterType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="4"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="VendorType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="20"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="PurchaseOrderNumberType"> <xs:restriction base="xs:integer"/> </xs:simpleType> <xs:simpleType name="SpecialInstructionsType"> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="2048"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="NameType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="20"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="FullAddressType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="256"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="TelephoneType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="24"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="DateType"> <xs:restriction base="xs:date"/> </xs:simpleType> <xs:simpleType name="CommentsType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="2048"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="DescriptionType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="256"/> </xs:restriction> </xs:simpleType> <xs:complexType name="AddressType" xdb:SQLType="ADDRESS_T"> <xs:sequence> <xs:element name="StreetLine1" type="StreetType"/> <xs:element name="StreetLine2" type="StreetType" minOccurs="0"/> <xs:element name="City" type="CityType"/> <xs:choice> <xs:sequence> <xs:element name="State" type="StateType"/> <xs:element name="ZipCode" type="ZipCodeType"/> </xs:sequence> <xs:sequence> <xs:element name="Province" type="ProvinceType"/> <xs:element name="PostCode" type="PostCodeType"/> </xs:sequence> <xs:sequence> <xs:element name="County" type="CountyType"/> <xs:element name="Postcode" type="PostCodeType"/> </xs:sequence> </xs:choice> <xs:element name="Country" type="CountryType"/> </xs:sequence> </xs:complexType> <xs:simpleType name="StreetType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="128"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="CityType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="64"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="StateType"> <xs:restriction base="xs:string"> <xs:minLength value="2"/> <xs:maxLength value="2"/> <xs:enumeration value="AK"/> <xs:enumeration value="AL"/> <xs:enumeration value="AR"/> <xs:enumeration value="AS"/> <xs:enumeration value="AZ"/> <xs:enumeration value="CA"/> <xs:enumeration value="CO"/> <xs:enumeration value="CT"/> <xs:enumeration value="DC"/> <xs:enumeration value="DE"/> <xs:enumeration value="FL"/> <xs:enumeration value="FM"/> <xs:enumeration value="GA"/> <xs:enumeration value="GU"/> <xs:enumeration value="HI"/> <xs:enumeration value="IA"/> <xs:enumeration value="ID"/> <xs:enumeration value="IL"/> <xs:enumeration value="IN"/> <xs:enumeration value="KS"/> <xs:enumeration value="KY"/> <xs:enumeration value="LA"/> <xs:enumeration value="MA"/> <xs:enumeration value="MD"/> <xs:enumeration value="ME"/> <xs:enumeration value="MH"/> <xs:enumeration value="MI"/> <xs:enumeration value="MN"/> <xs:enumeration value="MO"/> <xs:enumeration value="MP"/> <xs:enumeration value="MQ"/> <xs:enumeration value="MS"/> <xs:enumeration value="MT"/> <xs:enumeration value="NC"/> <xs:enumeration value="ND"/> <xs:enumeration value="NE"/> <xs:enumeration value="NH"/> <xs:enumeration value="NJ"/> <xs:enumeration value="NM"/> <xs:enumeration value="NV"/> <xs:enumeration value="NY"/> <xs:enumeration value="OH"/> <xs:enumeration value="OK"/> <xs:enumeration value="OR"/> <xs:enumeration value="PA"/> <xs:enumeration value="PR"/> <xs:enumeration value="PW"/> <xs:enumeration value="RI"/> <xs:enumeration value="SC"/> <xs:enumeration value="SD"/> <xs:enumeration value="TN"/> <xs:enumeration value="TX"/> <xs:enumeration value="UM"/> <xs:enumeration value="UT"/> <xs:enumeration value="VA"/> <xs:enumeration value="VI"/> <xs:enumeration value="VT"/> <xs:enumeration value="WA"/> <xs:enumeration value="WI"/> <xs:enumeration value="WV"/> <xs:enumeration value="WY"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="ZipCodeType"> <xs:restriction base="xs:string"> <xs:pattern value="\d{5}"/> <xs:pattern value="\d{5}-\d{4}"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="CountryType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="64"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="CountyType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="32"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="PostCodeType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="12"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="ProvinceType"> <xs:restriction base="xs:string"> <xs:minLength value="2"/> <xs:maxLength value="2"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="NotesType"> <xs:restriction base="xs:string"> <xs:maxLength value="32767"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="UPCCodeType"> <xs:restriction base="xs:string"> <xs:minLength value="11"/> <xs:maxLength value="14"/> <xs:pattern value="\d{11}"/> <xs:pattern value="\d{12}"/> <xs:pattern value="\d{13}"/> <xs:pattern value="\d{14}"/> </xs:restriction> </xs:simpleType> </xs:schema>
Example D-3 Inserting XML Content into an XMLType Table Using C
This example is partially listed in Chapter 3, "Using Oracle XML DB", "Loading XML Content Using C".
#include "stdio.h" #include <xml.h> #include <stdlib.h> #include <string.h> #include <ocixmldb.h> OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIServer *srvhp; OCIDuration dur; OCISession *sesshp; oratext *username = "QUINE"; oratext *password = "CURRY"; oratext *filename = "AMCEWEN-20021009123336171PDT.xml"; oratext *schemaloc = "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"; /* Execute a SQL statement that binds XML data */ sword exec_bind_xml(OCISvcCtx *svchp, OCIError *errhp, OCIStmt *stmthp, void *xml, OCIType *xmltdo, OraText *sqlstmt) { OCIBind *bndhp1 = (OCIBind *) 0; sword status = 0; OCIInd ind = OCI_IND_NOTNULL; OCIInd *indp = &ind; if(status = OCIStmtPrepare(stmthp, errhp, (OraText *)sqlstmt, (ub4)strlen((const char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) return OCI_ERROR; if(status = OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) return OCI_ERROR; if(status = OCIBindObject(bndhp1, errhp, (CONST OCIType *) xmltdo, (dvoid **) &xml, (ub4 *) 0, (dvoid **) &indp, (ub4 *) 0)) return OCI_ERROR; if(status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) return OCI_ERROR; return OCI_SUCCESS; } /* Initialize OCI handles, and connect */ sword init_oci_connect() { sword status; if (OCIEnvCreate((OCIEnv **) &(envhp), (ub4) OCI_OBJECT, (dvoid *) 0, (dvoid * (*)(dvoid *,size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void (*)(dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIEnvCreate()\n"); return OCI_ERROR; } /* Allocate error handle */ if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &(errhp), (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on errhp\n"); return OCI_ERROR; } /* Allocate server handle */ if (status = OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on srvhp\n"); return OCI_ERROR; } /* Allocate service context handle */ if (status = OCIHandleAlloc((dvoid *) envhp, (dvoid **) &(svchp), (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on svchp\n"); return OCI_ERROR; } /* Allocate session handle */ if (status = OCIHandleAlloc((dvoid *) envhp, (dvoid **) &sesshp , (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on sesshp\n"); return OCI_ERROR; } /* Allocate statement handle */ if (OCIHandleAlloc((dvoid *)envhp, (dvoid **) &stmthp, (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on stmthp\n"); return status; } if (status = OCIServerAttach((OCIServer *) srvhp, (OCIError *) errhp, (CONST oratext *)"", 0, (ub4) OCI_DEFAULT)) { printf("FAILED: OCIServerAttach() on srvhp\n"); return OCI_ERROR; } /* Set server attribute to service context */ if (status = OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp)) { printf("FAILED: OCIAttrSet() on svchp\n"); return OCI_ERROR; } /* Set user attribute to session */ if (status = OCIAttrSet((dvoid *)sesshp, (ub4) OCI_HTYPE_SESSION, (dvoid *)username, (ub4) strlen((const char *)username), (ub4) OCI_ATTR_USERNAME, (OCIError *) errhp)) { printf("FAILED: OCIAttrSet() on authp for user\n"); return OCI_ERROR; } /* Set password attribute to session */ if (status = OCIAttrSet((dvoid *) sesshp, (ub4) OCI_HTYPE_SESSION, (dvoid *)password, (ub4) strlen((const char *)password), (ub4) OCI_ATTR_PASSWORD, (OCIError *) errhp)) { printf("FAILED: OCIAttrSet() on authp for password\n"); return OCI_ERROR; } /* Begin a session */ if (status = OCISessionBegin((OCISvcCtx *) svchp, (OCIError *) errhp, (OCISession *) sesshp, (ub4) OCI_CRED_RDBMS, (ub4) OCI_STMT_CACHE)) { printf("FAILED: OCISessionBegin(). Make sure database is up and the username/password is valid. \n"); return OCI_ERROR; } /* Set session attribute to service context */ if (status = OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)sesshp, (ub4) 0, (ub4) OCI_ATTR_SESSION, (OCIError *) errhp)) { printf("FAILED: OCIAttrSet() on svchp\n"); return OCI_ERROR; } } /* Free OCI handles, and disconnect */ void free_oci() { sword status = 0; /* End the session */ if (status = OCISessionEnd((OCISvcCtx *)svchp, (OCIError *)errhp, (OCISession *)sesshp, (ub4) OCI_DEFAULT)) { if (envhp) OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV); return; } /* Detach from the server */ if (status = OCIServerDetach((OCIServer *)srvhp, (OCIError *)errhp, (ub4)OCI_DEFAULT)) { if (envhp) OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV); return; } /* Free the handles */ if (stmthp) OCIHandleFree((dvoid *)stmthp, (ub4) OCI_HTYPE_STMT); if (sesshp) OCIHandleFree((dvoid *)sesshp, (ub4) OCI_HTYPE_SESSION); if (svchp) OCIHandleFree((dvoid *)svchp, (ub4) OCI_HTYPE_SVCCTX); if (srvhp) OCIHandleFree((dvoid *)srvhp, (ub4) OCI_HTYPE_SERVER); if (errhp) OCIHandleFree((dvoid *)errhp, (ub4) OCI_HTYPE_ERROR); if (envhp) OCIHandleFree((dvoid *)envhp, (ub4) OCI_HTYPE_ENV); return; } void main() { OCIType *xmltdo; xmldocnode *doc; ocixmldbparam params[1]; xmlerr err; xmlctx *xctx; oratext *ins_stmt; sword status; xmlnode *root; oratext buf[10000]; /* Initialize envhp, svchp, errhp, dur, stmthp */ init_oci_connect(); /* Get an XML context */ params[0].name_ocixmldbparam = XCTXINIT_OCIDUR; params[0].value_ocixmldbparam = &dur; xctx = OCIXmlDbInitXmlCtx(envhp, svchp, errhp, params, 1); if (!(doc = XmlLoadDom(xctx, &err, "file", filename, "schema_location", schemaloc, NULL))) { printf("Parse failed.\n"); return; } else printf("Parse succeeded.\n"); root = XmlDomGetDocElem(xctx, doc); printf("The xml document is :\n"); XmlSaveDom(xctx, &err, (xmlnode *)doc, "buffer", buf, "buffer_length", 10000, NULL); printf("%s\n", buf); /* Insert the document into my_table */ ins_stmt = (oratext *)"insert into purchaseorder values (:1)"; status = OCITypeByName(envhp, errhp, svchp, (const text *) "SYS", (ub4) strlen((const char *)"SYS"), (const text *) "XMLTYPE", (ub4) strlen((const char *)"XMLTYPE"), (CONST text *) 0, (ub4) 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, (OCIType **) &xmltdo); if (status == OCI_SUCCESS) { status = exec_bind_xml(svchp, errhp, stmthp, (void *)doc, xmltdo, ins_stmt); } if (status == OCI_SUCCESS) printf ("Insert successful\n"); else printf ("Insert failed\n"); /* Free XML instances */ if (doc) XmlFreeDocument((xmlctx *)xctx, (xmldocnode *)doc); /* Free XML CTX */ OCIXmlDbFreeXmlCtx(xctx); free_oci(); }
Example D-4 shows how to use OCI functions OCIXmlDbInitXmlCtx()
and OCIXmlDbFreeXmlCtx()
to initialize and terminate the XML context. It constructs an XML document using the C DOM API and saves it to the database.
Example D-4 is partially listed in Chapter 14, "Using the C API for XML", "Initializing and Terminating an XML Context". It assumes that the following SQL code has first been executed to create table my_table
in database schema CAPIUSER
:
CONNECT CAPIUSER/CAPIUSER CREATE TABLE my_table OF XMLType;
Example D-4 Using OCIXmlDbInitXmlCtx() and OCIXmlDbFreeXmlCtx()
#ifndef S_ORACLE #include <s.h> #endif #ifndef ORATYPES_ORACLE #include <oratypes.h> #endif #ifndef XML_ORACLE #include <xml.h> #endif #ifndef OCIXML_ORACLE #include <ocixml.h> #endif #ifndef OCI_ORACLE #include <oci.h> #endif #include <string.h> typedef struct test_ctx { OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIServer *srvhp; OCIDuration dur; OCISession *sesshp; oratext *username; oratext *password; } test_ctx; /* Helper function 1: execute a sql statement which binds xml data */ STATICF sword exec_bind_xml(OCISvcCtx *svchp, OCIError *errhp, OCIStmt *stmthp, void *xml, OCIType *xmltdo, OraText *sqlstmt); /* Helper function 2: Initialize OCI handles and connect */ STATICF sword init_oci_handles(test_ctx *ctx); /* Helper function 3: Free OCI handles and disconnect */ STATICF sword free_oci_handles(test_ctx *ctx); void main() { test_ctx temp_ctx; test_ctx *ctx = &temp_ctx; OCIType *xmltdo = (OCIType *) 0; xmldocnode *doc = (xmldocnode *)0; ocixmldbparam params[1]; xmlnode *quux, *foo, *foo_data, *top; xmlerr err; sword status = 0; xmlctx *xctx; oratext ins_stmt[] = "insert into my_table values (:1)"; oratext tlpxml_test_sch[] = "<TOP/>"; ctx->username = (oratext *)"CAPIUSER"; ctx->password = (oratext *)"CAPIUSER"; /* Initialize envhp, svchp, errhp, dur, stmthp */ init_oci_handles(ctx); /* Get an xml context */ params[0].name_ocixmldbparam = XCTXINIT_OCIDUR; params[0].value_ocixmldbparam = &ctx->dur; xctx = OCIXmlDbInitXmlCtx(ctx->envhp, ctx->svchp, ctx->errhp, params, 1); /* Start processing - first, check that this DOM supports XML 1.0 */ printf("\n\nSupports XML 1.0? : %s\n", XmlHasFeature(xctx, (oratext *) "xml", (oratext *) "1.0") ? "YES" : "NO"); /* Parse a document */ if (!(doc = XmlLoadDom(xctx, &err, "buffer", tlpxml_test_sch, "buffer_length", sizeof(tlpxml_test_sch)-1, "validate", TRUE, NULL))) { printf("Parse failed, code %d\n", err); } else { /* Get the document element */ top = (xmlnode *)XmlDomGetDocElem(xctx, doc); /* Print out the top element */ printf("\n\nOriginal top element is :\n"); XmlSaveDom(xctx, &err, top, "stdio", stdout, NULL); /* Print out the document-note that the changes are reflected here */ printf("\n\nOriginal document is :\n"); XmlSaveDom(xctx, &err, (xmlnode *)doc, "stdio", stdout, NULL); /* Create some elements and add them to the document */ quux = (xmlnode *) XmlDomCreateElem(xctx ,doc, (oratext *) "QUUX"); foo = (xmlnode *) XmlDomCreateElem(xctx, doc, (oratext *) "FOO"); foo_data = (xmlnode *) XmlDomCreateText(xctx, doc, (oratext *) "data"); foo_data = XmlDomAppendChild(xctx, (xmlnode *) foo, (xmlnode *) foo_data); foo = XmlDomAppendChild(xctx, quux, foo); quux = XmlDomAppendChild(xctx, top, quux); /* Print out the top element */ printf("\n\nNow the top element is :\n"); XmlSaveDom(xctx, &err, top, "stdio", stdout, NULL); /* Print out the document. Note that the changes are reflected here */ printf("\n\nNow the document is :\n"); XmlSaveDom(xctx, &err, (xmlnode *)doc, "stdio", stdout, NULL); /* Insert the document into my_table */ status = OCITypeByName(ctx->envhp, ctx->errhp, ctx->svchp, (const text *) "SYS", (ub4) strlen((char *)"SYS"), (const text *) "XMLTYPE", (ub4) strlen((char *)"XMLTYPE"), (CONST text *) 0, (ub4) 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, (OCIType **) &xmltdo); if (status == OCI_SUCCESS) { exec_bind_xml(ctx->svchp, ctx->errhp, ctx->stmthp, (void *)doc, xmltdo, ins_stmt); } } /* Free xml ctx */ OCIXmlDbFreeXmlCtx(xctx); /* Free envhp, svchp, errhp, stmthp */ free_oci_handles(ctx); } /* Helper function 1: execute a SQL statement that binds xml data */ STATICF sword exec_bind_xml(OCISvcCtx *svchp, OCIError *errhp, OCIStmt *stmthp, void *xml, OCIType *xmltdo, OraText *sqlstmt) { OCIBind *bndhp1 = (OCIBind *) 0; sword status = 0; OCIInd ind = OCI_IND_NOTNULL; OCIInd *indp = &ind; if(status = OCIStmtPrepare(stmthp, errhp, (OraText *)sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { printf("Failed OCIStmtPrepare\n"); return OCI_ERROR; } if(status = OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { printf("Failed OCIBindByPos\n"); return OCI_ERROR; } if(status = OCIBindObject(bndhp1, errhp, (CONST OCIType *) xmltdo, (dvoid **) &xml, (ub4 *) 0, (dvoid **) &indp, (ub4 *) 0)) { printf("Failed OCIBindObject\n"); return OCI_ERROR; } if(status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { printf("Failed OCIStmtExecute\n"); return OCI_ERROR; } return OCI_SUCCESS; } /* Helper function 2: Initialize OCI handles and connect */ STATICF sword init_oci_handles(test_ctx *ctx) { sword status; ctx->dur = OCI_DURATION_SESSION; if (OCIEnvCreate((OCIEnv **) &(ctx->envhp), (ub4) OCI_OBJECT, (dvoid *) 0, (dvoid * (*)(dvoid *,size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void (*)(dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIEnvCreate()\n"); return OCI_ERROR; } /* Allocate error handle */ if (OCIHandleAlloc((dvoid *) ctx->envhp, (dvoid **) &(ctx->errhp), (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on errhp\n"); return OCI_ERROR; } /* Allocate server handle */ if (status = OCIHandleAlloc((dvoid *) ctx->envhp, (dvoid **) &ctx->srvhp, (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on srvhp\n"); return OCI_ERROR; } /* Allocate service context handle */ if (status = OCIHandleAlloc((dvoid *) ctx->envhp, (dvoid **) &(ctx->svchp), (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on svchp\n"); return OCI_ERROR; } /* Allocate session handle */ if (status = OCIHandleAlloc((dvoid *) ctx->envhp, (dvoid **) &ctx->sesshp , (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on sesshp\n"); return OCI_ERROR; } /* Allocate statement handle */ if (OCIHandleAlloc((dvoid *)ctx->envhp, (dvoid **) &ctx->stmthp, (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0)) { printf("FAILED: OCIHandleAlloc() on stmthp\n"); return status; } if (status = OCIServerAttach((OCIServer *) ctx->srvhp, (OCIError *) ctx->errhp, (CONST oratext *)"", 0, (ub4) OCI_DEFAULT)) { printf("FAILED: OCIServerAttach() on srvhp\n"); return OCI_ERROR; } /* Set server attribute to service context */ if (status = OCIAttrSet((dvoid *) ctx->svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) ctx->srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) ctx->errhp)) { printf("FAILED: OCIAttrSet() on svchp\n"); return OCI_ERROR; } /* Set user attribute to session */ if (status = OCIAttrSet((dvoid *)ctx->sesshp, (ub4) OCI_HTYPE_SESSION, (dvoid *)ctx->username, (ub4) strlen((char *)ctx->username), (ub4) OCI_ATTR_USERNAME, (OCIError *) ctx->errhp)) { printf("FAILED: OCIAttrSet() on authp for user\n"); return OCI_ERROR; } /* Set password attribute to session */ if (status = OCIAttrSet((dvoid *) ctx->sesshp, (ub4) OCI_HTYPE_SESSION, (dvoid *)ctx->password, (ub4) strlen((char *)ctx->password), (ub4) OCI_ATTR_PASSWORD, (OCIError *) ctx->errhp)) { printf("FAILED: OCIAttrSet() on authp for password\n"); return OCI_ERROR; } /* Begin a session */ if (status = OCISessionBegin((OCISvcCtx *) ctx->svchp, (OCIError *) ctx->errhp, (OCISession *) ctx->sesshp, (ub4) OCI_CRED_RDBMS, (ub4) OCI_STMT_CACHE)) { printf("FAILED: OCISessionBegin(). Make sure database is up and the \ username/password is valid. \n"); return OCI_ERROR; } /* Set session attribute to service context */ if (status = OCIAttrSet((dvoid *) ctx->svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)ctx->sesshp, (ub4) 0, (ub4) OCI_ATTR_SESSION, (OCIError *) ctx->errhp)) { printf("FAILED: OCIAttrSet() on svchp\n"); return OCI_ERROR; } return status; } /* Helper function 3: Free OCI handles and disconnect */ STATICF sword free_oci_handles(test_ctx *ctx) { sword status = 0; /* End the session */ if (status = OCISessionEnd((OCISvcCtx *)ctx->svchp, (OCIError *)ctx->errhp, (OCISession *)ctx->sesshp, (ub4) OCI_DEFAULT)) { if (ctx->envhp) OCIHandleFree((dvoid *)ctx->envhp, OCI_HTYPE_ENV); return status; } /* Detach from the server */ if (status = OCIServerDetach((OCIServer *)ctx->srvhp, (OCIError *)ctx->errhp, (ub4)OCI_DEFAULT)) { if (ctx->envhp) OCIHandleFree((dvoid *)ctx->envhp, OCI_HTYPE_ENV); return status; } /* Free the handles */ if (ctx->stmthp) OCIHandleFree((dvoid *)ctx->stmthp, (ub4) OCI_HTYPE_STMT); if (ctx->sesshp) OCIHandleFree((dvoid *)ctx->sesshp, (ub4) OCI_HTYPE_SESSION); if (ctx->svchp) OCIHandleFree((dvoid *)ctx->svchp, (ub4) OCI_HTYPE_SVCCTX); if (ctx->srvhp) OCIHandleFree((dvoid *)ctx->srvhp, (ub4) OCI_HTYPE_SERVER); if (ctx->errhp) OCIHandleFree((dvoid *)ctx->errhp, (ub4) OCI_HTYPE_ERROR); if (ctx->envhp) OCIHandleFree((dvoid *)ctx->envhp, (ub4) OCI_HTYPE_ENV); return status; }