SSC-EWI-TS0041

XML data type methods are not supported in Snowflake.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

Medium

Description

This EWI is added for the following XML data type methods that are not supported in Snowflake SQL:

• Value

• Query

• Exist

• Modify

• Nodes

Code Example

Input Code:

IN -> SqlServer_01.sql

CREATE PROCEDURE xml_procedure
    @inUserGroupsXML XML
AS
BEGIN
    SELECT  entities.entity.value('TypeID[1]', 'VARCHAR(100)') AS TypeID
        ,entities.entity.value('Name[1]', 'VARCHAR(100)') AS Name
    INTO  #tmpUserGroups
    FROM  @inUserGroupsXML.nodes('/entities/entity') entities(entity)
END;

Output Code:

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE xml_procedure (INUSERGROUPSXML TEXT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CREATE OR REPLACE TEMPORARY TABLE T_tmpUserGroups AS
            SELECT
                XMLGET(entity, '$') :: VARCHAR(100) AS TypeID
                ,
                XMLGET(entity, '$') :: VARCHAR(100) AS Name
            FROM
                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0041 - XML TYPE METHOD nodes IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                T_inUserGroupsXML('/entities/entity') entities (
                    entity
                );
    END;
$$;

Recommendations

• Consider using UDFs to emulate the behavior of the source code

• You can check this documentation and review some possible approaches to work with XML datatypes in Snowflake.

• If you need more support, you can email us at [email protected]