MSCEWI4043
WITH XMLNAMESPACES is not supported in Snowflake.
Last updated
WITH XMLNAMESPACES is not supported in Snowflake.
Last updated
This is a deprecated version of the SnowConvert documentation, please visit the official site .
Medium
This EWI is added fort the clause which is not supported in Snowflake SQL
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
-- ** 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
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 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, '&')
.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 "<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, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, ''');
};
}
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