使用CROSS APPLY解析SQL Server嵌套XML的技术咨询

CFDI格式XML示例 #

<?xml version="1.0" encoding="UTF-8"?>
<cfdi:Comprobante xmlns:cfdi="http://www.sat.gob.mx/cfd/3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sat.gob.mx/cfd/3 http://www.sat.gob.mx/sitio_internet/cfd/3/cfdv33.xsd" Version="3.3" Serie="A" Folio="123" Fecha="2024-05-20T10:30:00" UUID="ABC12345-6789-0DEF-GHIJ-KLMNOPQRSTUV" Total="1500.00">
  <cfdi:Complemento>
    <tfd:TimbreFiscalDigital xmlns:tfd="http://www.sat.gob.mx/TimbreFiscalDigital" xsi:schemaLocation="http://www.sat.gob.mx/TimbreFiscalDigital http://www.sat.gob.mx/sitio_internet/cfd/TimbreFiscalDigital/TimbreFiscalDigitalv11.xsd" Version="1.1" UUID="ABC12345-6789-0DEF-GHIJ-KLMNOPQRSTUV" FechaTimbrado="2024-05-20T10:35:00"/>
  </cfdi:Complemento>
  <cfdi:CfdiRelacionados>
    <cfdi:CfdiRelacionado UUIDRelacionado="XYZ98765-4321-0CBA-FEDC-BA9876543210" TipoRelacion="01"/>
  </cfdi:CfdiRelacionados>
  <cfdi:Conceptos>
    <cfdi:Concepto ClaveProdServ="10101501" Cantidad="1" ClaveUnidad="H87" Descripcion="Servicio Consultor" ValorUnitario="1000.00" Importe="1000.00">
      <cfdi:Impuestos>
        <cfdi:Traslados>
          <cfdi:Traslado Base="1000.00" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.16" Importe="160.00"/>
        </cfdi:Traslados>
      </cfdi:Impuestos>
    </cfdi:Concepto>
    <cfdi:Concepto ClaveProdServ="10101502" Cantidad="1" ClaveUnidad="H87" Descripcion="Servicio Soporte" ValorUnitario="300.00" Importe="300.00">
      <cfdi:Impuestos>
        <cfdi:Traslados>
          <cfdi:Traslado Base="300.00" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.16" Importe="48.00"/>
        </cfdi:Traslados>
      </cfdi:Impuestos>
    </cfdi:Concepto>
  </cfdi:Conceptos>
  <cfdi:Impuestos TotalImpuestosTrasladados="208.00" TotalEgresos="1500.00"/>
</cfdi:Comprobante>

调整后的SQL解析代码 #

WITH XMLNAMESPACES (
    'http://www.sat.gob.mx/cfd/3' AS cfdi,
    'http://www.sat.gob.mx/TimbreFiscalDigital' AS tfd
)
SELECT
    -- 优先提取官方有效UUID（来自TimbreFiscalDigital），无则回退到Comprobante节点
    COALESCE(
        tfd.value('@UUID', 'UNIQUEIDENTIFIER'),
        comprobante.value('@UUID', 'UNIQUEIDENTIFIER')
    ) AS UUID,
    -- 关联CFDI的UUID，无关联时返回NULL
    relacionado.value('@UUIDRelacionado', 'UNIQUEIDENTIFIER') AS UUIDRelaciona,
    -- 总支出金额
    comprobante.value('(cfdi:Impuestos/@TotalEgresos)[1]', 'DECIMAL(18,2)') AS TotalEgresos,
    -- 税项计算基数
    traslado.value('@Base', 'DECIMAL(18,2)') AS Base,
    -- 转移税金额
    traslado.value('@Importe', 'DECIMAL(18,2)') AS ImporteTraslado
FROM
    CFDI_Documents -- 替换为你的实际表名
CROSS APPLY
    XMLData.nodes('/cfdi:Comprobante') AS N1(comprobante)
LEFT JOIN
    XMLData.nodes('/cfdi:Comprobante/cfdi:CfdiRelacionados/cfdi:CfdiRelacionado') AS N2(relacionado) ON 1=1
CROSS APPLY
    comprobante.nodes('cfdi:Conceptos/cfdi:Concepto/cfdi:Impuestos/cfdi:Traslados/cfdi:Traslado') AS N3(traslado)
CROSS APPLY
    comprobante.nodes('cfdi:Complemento/tfd:TimbreFiscalDigital') AS N4(tfd);

目标查询结果示例 #

关键逻辑说明 #

• 命名空间声明：通过XMLNAMESPACES指定CFDI和电子签名的官方命名空间，避免节点定位失败。
• UUID提取逻辑：优先使用TimbreFiscalDigital节点的UUID（SAT认可的唯一有效标识），兼容部分旧文档仅在Comprobante节点存UUID的情况。
• 关联UUID处理：用LEFT JOIN适配无关联CFDI的场景，保证结果完整性。
• 嵌套节点解析：多层CROSS APPLY逐层拆解Conceptos、Impuestos、Traslados节点，确保每个税项的明细字段被正确提取。
• 数值精度控制：使用DECIMAL(18,2)保证财务数据的精度符合业务要求。

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