xml文件不能直接加载到snowflake或bigquery，必须先用python（xmltodict/defusedxml）转为jsonl等支持格式，再按目标表结构展开嵌套、处理命名空间与空值，最后通过stage/copy或bq load导入。
XML 文件太大时，别直接用 COPY INTO 或 bq load
XML 不是 Snowflake 或 BigQuery 原生支持的加载格式。直接尝试 COPY INTO table FROM @stage FILE_FORMAT = (TYPE = 'XML') 会报错 Unsupported file format: XML；BigQuery 的 bq load 同样不接受 --source_format=XML。必须先转换为支持格式（如 JSON、CSV、Parquet），再入库。
常见错误现象：上传后数据全变成单字段大文本（XML string in one column），或解析失败导致空行/截断。
- Snowflake 中，
XMLPARSE只能处理已入库的 XML 字符串，不能用于原始文件加载阶段 - BigQuery 没有内置 XML 解析函数，
XML类型根本不存在 - 若 XML 含命名空间、CDATA、自闭合标签或混合内容（text + element），通用转换器易出错
用 Python + xmltodict 或 defusedxml 做预处理更可控
比起 shell 脚本或在线工具，Python 脚本能精确控制层级扁平化逻辑、空值处理和编码兼容性。推荐用 xmltodict（轻量）或 defusedxml（防 XXE 攻击，生产环境必需）。
关键点：不是“转成 JSON 就完事”，而是按目标表结构决定如何展开嵌套。例如，一个 <order><item><sku>A</sku></item><item><sku>B</sku></item></order> 应该拆成两行 item 记录，而非存为数组字段。
import xmltodict
import json
<p>with open('orders.xml', 'rb') as f:
xml_data = f.read()</p><h1>防止恶意实体注入（尤其读不可信源时）</h1><p>from defusedxml.minidom import parseString
dom = parseString(xml_data) # 验证合法性后再解析</p><p>data = xmltodict.parse(xml_data, force_list={'item': True}) # 确保 item 总是 list
json_lines = []
for order in data.get('orders', {}).get('order', []):
for item in order.get('item', []):
json_lines.append({
'order_id': order.get('@id'),
'sku': item.get('sku'),
'qty': int(item.get('qty', 0))
})</p><h1>写入 NDJSON（每行一个 JSON 对象），供 Snowflake/BigQuery 加载</h1><p>with open('items.jsonl', 'w') as f:
for line in json_lines:
f.write(json.dumps(line, ensure_ascii=False) + '\n')</p>Snowflake 中用 STAGE + FILE_FORMAT + COPY INTO 分三步走
不要把 XML 或中间 JSONL 文件放在本地路径用 file:// 加载 —— Snowflake 无法访问客户端磁盘。必须先 PUT 到内部 stage，再 COPY INTO。
-
CREATE OR REPLACE STAGE my_xml_stage URL = '@%my_db.my_schema.my_table';是错的 —— stage 是独立对象，不是表附属物 - JSONL 文件需指定
TYPE = 'JSON'和STRIP_OUTER_ARRAY = FALSE（默认就是 FALSE，但显式写上更安全） - 字段映射必须用
$1:order_id::STRING这类路径表达式，不能依赖自动列名推断
PUT file:///path/to/items.jsonl @my_xml_stage; COPY INTO my_table FROM (SELECT $1:order_id::STRING, $1:sku::STRING, $1:qty::INT FROM @my_xml_stage/items.jsonl) FILE_FORMAT = (TYPE = 'JSON');
BigQuery 加载 JSONL 时注意 schema 推断陷阱
用 bq load --source_format=NEWLINE_DELIMITED_JSON 时，如果首几行缺失某字段（比如有的 item 没 qty），BQ 可能将该字段设为 NULLABLE STRING，后续含数字的行就会加载失败。
正确做法：始终用 --schema 显式声明，或先建好表再用 --autodetect=false。
- XML 中的
<price>19.99</price>和<price></price>在转 JSON 后分别是"price": "19.99"和"price": null—— 若 schema 定义price: NUMERIC，后者会触发类型冲突 - 时间字段如
<created>2024-03-15T10:30:00Z</created>必须在 JSON 中保持 ISO 格式，并在 BQ schema 中设为TIMESTAMP
真正麻烦的不是技术链路，而是 XML 设计本身：没有 schema 约束、字段可选性模糊、多级嵌套深度不一 —— 这些都会让解析脚本变成 case-by-case 维护项。
