This EWI is added for the following 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 and review some possible approaches to work with XML datatypes in Snowflake.
