Блог

Главная - Вопросы по программированию - Преобразование XML в таблицу SQL Server (документ cfdi)

Преобразование XML в таблицу SQL Server (документ cfdi)

#sql #sql-server #xml

#sql #sql-сервер #xml

Вопрос:

Я пытаюсь преобразовать XML в таблицу SQL Server.

XML выглядит следующим образом (пример):

 declare @XML as XML

set @XML = '<cfdi:Comprobante xmlns:cfdi="http://www.sat.gob.mx/cfd/3" xmlns:nomina12="http://www.sat.gob.mx/nomina12" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Fecha="2020-02-15">
  <cfdi:Emisor Rfc="12345"  />
  <cfdi:Conceptos>
    <cfdi:Concepto ClaveProdServ="84111505" Cantidad="1" />
  </cfdi:Conceptos>
  <cfdi:Complemento>
    <tfd:TimbreFiscalDigital xmlns:tfd="http://www.sat.gob.mx/TimbreFiscalDigital" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://www.sat.gob.mx/TimbreFiscalDigital http://www.sat.gob.mx/sitio_internet/cfd/timbrefiscaldigital/TimbreFiscalDigitalv11.xsd" 
    Version="1.1" UUID="28C9D7DC-A9CB-4663-9606-3B7E37D922D4" FechaTimbrado="2020-04-15T18:59:18"  />
    <nomina12:Nomina Version="1.2" TipoNomina="O" FechaPago="2020-04-15" FechaInicialPago="2020-04-01" FechaFinalPago="2020-04-15" >
      <nomina12:Emisor RegistroPatronal="Z2935172101" />
      <nomina12:Receptor Curp="XYZ"  />
      <nomina12:Percepciones TotalSueldos="6807.63" TotalGravado="5865.48" TotalExento="942.15">
        <nomina12:Percepcion TipoPercepcion="001" Clave="P001" Concepto="Sueldo Normal" ImporteGravado="4887.90" ImporteExento="0.00" />
        <nomina12:Percepcion TipoPercepcion="005" Clave="P013" Concepto="Fondo Ahorro Empresa" ImporteGravado="0.00" ImporteExento="342.15" />
      </nomina12:Percepciones>
      <nomina12:Deducciones TotalOtrasDeducciones="1282.01" TotalImpuestosRetenidos="610.94">
        <nomina12:Deduccion TipoDeduccion="001" Clave="D003" Concepto="IMSS" Importe="70.40" />
        <nomina12:Deduccion TipoDeduccion="001" Clave="D034" Concepto="Cuota IMSS RCV" Importe="58.66" />
      </nomina12:Deducciones>
    </nomina12:Nomina>
  </cfdi:Complemento>
</cfdi:Comprobante>'

Мой код SQL выглядит следующим образом:

 ;WITH XMLNAMESPACES(
        'http://www.sat.gob.mx/cfd/3' as cfdi,
        'http://www.w3.org/2001/XMLSchema-instance' as xsi,
        'http://www.sat.gob.mx/TimbreFiscalDigital' as tfd,
        'http://www.sat.gob.mx/TimbreFiscalDigital' as schemaLocation, 
'http://www.sat.gob.mx/nomina12' as nomina12)
SELECT
    xmldata.value('(@Fecha)', 'varchar(20)') AS fecha_elaboracion,
    xmldata.value('(cfdi:Emisor/@Rfc)[1]', 'varchar(20)') AS rfc_emisor,
    xmldata1.value('(@UUID)', 'varchar(100)') AS UUID,
    xmldata1.value('(@FechaTimbrado)', 'varchar(50)') AS fecha_timbre,
    xmldata2.value('(@FechaPago)', 'nvarchar(50)') AS fecha_pago,
    xmldata2.value('(@FechaInicialPago)', 'nvarchar(50)') AS fecha_inicio,
    xmldata2.value('(@FechaFinalPago)', 'nvarchar(50)') AS fecha_final
FROM
    (SELECT @XML AS x) AS x1
CROSS APPLY 
    x.nodes('/cfdi:Comprobante') AS a(xmldata)
CROSS APPLY 
    xmldata.nodes('cfdi:Complemento/tfd:TimbreFiscalDigital') AS a1(xmldata1)
OUTER APPLY 
    xmldata.nodes('cfdi:Complemento/tfd:TimbreFiscalDigital/nomina12:Nomina') AS a2(xmldata2);

Я получаю информацию для первых узлов, но когда я хочу получить узел «nomina12: Nomina», я получил нулевое ЗНАЧЕНИЕ, какая-то идея, что не так?

Заранее спасибо за вашу помощь.

Ответ №1:

Несколько очищенная версия.

Я удалил ненужные объявления пространства имен и скорректировал правильные типы данных.

SQL

 DECLARE @xml XML =
