MSCEWI4044
FOR XML clause is not supported in Snowflake.
Medium
SELECT TOP 1 LastName
FROM AdventureWorks2019.Person.Person
FOR XML AUTO;
SELECT TOP 1 LastName
FROM AdventureWorks2019.Person.Person
-- ** MSC-ERROR - MSCEWI4043 - FOR XML clause is not supported in SnowFlake **
--FOR XML AUTO
;
- 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, '&')
.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)+` />`;
$$;
- For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.
Last modified 4mo ago