Important Notice: Migration of Documentation Website
Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:
For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].
Thank you for your understanding.
Applies to
Some parts in the output code are omitted for clarity reasons.
Description
This custom UDF is added to process a rowset view over an XML document. This would be used for declarations in because it works as a rowset provider.
Optional parameters and different node types are not supported in this version of the UDF. The element node is processed by default.
Custom UDF overloads
Parameters
XML: A VARCHAR that represents the readable content of the XML.
PATH: A varchar that contains the pattern of the nodes to be processed as rows.
Transact-SQL
Snowflake
The following example is isolated into a stored procedure because environment variables only support 256 bytes of storage, and the XML demo code uses more than that limit.
CREATE OR REPLACE FUNCTION OPENXML_UDF(XML VARCHAR, PATH VARCHAR)
RETURNS TABLE(VALUE VARIANT)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT VALUE from TABLE(FLATTEN(input=>XML_JSON_SIMPLE(PARSE_XML(XML)), path=>PATH))
$$;
CREATE OR REPLACE FUNCTION XML_JSON_SIMPLE(XML VARIANT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
function toNormalJSON(xmlJSON) {
var finalres = {};
var name=xmlJSON['@'];
var res = {};
finalres[name] = res;
for(var key in xmlJSON)
{
if (key == "@")
{
res["$name"] = xmlJSON["@"];
}
else if (key == "$") {
continue;
}
else if (key.startsWith("@"))
{
// This is an attribute
res[key]=xmlJSON[key];
}
else
{
var elements = xmlJSON['$']
var value = xmlJSON[key];
res[key] = [];
if (Array.isArray(value))
{
for(var elementKey in value)
{
var currentElement = elements[elementKey];
var fixedElement = toNormalJSON(currentElement);
res[key].push(fixedElement);
}
}
else if (value === 0)
{
var fixedElement = toNormalJSON(elements);
res[key].push(fixedElement);
}
}
}
return finalres;
}
return toNormalJSON(XML);
$$;
