XML Parsing with CROSS APPLY

I have been working on this XML, which is nested in a column of a table in SQL Server.

The following code:

WITH XMLNAMESPACES('http://www.sat.gob.mx/cfd/4' AS cfdi, 
                    'http://www.sat.gob.mx/Pagos20' AS pago20, 
                    'http://www.sat.gob.mx/TimbreFiscalDigital' AS tfd)
  SELECT
      prx3_3.*
  FROM
(
 SELECT
    UUID,
    UUIDRelaciona = pd.value('@UUID', 'NVARCHAR(64)'),
    c.value('(@Total)', 'DECIMAL(26, 8)') AS TotalEgresos,
    x.value('(@Base)', 'DECIMAL(26,8)') AS Base,
    x.value('(@Importe)', 'DECIMAL(16,4)') AS ImporteTraslados
FROM [dbo].[XML]

    --El casteo ocurre aqui
    CROSS APPLY(SELECT CAST(XML AS XML)) A(TheXml)

    --Se llama a la funcion .nodes para retornar la tabla derivada de la consulta
    CROSS APPLY TheXml.nodes('/cfdi:Comprobante/cfdi:CfdiRelacionados/cfdi:CfdiRelacionado') AS i(pd)
    CROSS APPLY TheXml.nodes('/cfdi:Comprobante') AS C(c)
    CROSS APPLY TheXml.nodes('/cfdi:Comprobante/cfdi:Conceptos/cfdi:Concepto/cfdi:Impuestos/cfdi:Traslados/cfdi:Traslado') B(x) 
) prx3_3

This is my XML:

<?xml version="1.0" encoding="iso-8859-1"?>
    <cfdi:Comprobante xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sat.gob.mx/cfd/4 http://www.sat.gob.mx/sitio_internet/cfd/4/cfdv40.xsd" xmlns:cfdi="http://www.sat.gob.mx/cfd/4" Version="4.0" Serie="NC" Folio="1500" Fecha="2023-06-06T14:58:38" Sello="FKE==" FormaPago="99" NoCertificado="00001000000504344657" Certificado="MIIF4=" SubTotal="9006.47" Moneda="MXN" Total="10447.51" TipoDeComprobante="E" Exportacion="01" MetodoPago="PUE" LugarExpedicion="52160">
        <cfdi:CfdiRelacionados TipoRelacion="01">
            <cfdi:CfdiRelacionado UUID="8D6A947B-55D5-7F47-9541-4DFEDFADFD76" />
            <cfdi:CfdiRelacionado UUID="22F27E3B-938F-A747-873F-CB12D8C3D618" />
            <cfdi:CfdiRelacionado UUID="02FC251C-D156-0F4D-B9D6-0FD3A099CD0E" />
            <cfdi:CfdiRelacionado UUID="36FAACBA-6134-2640-AEB0-07F2C3913E94" />
            <cfdi:CfdiRelacionado UUID="016E4B16-2A41-1746-B5A1-FD34722965F6" />
        </cfdi:CfdiRelacionados>
        <cfdi:Emisor Rfc="CVE160307HY1" Nombre="CADENA 24" RegimenFiscal="601" />
        <cfdi:Receptor Rfc="SON990511MI0" Nombre="SONIGAS" DomicilioFiscalReceptor="37500" RegimenFiscalReceptor="601" UsoCFDI="G02" />
        <cfdi:Conceptos>
            <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT" Descripcion="Nota de crédito de Interés moratorio 22314 01/feb./2023" ValorUnitario="320.16" Importe="320.16" ObjetoImp="02">
                <cfdi:Impuestos>
                    <cfdi:Traslados>
                    <cfdi:Traslado Base="320.16" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="51.23" />
                    </cfdi:Traslados>
                </cfdi:Impuestos>
            </cfdi:Concepto>
            <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT" Descripcion="Nota de crédito de Interés moratorio 22988 01/mar./2023" ValorUnitario="1723.86" Importe="1723.86" ObjetoImp="02">
                <cfdi:Impuestos>
                    <cfdi:Traslados>
                        <cfdi:Traslado Base="1723.86" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="275.82" />
                    </cfdi:Traslados>
                </cfdi:Impuestos>
            </cfdi:Concepto>
            <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT" Descripcion="Nota de crédito de Interés moratorio 25094 11/abr./2023" ValorUnitario="1794.99" Importe="1794.99" ObjetoImp="02">
                <cfdi:Impuestos>
                    <cfdi:Traslados>
                        <cfdi:Traslado Base="1794.99" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="287.20" />
                    </cfdi:Traslados>
                </cfdi:Impuestos>
            </cfdi:Concepto>
            <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT" Descripcion="Nota de crédito de Interés moratorio 25773 01/may./2023" ValorUnitario="3198.69" Importe="3198.69" ObjetoImp="02">
                <cfdi:Impuestos>
                    <cfdi:Traslados>
                        <cfdi:Traslado Base="3198.69" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="511.79" />
                    </cfdi:Traslados>
                </cfdi:Impuestos>
            </cfdi:Concepto>
            <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT" Descripcion="Nota de crédito de Interés moratorio 26356 01/jun./2023" ValorUnitario="1968.77" Importe="1968.77" ObjetoImp="02">
                <cfdi:Impuestos>
                    <cfdi:Traslados>
                        <cfdi:Traslado Base="1968.77" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="315.00" />
                    </cfdi:Traslados>
                </cfdi:Impuestos>
            </cfdi:Concepto>
        </cfdi:Conceptos>
        <cfdi:Impuestos TotalImpuestosTrasladados="1441.04">
            <cfdi:Traslados>
                <cfdi:Traslado Base="9006.47" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="1441.04" />
            </cfdi:Traslados>
        </cfdi:Impuestos>
        <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" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="1.1" UUID="126F9C73-DD92-2943-AA07-320EB2955100" FechaTimbrado="2023-06-06T15:00:30" RfcProvCertif="SCD110105654" SelloCFD="FKEz==" NoCertificadoSAT="00001000000502000436" SelloSAT="XLREB==" />
        </cfdi:Complemento>
    </cfdi:Comprobante>

