MSCEWI4042

OPENXML clause is not supported in Snowflake.

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    *
FROM OPENXML(@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:

SQL Server

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

Snowflake SQL

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);
$$

• If you need more support, you can email us at [email protected]