CODEWITHSUNDEEP
sqlsql-serverxml
Juan Avalos
Juan Avalos

Reputation: 13

Convert XML to SQL Server table (cfdi document)

I'm trying to convert a XML to a SQL Server table.

The XML is the following (sample):

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>'

My SQL code is the following:

;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);

I'm getting information for the first nodes but when I want to get the node "nomina12:Nomina", I got a null VALUE, some idea what's wrong?

Thanks in advance for your help.

Upvotes: 1

Views: 915

Answers (2)

eshirvana
eshirvana

Reputation: 24568

You are refering to the wrong path in your outer apply statement:

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

And also here is the simplified version of your query:

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

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22182

Somewhat cleaned up version.

I removed not needed namespace declarations, and adjusted proper data types.

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);

Output

+-------------------+------------+--------------------------------------+---------------------+------------+--------------+-------------+
| 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  |
+-------------------+------------+--------------------------------------+---------------------+------------+--------------+-------------+

Upvotes: 1

Related Questions