Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

1 Introducing the Oracle Data Service Integrator XQuery Engine

• 1.1 XML and XQuery
• 1.2 XQuery Use in Oracle Data Service Integrator
• 1.3 Supported XQuery Specifications
• 1.4 Learning More About the XQuery Language

2 Oracle's XQuery Implementation

• 2.1 Oracle XQuery Function Implementation
  • 2.1.1 Function Overview
  • 2.1.2 Access Control Functions
    • 2.1.2.1 fn-bea:is-access-allowed
    • 2.1.2.2 fn-bea:is-user-in-group
    • 2.1.2.3 fn-bea:is-user-in-role
    • 2.1.2.4 fn-bea:userid
  • 2.1.3 Duration, Date, and Time Functions
    • 2.1.3.1 fn-bea:date-from-dateTime
    • 2.1.3.2 fn-bea:date-from-string-with-format
    • 2.1.3.3 fn-bea:date-to-string-with-format
    • 2.1.3.4 fn-bea:dateTime-from-string-with-format
    • 2.1.3.5 fn-bea:dateTime-to-string-with-format
    • 2.1.3.6 fn-bea:time-from-dateTime
    • 2.1.3.7 fn-bea:time-from-string-with-format
    • 2.1.3.8 fn-bea:time-to-string-with-format
    • 2.1.3.9 Date and Time Patterns
  • 2.1.4 Execution Control Functions
    • 2.1.4.1 fn-bea:async
    • 2.1.4.2 fn-bea:fence
    • 2.1.4.3 fn-bea:timeout and fn-bea:timeout-with-label
    • 2.1.4.4 fn-bea:fail-over, fn-bea:fail-over-with-label, fn-bea:fail-over-retry, and fn-bea:fail-over-retry-with-label
  • 2.1.5 Numeric Functions
    • 2.1.5.1 fn-bea:format-number
    • 2.1.5.2 fn-bea:decimal-round
    • 2.1.5.3 fn-bea:decimal-truncate
  • 2.1.6 Other Functions
    • 2.1.6.1 fn-bea:get-property
    • 2.1.6.2 fn-bea:inlinedXML
    • 2.1.6.3 fn-bea:rename
  • 2.1.7 QName Functions
    • 2.1.7.1 fn-bea:QName-from-string
  • 2.1.8 Sequence Functions
    • 2.1.8.1 fn-bea:interleave
  • 2.1.9 String Functions
    • 2.1.9.1 fn-bea:match
    • 2.1.9.2 fn-bea:sql-like
    • 2.1.9.3 fn-bea:trim
    • 2.1.9.4 fn-bea:trim-left
    • 2.1.9.5 fn-bea:trim-right
    • 2.1.9.6 fn-bea:pad-left
    • 2.1.9.7 fn-bea:pad-right
  • 2.1.10 Extended XQuery Data Model (XXDM) Functions
    • 2.1.10.1 fn-bea:current-value
    • 2.1.10.2 fn-bea:old-value
  • 2.1.11 Using Mutators for Updates
    • 2.1.11.1 fn-bea:changed-element
    • 2.1.11.2 fn-bea:insert-into
    • 2.1.11.3 fn-bea:delete
    • 2.1.11.4 fn-bea:replace-value
  • 2.1.12 Mutator Function Examples
    • 2.1.12.1 Update Based on Simple Parameters
    • 2.1.12.2 Update Based on Complex Input Parameters
    • 2.1.12.3 Update When the Parameter Structure Matches the Return Type
    • 2.1.12.4 Update Based on Differences Between Old and New Values
    • 2.1.12.5 Update Using Additional Data
    • 2.1.12.6 Update Operation Replication
  • 2.1.13 Unsupported XQuery Functions
  • 2.1.14 Restrictions Related to Client Ad-Hoc Queries
  • 2.1.15 Implementation-Specific Functions and Operators
• 2.2 Oracle XQuery Language Implementation
  • 2.2.1 XQuery Language Support (and Unsupported Features)
  • 2.2.2 Extensions to the XQuery Language in the Oracle Data Service Integrator XQuery Engine
    • 2.2.2.1 Generalized FLWGOR (group by)
    • 2.2.2.2 Optional Indicator in Direct Element and Attribute Constructors
  • 2.2.3 Implementation-Defined Values for XQuery Language Processing

3 XQuery Engine and SQL

• 3.1 Introduction
  • 3.1.1 Base and Core RDBMS Support
  • 3.1.2 How the XQuery Engine Supports SQL Data Sources
    • 3.1.2.1 Metadata and Data Type Mappings Get Stored in Annotated Files
    • 3.1.2.2 Runtime Connection Management—Connection Sharing
