Categories:

Semi-structured Data Functions (Parsing)

PARSE_XML

Interprets an input string as an XML document, producing an OBJECT value. If the input is NULL, the output is NULL.

See also:

CHECK_XML

Syntax

PARSE_XML( <string_expr> )

Arguments

string_expr

This expression should evaluate to a VARCHAR. The VARCHAR should contain valid XML.

Returns

The data type of the returned value is OBJECT. The OBJECT contains an internal representation of the XML.

Usage Notes

  • The content of every element in XML documents is text. PARSE_XML attempts to convert some XML data from text to native (Snowflake SQL) Data Types.

    • NUMERIC and BOOLEAN:

      PARSE_XML attempts to convert obviously numeric and Boolean values to the native representation in a way that printing these values back produces textually identical results. For example, when parsing decimal numbers, PARSE_XML attempts to preserve exactness of the representation by treating 123.45 as NUMBER(5,2), not as a DOUBLE. However:

      • Numbers in scientific notation (i.e. 1.2345e+02) or numbers that cannot be stored as fixed-point decimals due to range or scale limitations are stored as DOUBLE.

      • If the content of an XML element is a number with digits after the decimal point, then PARSE_XML might truncate trailing zeros.

    • TIMESTAMP, DATE, TIME, BINARY:

      Because XML does not represent values such as TIMESTAMP, DATE, TIME, or BINARY natively, these have to be represented as strings in XML. PARSE_XML does not automatically recognize these values; they are retained as strings, so users must convert the values from strings to native SQL data types if needed.

  • XML attributes are an unordered collection of name/value pairs. The PARSE_XML function does not necessarily preserve order. For example, converting text to XML and back to text might result in a string that contains the original information in a different order.

  • You might see changes in whitespace between elements when converting from string to XML.

Examples

CREATE TABLE xtab (v OBJECT);

INSERT INTO xtab SELECT PARSE_XML(column1) AS v
  FROM VALUES ('<a/>'), ('<a attr="123">text</a>'), ('<a><b>X</b><b>Y</b></a>');

SELECT * FROM xtab;

-------------------------+
           V             |
-------------------------+
 <a></a>                 |
 <a attr="123">text</a>  |
 <a><b>X</b><b>Y</b></a> |
-------------------------+
Back to top