N'<cfdi:Comprobante xmlns:cfdi="http://www.sat.gob.mx/cfd/3"
                  xmlns:nomina12="http://www.sat.gob.mx/nomina12"
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  Fecha="2020-02-15">
    <cfdi:Emisor Rfc="12345"/>
    <cfdi:Conceptos>
        <cfdi:Concepto ClaveProdServ="84111505" Cantidad="1"/>
    </cfdi:Conceptos>
    <cfdi:Complemento>
        <tfd:TimbreFiscalDigital xmlns:tfd="http://www.sat.gob.mx/TimbreFiscalDigital"
                                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                 xsi:schemaLocation="http://www.sat.gob.mx/TimbreFiscalDigital http://www.sat.gob.mx/sitio_internet/cfd/timbrefiscaldigital/TimbreFiscalDigitalv11.xsd"
                                 Version="1.1"
                                 UUID="28C9D7DC-A9CB-4663-9606-3B7E37D922D4"
                                 FechaTimbrado="2020-04-15T18:59:18"/>
        <nomina12:Nomina Version="1.2" TipoNomina="O" FechaPago="2020-04-15"
                         FechaInicialPago="2020-04-01"
                         FechaFinalPago="2020-04-15">
            <nomina12:Emisor RegistroPatronal="Z2935172101"/>
            <nomina12:Receptor Curp="XYZ"/>
            <nomina12:Percepciones TotalSueldos="6807.63" TotalGravado="5865.48"
                                   TotalExento="942.15">
                <nomina12:Percepcion TipoPercepcion="001" Clave="P001"
                                     Concepto="Sueldo Normal"
                                     ImporteGravado="4887.90"
                                     ImporteExento="0.00"/>
                <nomina12:Percepcion TipoPercepcion="005" Clave="P013"
                                     Concepto="Fondo Ahorro Empresa"
                                     ImporteGravado="0.00"
                                     ImporteExento="342.15"/>
            </nomina12:Percepciones>
            <nomina12:Deducciones TotalOtrasDeducciones="1282.01"
                                  TotalImpuestosRetenidos="610.94">
                <nomina12:Deduccion TipoDeduccion="001" Clave="D003"
                                    Concepto="IMSS" Importe="70.40"/>
                <nomina12:Deduccion TipoDeduccion="001" Clave="D034"
                                    Concepto="Cuota IMSS RCV" Importe="58.66"/>
            </nomina12:Deducciones>
        </nomina12:Nomina>
    </cfdi:Complemento>
</cfdi:Comprobante>';

;WITH XMLNAMESPACES('http://www.sat.gob.mx/cfd/3' as cfdi
    , 'http://www.sat.gob.mx/TimbreFiscalDigital' as tfd
    , 'http://www.sat.gob.mx/nomina12' as nomina12)
SELECT xmldata.value('@Fecha', 'DATE') AS fecha_elaboracion
    , xmldata.value('(cfdi:Emisor/@Rfc)[1]', 'VARCHAR(20)') AS rfc_emisor
    , xmldata1.value('@UUID', 'UNIQUEIDENTIFIER') AS UUID
    , xmldata1.value('@FechaTimbrado', 'VARCHAR(50)') AS fecha_timbre
    , xmldata2.value('@FechaPago', 'DATE') AS fecha_pago
    , xmldata2.value('@FechaInicialPago', 'DATE') AS fecha_inicio
    , xmldata2.value('@FechaFinalPago', 'DATE') AS fecha_final
FROM @xml.nodes('/cfdi:Comprobante') AS a(xmldata)
OUTER APPLY xmldata.nodes('cfdi:Complemento/tfd:TimbreFiscalDigital') AS a1(xmldata1)
OUTER APPLY xmldata.nodes('cfdi:Complemento/nomina12:Nomina') AS a2(xmldata2);

Вывод

  ------------------- ------------ -------------------------------------- --------------------- ------------ -------------- ------------- 
| fecha_elaboracion | rfc_emisor |                 UUID                 |    fecha_timbre     | fecha_pago | fecha_inicio | fecha_final |
 ------------------- ------------ -------------------------------------- --------------------- ------------ -------------- ------------- 
| 2020-02-15        |      12345 | 28C9D7DC-A9CB-4663-9606-3B7E37D922D4 | 2020-04-15T18:59:18 | 2020-04-15 | 2020-04-01   | 2020-04-15  |
 ------------------- ------------ -------------------------------------- --------------------- ------------ -------------- -------------

Ответ №2:

Вы ссылаетесь на неправильный путь в своем outer apply заявлении:

 xmldata.nodes('cfdi:Complemento/nomina12:Nomina') AS a2(xmldata2);

А также вот упрощенная версия вашего запроса:

 WITH XMLNAMESPACES ('http://www.sat.gob.mx/cfd/3' AS cfdi
        , 'http://www.w3.org/2001/XMLSchema-instance' AS xsi
        , 'http://www.sat.gob.mx/TimbreFiscalDigital' AS tfd
        , 'http://www.sat.gob.mx/TimbreFiscalDigital' AS schemaLocation
        , 'http://www.sat.gob.mx/nomina12' AS nomina12
    )
SELECT
    xmldata.value('(/cfdi:Comprobante/@Fecha)[1]', 'varchar(20)')                AS fecha_elaboracion
    , xmldata.value('(/cfdi:Comprobante/cfdi:Emisor/@Rfc)[1]', 'varchar(20)') AS rfc_emisor
    , xmldata.value('(/cfdi:Comprobante/cfdi:Complemento/tfd:TimbreFiscalDigital/@UUID)[1]', 'varchar(100)')             AS UUID
    , xmldata.value('(/cfdi:Comprobante/cfdi:Complemento/tfd:TimbreFiscalDigital/@FechaTimbrado)[1]', 'varchar(50)')     AS fecha_timbre
    , xmldata.value('(/cfdi:Comprobante/cfdi:Complemento/nomina12:Nomina/@FechaPago)[1]', 'nvarchar(50)')     AS fecha_pago
    , xmldata.value('(/cfdi:Comprobante/cfdi:Complemento/nomina12:Nomina/@FechaInicialPago)[1]', 'nvarchar(50)') AS fecha_inicio
    , xmldata.value('(/cfdi:Comprobante/cfdi:Complemento/nomina12:Nomina/@FechaFinalPago)[1]', 'nvarchar(50)')   AS fecha_final
FROM
    (SELECT @XML AS xmldata) AS xmldata