如何解析Azure Synapse中nvarchar列内嵌的XML数据？

先确认XML结构与命名空间 #

先明确你的XML字符串的命名空间类型，两种常见格式示例：

示例1（默认命名空间，无前缀）：

<Richiesta xmlns="http://schemas.example.com/2024/msp">
  <codiceMSP>U678202</codiceMSP>
</Richiesta>

示例2（带前缀的命名空间）：

<Richiesta xmlns:msp="http://schemas.example.com/2024/msp">
  <msp:codiceMSP>U678202</msp:codiceMSP>
</Richiesta>

配置Parse转换的步骤 #

1. 预处理XML字符串（可选但必要） #

如果SQL Server存储的XML包含HTML转义字符（比如<、>），先添加Derived Column转换，用以下表达式还原XML格式：

replace(replace(request, '&lt;', '<'), '&gt;', '>')

将输出列命名为cleanedRequest，后续用该列做解析操作。

2. 配置Parse转换 #

• 添加Parse转换，选择cleanedRequest（或原始request列）作为待解析列，输出格式选择XML
• 进入XML Settings，点击Namespaces下的Add按钮：
  • 针对示例1（默认命名空间）：输入XML中的命名空间URI（如http://schemas.example.com/2024/msp），自定义一个前缀（比如ns）
  • 针对示例2（带前缀）：直接填入XML里的前缀（msp）和对应的URI
• 进入Schema区域，手动添加目标节点配置：
  • 点击+ Add column，列名设为codiceMSP，数据类型选String
  • 在Path字段输入节点的完整路径：
    • 示例1路径：/ns:Richiesta/ns:codiceMSP
    • 示例2路径：/Richiesta/msp:codiceMSP
• 保存Parse转换，输出列默认命名为parseCleanedRequest（可自定义名称）

3. 提取为新列 #

添加Derived Column转换，创建MSP列，表达式直接引用解析后的节点值：

parseCleanedRequest.codiceMSP

常见问题排查 #

• 解析后无数据：检查XML路径是否正确，命名空间前缀与URI是否完全匹配，XML字符串是否有语法错误（可用isXml(cleanedRequest)函数验证）
• 命名空间配置无效：确保命名空间URI完全复制XML中xmlns的取值，不要遗漏任何字符或符号
• 嵌套节点解析：如果codiceMSP在多层嵌套节点下，路径要写全层级，比如/ns:Richiesta/ns:Dati/ns:codiceMSP

内容的提问来源于stack exchange，提问作者Porchetta17