DAA-C01 Question 122
Single answerXMLA retail analytics team stores supplier product feeds as XML documents in a VARIANT column named RAW_XML in table STAGE_FEEDS. Each XML document contains many
- A
Use LATERAL FLATTEN on XMLGET(RAW_XML, 'product') and extract element values from the flattened VARIANT objects.
- B
Use COPY INTO with FILE_FORMAT TYPE = XML directly into relational columns SKU and PRICE so Snowflake automatically maps repeated XML elements to separate table rows.
- C
Convert RAW_XML to VARCHAR and use SPLIT_PART on '
' boundaries, then REGEXP_SUBSTR to parse SKU and price values. - D
Use PARSE_JSON on RAW_XML, then FLATTEN the resulting ARRAY to produce one row per product.
Show answer and explanation
Correct answer: A
Explanation
For XML in Snowflake, the recommended pattern is to load the XML into a VARIANT column and then query it using Snowflake's semi-structured data capabilities. Repeated XML nodes, such as multiple
- A. Correct.
Correct. In Snowflake, XML data is commonly stored in VARIANT and queried using semi-structured data functions. XMLGET can navigate to XML elements, and FLATTEN is the standard way to explode repeated nested elements into separate rows. This approach supports extracting values from each product element and, with an outer lateral join pattern when needed, can preserve rows even if some child elements are absent. This is the appropriate relationalization pattern for repeated XML nodes.
- B. Incorrect.
Incorrect. Snowflake does support loading XML, but it does not automatically map repeated XML elements directly into multiple relational target columns and rows during COPY INTO in the way described. XML is typically loaded into a semi-structured column such as VARIANT first, then queried and transformed with functions like XMLGET and FLATTEN.
- C. Incorrect.
Incorrect. Treating XML as raw text and parsing it with string functions or regular expressions is fragile and not a best practice for semi-structured data in Snowflake. It can break on formatting changes, namespaces, attributes, whitespace, nested elements, or escaped characters. Snowflake provides native XML handling that is more reliable and maintainable.
- D. Incorrect.
Incorrect. PARSE_JSON is for JSON text, not XML content. RAW_XML already contains XML in VARIANT form, and JSON parsing functions are not applicable to XML documents. The misconception is assuming all semi-structured formats can be processed interchangeably with JSON functions.