XML_JSON_SIMPLE

Definition

This user-defined function (UDF) generates an object with the information from executing a reading from an XML value.

XML_JSON_SIMPLE(XML VARIANT)

Parameters

XML VARIANT

The XML to be read.

Returns

Returns an object with the processed information from the XML.

Usage example

Input:

SELECT XML_JSON_SIMPLE(TO_VARIANT(PARSE_XML('<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>
')));

Output:

{
  "iceCreamOrders": {
    "$name": "iceCreamOrders",
    "order": [
      {
        "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
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "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
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}