SSC-EWI-TS0041

XML data type methods are not supported in Snowflake.

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]