MSCEWI4043

WITH XMLNAMESPACES is not supported in Snowflake.
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
Snowflake SQL
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
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)+" />";
$$;
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.
MSCEWI4042
MSCEWI4044
Last modified 3mo ago