This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Medium
Description
This EWI is added fort the OPENXML clause which is not supported in Snowflake SQL
Code Example
Input Code:
SELECT*FROMOPENXML(@idoc, '/ROOT/Customers')
Output Code:
SELECT*FROM-- ** MSC-ERROR - MSCEWI4042 - OPENXML clause is not supported in SnowFlake**-- OPENXML(@idoc, '/ROOT/Customers');
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:
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);
$$
