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:
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, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, ''');
};
}
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)+` />`;
$$;If you need more support, you can email us at [email protected]
Last updated