OPENXML UDF

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

1. XML: A VARCHAR that represents the readable content of the XML.

2. PATH: A varchar that contains the pattern of the nodes to be processed as rows.

UDF

CREATE OR REPLACE FUNCTION OPENXML_UDF(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("@"))
        {
            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);
$$

SQL Server

Query

DECLARE @idoc INT, @doc VARCHAR(1000);  
SET @doc ='  
<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">  
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>';  

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;  
 
SELECT *  FROM OPENXML (@idoc, '/ROOT/Customer',1)  
WITH (CustomerID  VARCHAR(10), ContactName VARCHAR(20)); 

Result

CustomerID  | ContactName
----------------------------+
VINET	    | Paul Henriot
LILAS	    | Carlos Gonzlez

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.

Query

CREATE OR REPLACE PROCEDURE ISOLATED_XML()
returns table(CustomerID varchar, ContactName varchar)
language sql
AS
$$
declare
    xml varchar;
    res resultset;
begin
    xml := '<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">  
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>';
    res := (SELECT
Left(value:Customer['@CustomerID'],10) as "CustomerID",  
Left(value:Customer['@ContactName'],20) as "ContactName"
FROM TABLE(OPEN_XML_UDF(:xml,'ROOT:Customer')));
    return table(res);
end;
$$;

CALL ISOLATED_XML();

Result

CustomerID  | ContactName
----------------------------+
VINET	    | Paul Henriot
LILAS	    | Carlos Gonzlez

Query

SET code = '<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
   </Order>  
</Customer>  
</ROOT>';
SELECT
Left(value:Customer['@CustomerID'],10) as "CustomerID",  
Left(value:Customer['@ContactName'],20) as "ContactName"
FROM TABLE(OPEN_XML_UDF($code,'ROOT:Customer'));

Result

CustomerID  | ContactName
----------------------------+
VINET	    | Paul Henriot

Related EWIs

1. MSCEWI1020: CUSTOM UDF INSERTED.

2. MSCEW4067: INVALID PARAMETERS IN OPENXML.