Snowflake中从VARIANT存储的XML提取指定嵌套键属性值的方法
原查询返回NULL甚至报语法错的核心原因
- 语法错误+顶层过滤不匹配:原查询在
WHERE RAW_COLUMN_1:"@"::STRING = 'edge'后面误加了分号,直接截断SQL逻辑;同时实际存储的食谱类节点顶层标签是node,和筛选的edge条件完全不匹配,无有效记录 - 节点层级定位错误:
instructions内容嵌在<data>标签内部,不是node节点的直接子节点;原查询直接在顶层找名为instruction的节点,既拼写错误(和实际的instructions差了末尾的s)又跳过了嵌套层级 - 属性匹配条件错误:原查询过滤
XMLGET(RAW_COLUMN_1, 'label'):"$"::STRING = 'recipe',但实际标识节点类型的data节点对应的key是labelV,不是label
正确提取指定key嵌套XML数据的实现
Snowflake里处理这类带属性匹配的嵌套XML,最稳定的写法是用XPATH_GET_STRING函数,通过属性值定位目标节点,不依赖节点的排列顺序:
SELECT -- 匹配路径下key为instructions的节点,直接提取内部文本 XPATH_GET_STRING(RAW_COLUMN_1, '/node/data[@key="instructions"]/text()') AS recipe_instructions FROM "XML_DB_1"."PUBLIC"."XML_TABLE_1" WHERE -- 筛选顶层为node标签的记录 RAW_COLUMN_1:"@"::STRING = 'node' -- 筛选节点类型为recipe的记录 AND XPATH_GET_STRING(RAW_COLUMN_1, '/node/data[@key="labelV"]/text()') = 'recipe';
如果习惯使用之前接触过的XMLGET语法,也可以通过展开嵌套同名节点的方式实现,和上面的方法效果一致:
SELECT target_data:"$"::STRING AS recipe_instructions FROM "XML_DB_1"."PUBLIC"."XML_TABLE_1", -- 展开node下所有同名的data子节点 LATERAL FLATTEN(INPUT => RAW_COLUMN_1:data) AS data_list, LATERAL XMLGET(data_list.value, '') AS target_data WHERE RAW_COLUMN_1:"@"::STRING = 'node' -- 匹配key为instructions的节点 AND target_data:"@key"::STRING = 'instructions' -- 只保留食谱类型的节点 AND EXISTS ( SELECT 1 FROM LATERAL FLATTEN(INPUT => RAW_COLUMN_1:data) AS label_list, LATERAL XMLGET(label_list.value, '') AS label_data WHERE label_data:"@key"::STRING = 'labelV' AND label_data:"$"::STRING = 'recipe' );
注意:不要直接用XMLGET的索引参数固定取第几个data节点当instructions,一旦后续XML结构调整、节点顺序变动,会取到完全错误的内容,不适合生产场景使用。
内容的提问来源于stack exchange,提问作者Anirban Saha
相关产品推荐
相关产品推荐

