Categories:: Conversion functions , Semi-structured and structured data functions (Cast)

TO_XML¶

Converts a VARIANT to a VARCHAR that contains an XML representation of the value. If the input is NULL, the result is also NULL.

See also:: CHECK_XML, PARSE_XML, XMLGET

Syntax¶

TO_XML( <expression> )

Arguments¶

expression: An expression that evaluates to a VARIANT or that can be cast to a VARIANT.

Returns¶

The data type of the returned value is VARCHAR.

Usage notes¶

Common uses for this function include:
- Generating a string that contains an XML-formatted value that matches an originally inserted XML-formatted value.
- Converting a semi-structured value (which doesn’t necessarily need to have been formatted as XML originally) into an XML-formatted value. For example, you can use TO_XML to generate an XML-compatible representation of a value that was originally formatted as JSON.
If the input expression doesn’t evaluate to a VARIANT, Snowflake implicitly casts the result of the expression to a VARIANT. Because all other Snowflake data types can be cast to VARIANT, this means that a value of any data type can be passed to TO_XML and converted to an XML-formatted string. (The GEOGRAPHY data type is a partial exception; to call TO_XML with a value of type GEOGRAPHY, you must explicitly cast the GEOGRAPHY value to VARIANT.)
If the value didn’t originate as XML, then Snowflake generates XML-compatible tags. These tags may use the type attribute to specify the Snowflake data type of the tag’s contents. Below are examples of tags generated by Snowflake.

The outermost tag pair is similar to the following:
```
<SnowflakeData type="OBJECT"> </SnowflakeData>
```
The data type specified in the type attribute of the tag can vary.

For an OBJECT, each key-value pair’s tags are based on the key. For example:
```
<key1 type="VARCHAR">value1</key1>
```
For an ARRAY, each element of the array is in a tag pair similar to:
```
<e type="VARCHAR"> </e>
```
Here is a complete example of the XML for a simple OBJECT that contains two key-value pairs:
```
<SnowflakeData type="OBJECT">
    <key1 type="VARCHAR">value1</key1>
    <key2 type="VARCHAR">value2</key2>
</SnowflakeData>
```
Here is a complete example of the XML for a simple ARRAY that contains two VARCHAR values:
```
<SnowflakeData type="ARRAY">
    <e type="VARCHAR">v1</e>
    <e type="VARCHAR">v2</e>
</SnowflakeData>
```

Examples¶

This example shows how to use the function if you’ve loaded XML-formatted data into an OBJECT by calling PARSE_XML.

Create a table and insert data:

CREATE OR REPLACE TABLE xml_02 (x OBJECT);

INSERT INTO xml_02 (x)
  SELECT PARSE_XML('<note> <body>Sample XML</body> </note>');

Call the TO_XML and TO_VARCHAR functions:

SELECT x, TO_VARCHAR(x), TO_XML(x) FROM xml_02;

+---------------------------+--------------------------------------+--------------------------------------+
| X                         | TO_VARCHAR(X)                        | TO_XML(X)                            |
|---------------------------+--------------------------------------+--------------------------------------|
| <note>                    | <note><body>Sample XML</body></note> | <note><body>Sample XML</body></note> |
|   <body>Sample XML</body> |                                      |                                      |
| </note>                   |                                      |                                      |
+---------------------------+--------------------------------------+--------------------------------------+

You can also call the TO_XML function with data that did not originate as XML-formatted data, as shown in the examples below.

The following example creates a simple OBJECT and then generates the corresponding XML. The XML output contains information about the data types of the values in the key-value pairs, as well as the data type of the overall value (OBJECT).

CREATE OR REPLACE TABLE xml_03 (object_col_1 OBJECT);

INSERT INTO xml_03 (object_col_1)
  SELECT OBJECT_CONSTRUCT('key1', 'value1', 'key2', 'value2');

SELECT object_col_1, TO_XML(object_col_1)
  FROM xml_03;

+---------------------+-------------------------------------------------------------------------------------------------------------------+
| OBJECT_COL_1        | TO_XML(OBJECT_COL_1)                                                                                              |
|---------------------+-------------------------------------------------------------------------------------------------------------------|
| {                   | <SnowflakeData type="OBJECT"><key1 type="VARCHAR">value1</key1><key2 type="VARCHAR">value2</key2></SnowflakeData> |
|   "key1": "value1", |                                                                                                                   |
|   "key2": "value2"  |                                                                                                                   |
| }                   |                                                                                                                   |
+---------------------+-------------------------------------------------------------------------------------------------------------------+

The following example creates a simple ARRAY and then generates the corresponding XML. The XML output contains information about the data types of the array elements, as well as the data type of the overall value (ARRAY).

CREATE OR REPLACE TABLE xml_04 (array_col_1 ARRAY);

INSERT INTO xml_04 (array_col_1)
  SELECT ARRAY_CONSTRUCT('v1', 'v2');

SELECT array_col_1, TO_XML(array_col_1)
  FROM xml_04;

+-------------+----------------------------------------------------------------------------------------------+
| ARRAY_COL_1 | TO_XML(ARRAY_COL_1)                                                                          |
|-------------+----------------------------------------------------------------------------------------------|
| [           | <SnowflakeData type="ARRAY"><e type="VARCHAR">v1</e><e type="VARCHAR">v2</e></SnowflakeData> |
|   "v1",     |                                                                                              |
|   "v2"      |                                                                                              |
| ]           |                                                                                              |
+-------------+----------------------------------------------------------------------------------------------+

The following example inserts data that is in JSON format and then generates the corresponding XML.

CREATE OR REPLACE TABLE xml_05 (json_col_1 VARIANT);

INSERT INTO xml_05 (json_col_1)
  SELECT PARSE_JSON(' { "key1": ["a1", "a2"] } ');

SELECT json_col_1,
       TO_JSON(json_col_1),
       TO_XML(json_col_1)
  FROM xml_05;

+-------------+----------------------+-------------------------------------------------------------------------------------------------------------------------+
| JSON_COL_1  | TO_JSON(JSON_COL_1)  | TO_XML(JSON_COL_1)                                                                                                      |
|-------------+----------------------+-------------------------------------------------------------------------------------------------------------------------|
| {           | {"key1":["a1","a2"]} | <SnowflakeData type="OBJECT"><key1 type="ARRAY"><e type="VARCHAR">a1</e><e type="VARCHAR">a2</e></key1></SnowflakeData> |
|   "key1": [ |                      |                                                                                                                         |
|     "a1",   |                      |                                                                                                                         |
|     "a2"    |                      |                                                                                                                         |
|   ]         |                      |                                                                                                                         |
| }           |                      |                                                                                                                         |
+-------------+----------------------+-------------------------------------------------------------------------------------------------------------------------+