DAA-C01 Question 121
Single answerXMLA retail company stores inbound supplier messages as XML documents in a VARIANT column named SRC_XML in table RAW_SUPPLIER_FEED. Each document contains repeated
- A
Use PARSE_XML to convert the XML text to VARIANT if needed, then use XMLGET to navigate to the purchaseOrder and LATERAL FLATTEN over the repeated item elements, extracting scalar values from each item.
- B
Use GET_PATH directly on the raw XML text column and cast the result to ARRAY so FLATTEN can iterate over item nodes without parsing.
- C
Cast the XML VARIANT to OBJECT and use dot notation on purchaseOrder.item because XML is stored as a native OBJECT with arrays for repeated tags.
- D
Use REGEXP_SUBSTR repeatedly against the XML text to extract purchase order number, SKU, and quantity because Snowflake cannot flatten repeated XML elements from VARIANT.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowflake's native XML handling for semi-structured data: parse XML with PARSE_XML, navigate elements with XMLGET, and expand repeated elements with LATERAL FLATTEN. This approach is robust and aligns with Snowflake best practices for XML stored in VARIANT. XML in Snowflake is not manipulated exactly like JSON, so relying on direct OBJECT/dot-notation assumptions can lead to incorrect logic. Likewise, operating on raw XML text without parsing, or using regular expressions for element extraction, is unreliable and not suitable for production analytics workflows. Snowflake documentation on semi-structured data, PARSE_XML, XMLGET, and FLATTEN supports this pattern.
- A. Correct.
Correct. In Snowflake, XML data is typically parsed with PARSE_XML and stored in VARIANT. To work with repeated XML elements, a common pattern is to navigate to the relevant node with XMLGET and then use LATERAL FLATTEN to expand repeating child elements into rows. Scalar values can then be extracted from each flattened element. This is the appropriate semi-structured processing pattern for XML in Snowflake.
- B. Incorrect.
Incorrect. GET_PATH operates on semi-structured data such as VARIANT, OBJECT, or ARRAY, not on unparsed raw XML text. XML must first be parsed. Also, XML nodes should not be assumed to be directly castable to ARRAY in a reliable way for repeated-element traversal.
- C. Incorrect.
Incorrect. Although XML is stored in VARIANT, Snowflake does not expose parsed XML as if it were ordinary JSON-style OBJECT structures where dot notation reliably handles repeated tags as arrays. XML has its own internal representation and functions such as XMLGET are used to access elements. Assuming direct OBJECT semantics is a common misconception.
- D. Incorrect.
Incorrect. Regular expressions may appear to work for simple samples, but they are fragile for nested or variable XML and are not a best practice for structured XML processing in Snowflake. Snowflake does support parsing and querying XML from VARIANT, including flattening repeated elements, so regex-based extraction is unnecessary and error-prone.