Hi @peter_ticker ,

Can you try this SQL snippet (refer to the attachment).

WITH xml_data AS (
SELECT '<ROOT><Message><Attribute>1</Attribute><Attribute>2</Attribute></Message><Message><Attribute>3</Attribute></Message><Message><Attribute>5</Attribute><Attribute>6</Attribute></Message></ROOT>' AS xml_string
),
parsed_xml AS (
SELECT
xpath(xml_string, 'ROOT/Message/Attribute/text()') AS attributes
FROM xml_data
)
SELECT
explode(attributes) AS attribute_value
FROM parsed_xml;


However, this approach may not work for complex and nested schemas. 

When you are dealing with large and complex schema, I personally recommend either to convert to json or make use of external libraries like Maven spark-xml (com.databricks:spark-xml_2.12:0.16.0) with predefined schema.

Parsing using Maven Library:

1. Predefine the schema
2. Read the datafile as a dataframe with predefined schema - just schema is captured
3. Flatten it node level
4. Finally select the attributes that you wanted from the file. 

I tried different approaches and settled with this as a best fit for my use case. This works even for very complex nested structure as well.

Let me know for anything, else please mark as accepted solution.