MSCEWI4043

WITH XMLNAMESPACES 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 fort the WITH XMLNAMESPACES clause which is not supported in Snowflake SQL

Code Example

Input Code:

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:

-- ** MSC-ERROR - MSCEWI4043 - WITH XMLNAMESPACES is not supported in SnowFlake **
--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

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

CREATE TEMPORARY TABLE PRODUCT (ProductID INTEGER, Name VarChar, Color VarChar);
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');

select listagg(FOR_XML_NOELEMENTS(object_construct(*),object_construct('ns1','url'))) from (
SELECT ProductID as "ns1:ProductID",  
       Name      as "ns1:Name",  
       Color     as "ns1:Color"
FROM Product );


--Rquired UDFs

create or replace function FOR_XML(O VARIANT) 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 "<row "+OBJtoXML(O)+"</row>";
$$;

create or replace function FOR_XML(O VARIANT,NSMAP VARIANT) RETURNS STRING LANGUAGE JAVASCRIPT AS
$$
function addNS() {
    var res="";
    for (var ns in NSMAP)
    {
        res+=` xmlns:${ns}="${NSMAP[ns]}" `;
    }
    return res;
}
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 "<row"+addNS()+">"+OBJtoXML(O)+"</row>";
$$;


create or replace function FOR_XML_NOELEMENTS(O VARIANT) 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 "<row "+OBJtoXML(O)+" />";
$$;



create or replace function FOR_XML_NOELEMENTS(O VARIANT, NSMAP VARIANT) 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 addNS() {
    var res="";
    for (var ns in NSMAP)
    {
        res+=` xmlns:${ns}="${NSMAP[ns]}" `;
    }
    return res;
}
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 "<row "+addNS()+OBJtoXML(O)+" />";
$$;

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