MSCEWI4042

OPENXML clause is not supported in Snowflake.

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:

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

Last updated