OPENXML_UDF

Definition

This user-defined function (UDF) generates a query from an XML reading.

OPENXML_UDF(XML VARCHAR, PATH VARCHAR)

Parameters

XML VARCHAR

The XML content as a varchar.

PATH VARCHAR

The path of the node to extract.

Returns

Returns a table with the data generated by the XML reading.

Usage example

Input:

SELECT * FROM TABLE(OPENXML_UDF('<iceCreamOrders>
    <order>
        <customer customerID="CUST001" contactName="Test ABC">
            <iceCreamOrder orderID="ORD001" employeeID="101" orderDate="2023-05-15T14:30:00">
                <iceCreamDetail productID="001" quantity="2"/>
                <iceCreamDetail productID="003" quantity="1"/>
            </iceCreamOrder>
        </customer>
    </order>
    <order>
        <customer customerID="CUST002" contactName="Test XYZ">
            <iceCreamOrder orderID="ORD002" employeeID="102" orderDate="2023-06-20T12:45:00">
                <iceCreamDetail productID="005" quantity="3"/>
                <iceCreamDetail productID="007" quantity="2"/>
            </iceCreamOrder>
        </customer>
    </order>
</iceCreamOrders>
', 'iceCreamOrders:order'));

Output:

	Value
1	{ "order": { "$name": "order", "customer": [ { "customer": { "$name": "customer", "@contactName": "Test ABC", "@customerID": "CUST001", "iceCreamOrder": [ { "iceCreamOrder": { "$name": "iceCreamOrder", "@employeeID": 101, "@orderDate": "2023-05-15T14:30:00", "@orderID": "ORD001", "iceCreamDetail": [ { "iceCreamDetail": { "$name": "iceCreamDetail", "@productID": "001", "@quantity": 2 } }, { "iceCreamDetail": { "$name": "iceCreamDetail", "@productID": "003", "@quantity": 1 } } ] } } ] } } ] } }
2	{ "order": { "$name": "order", "customer": [ { "customer": { "$name": "customer", "@contactName": "Test XYZ", "@customerID": "CUST002", "iceCreamOrder": [ { "iceCreamOrder": { "$name": "iceCreamOrder", "@employeeID": 102, "@orderDate": "2023-06-20T12:45:00", "@orderID": "ORD002", "iceCreamDetail": [ { "iceCreamDetail": { "$name": "iceCreamDetail", "@productID": "005", "@quantity": 3 } }, { "iceCreamDetail": { "$name": "iceCreamDetail", "@productID": "007", "@quantity": 2 } } ] } } ] } } ] } }