MSCEWI4044

FOR XML clause is not supported in Snowflake.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

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

Code Example

Input Code:

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

Output Code:

SELECT TOP 1 LastName
FROM AdventureWorks2019.Person.Person
-- ** MSC-ERROR - MSCEWI4043 - FOR XML 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

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

SELECT *
FROM TempTable
FOR XML AUTO

GO

/*
 returns
 <TempTable Ref="100001" Des="Normal" Qty="1"/><TempTable Ref="100002" Des="Foobar" Qty="1"/><TempTable Ref="100003" Des="Hello World" Qty="2"/>
 */
 
 
 SELECT *
FROM TempTable
FOR XML RAW

GO

/*
returns
<row Ref="100001" Des="Normal" Qty="1"/><row Ref="100002" Des="Foobar" Qty="1"/><row Ref="100003" Des="Hello World" Qty="2"/>
*/

Snowflake SQL

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