SSC-EWI-TS0043

WITH XMLNAMESPACES 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

Medium

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

Description

This EWI is added fort the WITH XMLNAMESPACES clause which is not supported in Snowflake SQL

Code Example

Input Code:

IN -> SqlServer_01.sql

WITH XMLNAMESPACES ('uri' as ns1)
SELECT ProductID as 'ns1:ProductID',
Name      as 'ns1:Name',
Color     as 'ns1:Color'
FROM Production.Product
WHERE ProductID = 316
FOR XML RAW, ELEMENTS XSINIL

Output Code:

OUT -> SqlServer_01.sql

--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0043 - WITH XMLNAMESPACES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
 XMLNAMESPACES ('uri' as VARIANT /*** SSC-FDM-TS0015 - DATA TYPE NS1 IS NOT SUPPORTED IN SNOWFLAKE ***/)
SELECT
ProductID AS "ns1:ProductID",
Name AS "ns1:Name",
Color AS "ns1:Color"
FROM
Production.Product
WHERE
ProductID = 316
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW, ELEMENTS XSINIL;

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

IN -> SqlServer_01.sql

CREATE  TABLE PRODUCT (ProductID INTEGER, Name VarChar(20), Color VarChar(20));
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

WITH XMLNAMESPACES ('uri' as ns1)  
SELECT ProductID as 'ns1:ProductID',  
       Name      as 'ns1:Name',  
       Color     as 'ns1:Color'  
FROM Product  
FOR XML RAW

Snowflake SQL

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

Related EWI

1. SSC-PRF-TS0001: Performance warning - recursion for CTE not checked. Might require a recursive keyword.

2. SSC-EWI-TS0044: FOR XML clause is not supported in Snowflake.

3. SSC-FDM-TS0015: Regexp_Substr Function only supports POSIX regular expressions.