Categories:

Conversion Functions , Semi-structured Data Functions (Cast)

TO_XML

Converts any VARIANT value to a string containing the XML representation of the value. If the input is NULL, the result is also NULL.

Examples

This shows how to use the function:

Create a table and data:

CREATE TABLE xml1 (x VARIANT);
INSERT INTO xml1 (x)
  SELECT TO_VARIANT(
    '<note> <body>Sample XML</body> </note>');

Now run two queries. The first shows the output from TO_XML, and the second shows the output as varchar for comparison:

SELECT TO_XML(x) FROM xml1;
SELECT TO_VARCHAR(x) FROM xml1;

Output:

+--------------------------------------------------------------------------------------------------------------+
| TO_XML(X)                                                                                                    |
|--------------------------------------------------------------------------------------------------------------|
| <SnowflakeData type="VARCHAR">&lt;note&gt; &lt;body&gt;Sample XML&lt;/body&gt; &lt;/note&gt;</SnowflakeData> |
+--------------------------------------------------------------------------------------------------------------+
+----------------------------------------+
| TO_VARCHAR(X)                          |
|----------------------------------------|
| <note> <body>Sample XML</body> </note> |
+----------------------------------------+