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