• 3.2 XQuery-SQL Data Type Mappings
  • 3.2.1 Date and Time Data Type Differences: Timezones and Time Precision
  • 3.2.2 How Oracle Data Service Integrator Handles Timezone Information
  • 3.2.3 How Oracle Data Service Integrator Handles Fractional Seconds
  • 3.2.4 Scope Differences for Expressions and Data Types
• 3.3 SQL Pushdown: Performance Optimization
  • 3.3.1 Function and Operator Pushdown
  • 3.3.2 Parameters in Generated SQL Statements
  • 3.3.3 Cast Operation Pushdown
  • 3.3.4 Path Expressions Pushdown
  • 3.3.5 Constant Pushdown
  • 3.3.6 Variable Pushdown
  • 3.3.7 Common Query Patterns
    • 3.3.7.1 Simple Projection Queries
    • 3.3.7.2 Where Clause Pushdown
    • 3.3.7.3 Order By Clause Pushdown
    • 3.3.7.4 Inner Join Pushdown
    • 3.3.7.5 Outer Join Pushdown
    • 3.3.7.6 Semi-Joins and Anti-Semi-Joins
  • 3.3.8 Grouping and Aggregation
    • 3.3.8.1 Group By Pushdown
    • 3.3.8.2 Distinct-by Pushdown
    • 3.3.8.3 Simple Aggregate Pattern
    • 3.3.8.4 Group-By with a Nested Where Clause Translates to SQL HAVING Clause
    • 3.3.8.5 Outer Join with Aggregate Pattern
    • 3.3.8.6 If-Then-Else Pattern
    • 3.3.8.7 Subsequence Pushdown
  • 3.3.9 Direct SQL Data Services and Pushdown
  • 3.3.10 Distributed Query Pushdown
• 3.4 Preventing SQL Pushdown

4 Understanding XML Namespaces

• 4.1 Introducing XML Namespaces
  • 4.1.1 Exploring XML Schema Namespaces
• 4.2 Using XML Namespaces in Oracle Data Service Integrator Queries and Schemas

5 Best Practices Using XQuery

• 5.1 Introducing Data Service Design
• 5.2 Understanding Data Service Design Principles
• 5.3 Applying Data Service Implementation Guidelines

6 Oracle XQuery Scripting Extension (XQSE)

• 6.1 Introducing the XQuery Scripting Extension
• 6.2 Prolog and Query Body
• 6.3 Procedure Declaration
• 6.4 XQSE Function Declaration
• 6.5 Value Statement and Procedure Call
• 6.6 Block
• 6.7 Set Statement
• 6.8 While Statement
• 6.9 Return Statement
• 6.10 Iterate Statement
• 6.11 Try Statement
• 6.12 If Statement
• 6.13 Changed Element
• 6.14 XQSE Grammar Summary

A XQuery-SQL Mapping Reference

• A.1 IBM DB2 9.7, 10.1
  • A.1.1 Data Type Mapping
  • A.1.2 Function and Operator Pushdown
  • A.1.3 Cast Operation Pushdown
  • A.1.4 Other SQL Generation Capabilities
• A.2 Microsoft SQL Server 2008 R2, 2012
  • A.2.1 Data Type Mapping
  • A.2.2 Function and Operator Pushdown
  • A.2.3 Cast Operation Pushdown
  • A.2.4 Other SQL Generation Capabilities
• A.3 Oracle 11.x, 12.x
  • A.3.1 Data Type Mapping
  • A.3.2 Function and Operator Pushdown
  • A.3.3 Cast Operation Pushdown
  • A.3.4 Other SQL Generation Capabilities
• A.4 Sybase Adaptive Server Enterprise 15.7
  • A.4.1 Data Type Mapping
  • A.4.2 Function and Operator Pushdown
  • A.4.3 Cast Operation Pushdown
  • A.4.4 Other SQL Generation Capabilities
• A.5 Teradata 13.x, 14.x
  • A.5.1 Data Type Mapping
  • A.5.2 Function and Operator Pushdown
  • A.5.3 Cast Operation Pushdown
  • A.5.4 Other SQL Generation Capabilities
• A.6 Derby 10.6 (and higher)
  • A.6.1 Data Type Mappings
  • A.6.2 Functions and Operators
  • A.6.3 Cast Operations
• A.7 Base (Generic) RDBMS Support
  • A.7.1 Database Capabilities Information
  • A.7.2 Data Type Mapping
  • A.7.3 Cast Operation Pushdown
  • A.7.4 Other SQL Generation Capabilities