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.

Syntax¶

TO_XML( <expression> )

Copy

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 does not 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 does not 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 did not 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>
```
Copy
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>
```
Copy
For an ARRAY, each element of the array is in a tag pair similar to:
```
<e type="VARCHAR"> </e>
```
Copy
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>
```
Copy
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>
```
Copy

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 data:

CREATE TABLE xml2 (x OBJECT);
INSERT INTO xml2 (x)
  SELECT PARSE_XML('<note> <body>Sample XML</body> </note>');

Copy

Call the TO_XML() and TO_VARCHAR() functions:

SELECT x, TO_VARCHAR(x), TO_XML(x)
    FROM xml2;
+---------------------------+--------------------------------------+--------------------------------------+
| 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>                   |                                      |                                      |
+---------------------------+--------------------------------------+--------------------------------------+

Copy

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

The following 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 TABLE xml_03 (object_col_1 OBJECT);
INSERT INTO xml_03 (object_col_1)
    SELECT OBJECT_CONSTRUCT('key1', 'value1', 'key2', 'value2');

Copy

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"  |                                                                                                                   |
| }                   |                                                                                                                   |
+---------------------+-------------------------------------------------------------------------------------------------------------------+

Copy

The following 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 TABLE xml_04 (array_col_1 ARRAY);
INSERT INTO xml_04 (array_col_1)
    SELECT ARRAY_CONSTRUCT('v1', 'v2');

Copy

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"      |                                                                                              |
| ]           |                                                                                              |
+-------------+----------------------------------------------------------------------------------------------+

Copy

The following code inserts data that is in JSON format and then generates the corresponding XML:

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

Copy

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"    |                      |                                                                                                                         |
|   ]         |                      |                                                                                                                         |
| }           |                      |                                                                                                                         |
+-------------+----------------------+-------------------------------------------------------------------------------------------------------------------------+

Copy