Snowflake查询XML节点属性返回null的解决方法

AI 小北

2026-8-30

Snowflake XML节点属性读取返回null问题修复方案

问题根因

属性读取返回null是因为XMLGET()函数的设计用途是提取当前节点下的子元素节点，不支持读取节点自身挂载的属性。你之前传入@date、@reseller-id作为XMLGET的参数时，函数会尝试查找标签名为@date的子元素，这类节点不存在自然返回null。
其余子节点字段能正常返回，是因为你用XMLGET读取transaction下嵌套子标签的写法本身是正确的。

正确实现代码

Snowflake中XML节点的自身属性统一存储在节点对象的@字段下，直接通过路径引用即可读取，无需调用XMLGET，修正后的完整SQL如下：

SELECT 
    retail.value:"@"."date"::STRING as "Transaction Date",
    retail.value:"@"."reseller-id"::STRING as "Reseller ID",
    XMLGET( retail.value, 'transactionid' ):"$"::STRING as "Transaction ID",
    XMLGET( retail.value, 'eventName' ):"$"::STRING as "Event Name",
    XMLGET( retail.value, 'numberOfPurchasedTickets' ):"$"::NUMBER as "Number of Purchased Tickets",
    XMLGET( retail.value, 'totalAmount' ):"$"::NUMBER as "Total Amount",
    XMLGET( retail.value, 'salesChannel' ):"$"::STRING as "Sales Channel",
    XMLGET( retail.value, 'officeLocation' ):"$"::STRING as "Office Location",
    XMLGET( retail.value, 'dateCreated' ):"$"::STRING as "Date Created",
    XMLGET( customer.value, 'firstName' ):"$"::STRING as "First Name",
    XMLGET( customer.value, 'lastName' ):"$"::STRING as "Last Name"
FROM stage_thirdparty retail_xml,
LATERAL FLATTEN(to_array(retail_xml.src_xml:"$" )) retail,
LATERAL FLATTEN(to_array(retail.value:"$" )) customer
where GET( customer.value, '@') = 'customer'
;