Links

MSCEWI4044

FOR XML clause is not supported in Snowflake.

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
Snowflake 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
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"/>
*/
CREATE TABLE TEMPTABLE (Ref INT, Des NVARCHAR(100), Qty INT);
INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2);
-- FOR XML
SELECT LISTAGG(FOR_XML_NOELEMENTS(OBJECT_CONSTRUCT(*),'TempTable'))
FROM TempTable
/*
returns
<TempTable DES="Normal" QTY="1" REF="100001" /><TempTable DES="Foobar" QTY="1" REF="100002" /><TempTable DES="Hello World" QTY="2" REF="100003" />
*/
-- FOR XML RAW
SELECT LISTAGG(FOR_XML_NOELEMENTS(OBJECT_CONSTRUCT(*)))
FROM TempTable
/*
returns
<row DES="Normal" QTY="1" REF="100001" /><row DES="Foobar" QTY="1" REF="100002" /><row DES="Hello World" QTY="2" REF="100003" />
*/
create or replace function FOR_XML(O VARIANT,XMLTAG TEXT) RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
function OBJtoXML(obj) {
var xml = '';
for (var prop in obj) {
xml += obj[prop] instanceof Array ? '' : "<" + prop + ">";
if (obj[prop] instanceof Array) {
for (var array in obj[prop]) {
xml += "<" + prop + ">";
xml += OBJtoXML(new Object(obj[prop][array]));
xml += "</" + prop + ">";
}
} else if (typeof obj[prop] == "object") {
xml += OBJtoXML(new Object(obj[prop]));
} else {
xml += obj[prop];
}
xml += obj[prop] instanceof Array ? '' : "</" + prop + ">";
}
var xml = xml.replace(/<\/?[0-9]{1,}>/g, '');
return xml;
}
return `<${XMLTAG}>`+OBJtoXML(O)+`</${XMLTAG}>`;
$$;
create or replace function FOR_XML_NOELEMENTS(O VARIANT) RETURNS STRING LANGUAGE SQL AS
$$
FOR_XML_NOELEMENTS(O,'row')
$$;
create or replace function FOR_XML_NOELEMENTS(O VARIANT, XMLTAG TEXT) RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
if (!String.prototype.encodeXML) {
String.prototype.encodeXML = function () {
return this.replace(/&/g, '&amp;')
.replace(/</g, '&lt;')
.replace(/>/g, '&gt;')
.replace(/"/g, '&quot;')
.replace(/'/g, '&apos;');
};
}
function OBJtoXML(obj) {
var xml = '';
for (var prop in obj) {
if (obj[prop] instanceof Array) {
// Not supported
xml += "";
} else if (typeof obj[prop] == "object") {
// Not supported
xml += "";
} else {
xml += prop + "=\"" + obj[prop].toString().encodeXML() + "\" ";
}
}
var xml = xml.replace(/<\/?[0-9]{1,}>/g, '');
return xml;
}
return `<${XMLTAG} `+OBJtoXML(O)+` />`;
$$;