MSCEWI4042
OPENXML clause is not supported in Snowflake.
Medium
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customers')
SELECT *
FROM
-- ** MSC-ERROR - MSCEWI4042 - OPENXML clause is not supported in SnowFlake**
-- OPENXML(@idoc, '/ROOT/Customers')
;
- 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 PROCEDURE OpenXMLTest
AS
BEGIN
DECLARE @idoc INT
DECLARE @data varchar(1000)
set @data='
<ROOT>
<Customer CustomerID="VINET" ContactName="Michael Jordan">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="1115" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="1115" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="LeBron James">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="1120" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @data
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID VARCHAR(10),
ContactName VARCHAR(20))
EXEC sp_xml_removedocument @idoc
END
CREATE OR REPLACE PROCEDURE OpenXMLTest() RETURNS STRING LANGUAGE JAVASCRIPT
AS
$$
var EXEC = (sql,params)=>snowflake.execute({sqlText:sql,binds:params});
//
var idoc;
var data;
data=`
<ROOT>
<Customer CustomerID="MJ" ContactName="Michael Jordan">
<Order CustomerID="MJ" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="1115" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="1115" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LJ" ContactName="LeBron James">
<Order CustomerID="LJ" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="1120" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>`;
//Create an internal representation of the XML document.
idoc = data;
// Execute a SELECT statement that uses the OPENXML rowset provider.
// Just added the temporary table to make it easier to test
EXEC(`CREATE OR REPLACE TEMPORARY TABLE OPENXMLTEST_RESULTS AS SELECT
Left(value:Customer['@CustomerID'],10) as "CustomerID",
Left(value:Customer['@ContactName'],20) as "ContactName"
FROM TABLE(OPENXML(?,'ROOT:Customer'))`,[idoc]);
$$;
CREATE OR REPLACE FUNCTION OPENXML(XML VARCHAR, PATH VARCHAR) RETURNS TABLE(VALUE VARIANT) LANGUAGE SQL 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 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);
$$
- 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