MSCEWI4043
WITH XMLNAMESPACES is not supported in Snowflake.
Medium
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:
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, '&')
.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)+" />";
$$;
- 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 3mo ago