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

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)+` />`;
$$;

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