XMLType

Description

This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. (Oracle SQL Language Reference XML Data Type)

Snowflake handles semi-structured data types (including XMLTYPE) using the VARIANT data type, for this reason, XMLTYPEs are to be migrated to VARIANT, and then usages of functions used to manipulate and query XML must be migrated to Snowflake's counterparts. For more information on how to use XML in Snowflake, please refer to this post in the Snowflake forum and the TO_XML function documentation in Snowflake.

XMLTYPE

Sample Source Patterns

XMLType in Create Table

Oracle

IN -> Oracle_01.sql

CREATE TABLE xml_table(
    xml_column XMLTYPE
);

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE xml_table (
        xml_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XMLTYPE DATA TYPE CONVERTED TO VARIANT ***/!!!
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

Insert data in the XML column

Oracle

IN -> Oracle_02.sql

INSERT INTO xml_table VALUES(
    XMLType(
'<?xml version="1.0"?>  
<note>  
  <to>SnowConvert</to>  
  <from>Oracle</from>  
  <heading>Greeting</heading>  
  <body>Hello there!</body>  
</note>')
);

Snowflake

OUT -> Oracle_02.sql

INSERT INTO xml_table
VALUES(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0016 - FUNCTION RELATED WITH XML NOT SUPPORTED ***/!!!
    XMLType(
'<?xml version="1.0"?>  
<note>  
  <to>SnowConvert</to>  
  <from>Oracle</from>  
  <heading>Greeting</heading>  
  <body>Hello there!</body>  
</note>')
);

Known Issues

1. XMLType manipulation and query functions are not recognized

The functions for manipulating and querying XML such as XMLTYPE() are not being recognized nor transformed by SnowConvert.

PreviousXML Types NextURI Data Types

Last updated 1 year ago

Description

This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. (Oracle SQL Language Reference XML Data Type)

XMLTYPE

Sample Source Patterns

XMLType in Create Table

Oracle

IN -> Oracle_01.sql

CREATE TABLE xml_table(
    xml_column XMLTYPE
);

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE xml_table (
        xml_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XMLTYPE DATA TYPE CONVERTED TO VARIANT ***/!!!
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

Insert data in the XML column

Oracle

IN -> Oracle_02.sql

INSERT INTO xml_table VALUES(
    XMLType(
'<?xml version="1.0"?>  
<note>  
  <to>SnowConvert</to>  
  <from>Oracle</from>  
  <heading>Greeting</heading>  
  <body>Hello there!</body>  
</note>')
);

Snowflake

OUT -> Oracle_02.sql

INSERT INTO xml_table
VALUES(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0016 - FUNCTION RELATED WITH XML NOT SUPPORTED ***/!!!
    XMLType(
'<?xml version="1.0"?>  
<note>  
  <to>SnowConvert</to>  
  <from>Oracle</from>  
  <heading>Greeting</heading>  
  <body>Hello there!</body>  
</note>')
);

Description

Sample Source Patterns

XMLType in Create Table

Oracle

Snowflake

Insert data in the XML column

Oracle

Snowflake

Known Issues

1. XMLType manipulation and query functions are not recognized

Related EWIs

Description

Sample Source Patterns

XMLType in Create Table

Oracle

Snowflake

Insert data in the XML column

Oracle

Snowflake

Known Issues

1. XMLType manipulation and query functions are not recognized

Related EWIs