XDBURIType

Some parts in the output code are omitted for clarity reasons.

Description

You can use XDBURIType to expose documents in the XML database hierarchy as URIs that can be embedded in any URIType column in a table. The XDBURIType consists of a URL, which comprises the hierarchical name of the XML document to which it refers and an optional fragment representing the XPath syntax. (Oracle SQL Language Reference URI Data Types)

XDBURITYPE

Sample Source Patterns

XDBURIType in create table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE xdburitype_table(
    xdb_uritype_column XDBURITYPE,
    sys_xdb_uritype_column SYS.XDBURITYPE
);

INSERT INTO xdburitype_table (xdb_uritype_column) VALUES(
    xdburitype('/home/OE/employees/emp_selby.xml')
);

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE xdburitype_table (
        xdb_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'XDBURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
        !!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
        sys_xdb_uritype_column SYS.XDBURITYPE
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    CREATE OR REPLACE VIEW PUBLIC.xdburitype_table_view
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
    AS
    SELECT
        xdb_uritype_column,
        sys_xdb_uritype_column
    FROM
        xdburitype_table;

        INSERT INTO xdburitype_table(xdb_uritype_column) VALUES(
    xdburitype('/home/OE/employees/emp_selby.xml') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'xdburitype' NODE ***/!!!
);

Retrieving data from XDBURIType column

Oracle

Query

IN -> Oracle_02.sql

SELECT ut.xdb_uritype_column.getclob() FROM xdburitype_table ut;

Result

|UT.XDB_URITYPE_COLUMN.GETCLOB()|
|-------------------------------|
|<emp_name>selby</emp_name>     |

This result query has XML syntax, this is how it is displayed:

<emp_name>selby</emp_name>

Snowflake

Query

OUT -> Oracle_02.sql

SELECT ut.xdb_uritype_column.getclob() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.xdb_uritype_column.getclob' NODE ***/!!! FROM
xdburitype_table ut;

getclob function is not being transformed by the tool, but is necessary to display the data in Oracle, this transformation is going to be available in future releases.

Known Issues

1. XDBURIType Data Type not recognized

XDBURIType is parsed and converted as Custom Data Type by SnowConvert or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

Related EWIs

1. SSC-EWI-0028: Type not supported.

2. SSC-EWI-0062: Custom type usage changed to variant

3. SSC-EWI-0073: Pending functional equivalence review