The result I hope for is the following table:

UUID	UUIDRelaciona	TotalEgresos	Base	ImporteTraslado
126F9C73-DD92-2943-AA07-320EB2955100	8D6A947B-55D5-7F47-9541-4DFEDFADFD76	10447.51	320.17	51.23
126F9C73-DD92-2943-AA07-320EB2955100	22F27E3B-938F-A747-873F-CB12D8C3D618	10447.51	1723.86	275.82
126F9C73-DD92-2943-AA07-320EB2955100	02FC251C-D156-0F4D-B9D6-0FD3A099CD0E	10447.51	1794.99	287.20
126F9C73-DD92-2943-AA07-320EB2955100	36FAACBA-6134-2640-AEB0-07F2C3913E94	10447.51	3198.69	315.00

Solution

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (UUID UNIQUEIDENTIFIER PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT @tbl (UUID, xmldata) VALUES
('126F9C73-DD92-2943-AA07-320EB2955100', N'<cfdi:Comprobante xmlns:cfdi="sat.gob.mx/cfd/4"
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:schemaLocation="http://www.sat.gob.mx/cfd/4 http://www.sat.gob.mx/sitio_internet/cfd/4/cfdv40.xsd"
                  MetodoPago="PUE" LugarExpedicion="52160" Total="10447.51">
    <cfdi:CfdiRelacionados TipoRelacion="01">
        <cfdi:CfdiRelacionado UUID="8D6A947B-55D5-7F47-9541-4DFEDFADFD76"/>
        <cfdi:CfdiRelacionado UUID="22F27E3B-938F-A747-873F-CB12D8C3D618"/>
        <cfdi:CfdiRelacionado UUID="02FC251C-D156-0F4D-B9D6-0FD3A099CD0E"/>
        <cfdi:CfdiRelacionado UUID="36FAACBA-6134-2640-AEB0-07F2C3913E94"/>
        <cfdi:CfdiRelacionado UUID="016E4B16-2A41-1746-B5A1-FD34722965F6"/>
    </cfdi:CfdiRelacionados>
    <cfdi:Emisor Rfc="CVE160307HY1" Nombre="CADENA 24" RegimenFiscal="601"/>
    <cfdi:Receptor Rfc="SON990511MI0" Nombre="SONIGAS"
                   DomicilioFiscalReceptor="37500" RegimenFiscalReceptor="601"
                   UsoCFDI="G02"/>
    <cfdi:Conceptos>
        <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT"
                       Descripcion="Nota de crédito de Interés moratorio 22314 01/feb./2023"
                       ValorUnitario="320.16" Importe="320.16" ObjetoImp="02">
            <cfdi:Impuestos>
                <cfdi:Traslados>
                    <cfdi:Traslado Base="320.16" Impuesto="002"
                                   TipoFactor="Tasa" TasaOCuota="0.160000"
                                   Importe="51.23"/>
                </cfdi:Traslados>
            </cfdi:Impuestos>
        </cfdi:Concepto>
        <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT"
                       Descripcion="Nota de crédito de Interés moratorio 22988 01/mar./2023"
                       ValorUnitario="1723.86" Importe="1723.86" ObjetoImp="02">
            <cfdi:Impuestos>
                <cfdi:Traslados>
                    <cfdi:Traslado Base="1723.86" Impuesto="002"
                                   TipoFactor="Tasa" TasaOCuota="0.160000"
                                   Importe="275.82"/>
                </cfdi:Traslados>
            </cfdi:Impuestos>
        </cfdi:Concepto>
        <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT"
                       Descripcion="Nota de crédito de Interés moratorio 25094 11/abr./2023"
                       ValorUnitario="1794.99" Importe="1794.99" ObjetoImp="02">
            <cfdi:Impuestos>
                <cfdi:Traslados>
                    <cfdi:Traslado Base="1794.99" Impuesto="002"
                                   TipoFactor="Tasa" TasaOCuota="0.160000"
                                   Importe="287.20"/>
                </cfdi:Traslados>
            </cfdi:Impuestos>
        </cfdi:Concepto>
        <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT"
                       Descripcion="Nota de crédito de Interés moratorio 25773 01/may./2023"
                       ValorUnitario="3198.69" Importe="3198.69" ObjetoImp="02">
            <cfdi:Impuestos>
                <cfdi:Traslados>
                    <cfdi:Traslado Base="3198.69" Impuesto="002"
                                   TipoFactor="Tasa" TasaOCuota="0.160000"
                                   Importe="511.79"/>
                </cfdi:Traslados>
            </cfdi:Impuestos>
        </cfdi:Concepto>
        <cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT"
                       Descripcion="Nota de crédito de Interés moratorio 26356 01/jun./2023"
                       ValorUnitario="1968.77" Importe="1968.77" ObjetoImp="02">
            <cfdi:Impuestos>
                <cfdi:Traslados>
                    <cfdi:Traslado Base="1968.77" Impuesto="002"
                                   TipoFactor="Tasa" TasaOCuota="0.160000"
                                   Importe="315.00"/>
                </cfdi:Traslados>
            </cfdi:Impuestos>
        </cfdi:Concepto>
    </cfdi:Conceptos>
    <cfdi:Impuestos TotalImpuestosTrasladados="1441.04">
        <cfdi:Traslados>
            <cfdi:Traslado Base="9006.47" Impuesto="002" TipoFactor="Tasa"
                           TasaOCuota="0.160000" Importe="1441.04"/>
        </cfdi:Traslados>
    </cfdi:Impuestos>
</cfdi:Comprobante>');
-- DDL and sample data population, end

WITH XMLNAMESPACES(DEFAULT 'sat.gob.mx/cfd/4')
SELECT t.UUID
    , x.value('@UUID', 'UNIQUEIDENTIFIER') AS UUIDRelaciona
    , x.value('(/Comprobante/@Total)[1]', 'DECIMAL(10,2)') AS TotalEgresos
    --, seq.pos
    , c.value('@Base', 'DECIMAL(10,2)') AS Base
    , c.value('@Importe', 'DECIMAL(10,2)') AS ImporteTraslado
FROM @tbl AS t
CROSS APPLY (SELECT CAST(xmldata AS XML)) t1(TheXml)
CROSS APPLY TheXml.nodes('/Comprobante/CfdiRelacionados/CfdiRelacionado') t2(x)
CROSS APPLY (SELECT t2.x.value('let $n := . return count(../*[. << $n[1]]) + 1','INT') AS pos
         ) AS seq
CROSS APPLY TheXml.nodes('/Comprobante/Conceptos/Concepto[sql:column("seq.pos")]/Impuestos/Traslados/Traslado') AS t3(c);

Output

UUID	UUIDRelaciona	TotalEgresos	Base	ImporteTraslado
126F9C73-DD92-2943-AA07-320EB2955100	8D6A947B-55D5-7F47-9541-4DFEDFADFD76	10447.51	320.16	51.23
126F9C73-DD92-2943-AA07-320EB2955100	22F27E3B-938F-A747-873F-CB12D8C3D618	10447.51	1723.86	275.82
126F9C73-DD92-2943-AA07-320EB2955100	02FC251C-D156-0F4D-B9D6-0FD3A099CD0E	10447.51	1794.99	287.20
126F9C73-DD92-2943-AA07-320EB2955100	36FAACBA-6134-2640-AEB0-07F2C3913E94	10447.51	3198.69	511.79
126F9C73-DD92-2943-AA07-320EB2955100	016E4B16-2A41-1746-B5A1-FD34722965F6	10447.51	1968.77	315.00