SSC-EWI-TS0044

FOR XML clause is 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

Critical

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

Description

This EWI is added for the FOR XML clause which is not supported in Snowflake SQL

Code Example

Input Code:

IN -> SqlServer_01.sql

SELECT TOP 1 LastName
FROM AdventureWorks2019.Person.Person
FOR XML AUTO;

Output Code:

OUT -> SqlServer_01.sql

SELECT TOP 1
LastName
FROM
AdventureWorks2019.Person.Person
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML AUTO CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML AUTO;

Recommendations

• Consider using UDFs to emulate the behavior of the source code. The following code provides suggestions of UDFs that can be used to achieve recreating the original behavior:

SQL Server

Query

IN -> SqlServer_02.sql

CREATE TABLE TEMPTABLE (Ref INT, Des NVARCHAR(100), Qty INT)

INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2)

GO

-- FOR XML
SELECT *
FROM TempTable
FOR XML AUTO

GO
 
-- FOR XML RAW
SELECT *
FROM TempTable
FOR XML RAW

Result

Snowflake

Query

OUT -> SqlServer_02.sql

CREATE OR REPLACE TABLE TEMPTABLE (
Ref INT,
Des VARCHAR(100),
Qty INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2);

-- FOR XML
SELECT
*
FROM
TempTable
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML AUTO CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML AUTO;

-- FOR XML RAW
SELECT
*
FROM
TempTable
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW;

Result

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