结合使用 XMLGET 以及 GET 函数来遍历xml文档中的嵌套对象。
前者帮助获取当前标记下的整个标记对象，而后者允许查询当前标记中的属性和常规值。

SELECT
    -- <dept> (root)
    GET(xmldata, '@dept_id')::integer as dept_id,
    GET(xmldata, '@dept_name')::string as dept_name,
    -- <dept>.<employee>
    GET(XMLGET(xmldata, 'employee'), '@empname')::string as employee_name,
    -- <dept>.<employee>.<address>
    GET(XMLGET(XMLGET(xmldata, 'employee'), 'address'), '@addr1')::string as address_1
FROM test_XML_copy;

这将产生：

+---------+-----------+---------------+-----------+                             
| DEPT_ID | DEPT_NAME | EMPLOYEE_NAME | ADDRESS_1 |
|---------+-----------+---------------+-----------|
|       1 | Marketing | a             | 123 abc   |
|       2 | Sales     | b             | 456 def   |
| ...     | ...       | ...           | ...       |
+---------+-----------+---------------+-----------+

您的示例数据显示子标记没有重复，但是如果它们确实重复（例如多个） employee 在每个 dept )那么 FLATTEN 可以先用来生产一个 employee 每行和上述方法可以重新应用。或者，如果它是标记结构的固定形式，并且它们总是有序的，那么您可以在中使用示例号 XMLGET 指向每个对象（隐式默认值为0，第一个对象）。
将文档分解为每个员工和每个地址一行的示例内部标记：

SELECT
xmldata:"@dept_id"::integer as dept_id,
xmldata:"@dept_name"::string as dept_name,
emp.value:"@empname"::string as employee_name,
addr.value:"@addr1"::string as address_1
FROM
test_XML_copy,
LATERAL FLATTEN(xmldata:"$") emp,
LATERAL FLATTEN(emp.value:"$") addr
WHERE emp.value:"@" = 'employee' AND addr.value:"@" = 'address';

（这将产生与上述类似的结果，例如op问题中提供的示例）
注意：还可以将路径语法用于 $ 以及 @ 用于导航结构而不是嵌套函数的字符，但这些字符依赖于输入数据结构的严格顺序：

-- See outer / inner structures in 'JSON' form
SELECT
    xmldata:"@" dept_tag,
    xmldata:"$" dept_tag_contents
FROM test_XML_copy;

-- Sample equivalent query using path expressions, relying on ordering:
SELECT
    xmldata:"@dept_id",
    xmldata:"@dept_name",
    xmldata:"$"[1]."@empname",
    xmldata:"$"[1]."$"[1]."@addr1"
FROM test_XML_copy;

要从子节点提取数据，需要使用横向展平来转换数据，例如展平 <employee> 元素并获取 emp_name 属性：

select
       XMLGET( xmldata, '@dept_id' ):"$"::string AS dept_id
     , XMLGET( xmldata, '@dept_name' ):"$"::string AS dept_name
     , XMLGET( emp.value, '@empname' ):"$"::string as empname
from
    test_XML_copy
    ,  lateral FLATTEN(test_XML_copy.xmldata:"$") emp
  where emp.value like '<employee>%'
  order by dept_id, empname;

本文提供了一些有关在snowflake中查询xml的非常有用的信息：https://community.snowflake.com/s/article/querying-nested-xml-in-snowflake