How to get values from a column of type image that contains XML in SQL Server?-CodePudding

I want to get the values Total and TipoDeComprobante of the tag name cfdi:Comprobante, currently in my table the column where the XML is stored is of type image so I do the following query to get the value of the XML, I tried to do some substring but I have not been successful, could you help me?

Query to obtain the xml

select cast(cast(xml as varbinary(max)) as varchar(max)) as column_name
from [tb_cfdi]  
where uuid = 'f425cd6d-ed30-4a0d-8135-8dc7229b79ff' ;

Result

<?xml version="1.0" encoding="utf-8"?>
<cfdi:Comprobante xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" Version="3.3" Serie="PRUEBAS-SEBASTIAN" Folio="SEBASTIANCANCELACIONSD-27" Fecha="2022-05-23T18:42:38" Sello="dUxav04E0XcbOhMsXjXDlWbFlZdcXqOk/R8DHLd3TILZ7gg5/aw T gI9yMLpVLvPkeEm5 A  QWHi0I/26TVTlsx6ejFWxXTEdewmarCuSrRO24CSFPz2dLX6ojy5Eg6QGTV0EQiS3LRtLsnTyqXH2QE7Ne Y7cqXXEcXWZ63AIAkkWY4U9uQu1/3F0jiHFfMCf4lBuSgDQ8c8LniBKoAsFoTySZmyhJv4h2koz1bE0Yl6Rvrr0NNlmYSH SGqbkYVsYKM9fbHIDJbNXosRqIGlugOlLIT2499j3gQfTn7d3sdQLtVqn4tcqaDGx2Du2dm1XFJRwT7aB62btNVW1Q==" FormaPago="99" NoCertificado="30001000000400002325" Certificado="MIIFdjCCA16gAwIBAgIUMzAwMDEwMDAwMDA0MDAwMDIzMjUwDQYJKoZIhvcNAQELBQAwggErMQ8wDQYDVQQDDAZBQyBVQVQxLjAsBgNVBAoMJVNFUlZJQ0lPIERFIEFETUlOSVNUUkFDSU9OIFRSSUJVVEFSSUExGjAYBgNVBAsMEVNBVC1JRVMgQXV0aG9yaXR5MSgwJgYJKoZIhvcNAQkBFhlvc2Nhci5tYXJ0aW5lekBzYXQuZ29iLm14MR0wGwYDVQQJDBQzcmEgY2VycmFkYSBkZSBjYWRpejEOMAwGA1UEEQwFMDYzNzAxCzAJBgNVBAYTAk1YMRkwFwYDVQQIDBBDSVVEQUQgREUgTUVYSUNPMREwDwYDVQQHDAhDT1lPQUNBTjERMA8GA1UELRMIMi41LjQuNDUxJTAjBgkqhkiG9w0BCQITFnJlc3BvbnNhYmxlOiBBQ0RNQS1TQVQwHhcNMTkwNTI5MTgwNDM0WhcNMjMwNTI5MTgwNDM0WjCBnTEYMBYGA1UEAxMPWEFJTUUgV0VJUiBST0pPMRgwFgYDVQQpEw9YQUlNRSBXRUlSIFJPSk8xGDAWBgNVBAoTD1hBSU1FIFdFSVIgUk9KTzEWMBQGA1UELRMNV0VSWDYzMTAxNlMzMDEbMBkGA1UEBRMSV0VSWDYzMTAxNkhKQ1JKTTA4MRgwFgYDVQQLEw9YQUlNRSBXRUlSIFJPSk8wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCwSjRVq5oohPioYAWcUoxVAvudA2MqKDBwX/GukHngSLtqMZ6b5I/JIhnDyqauugRYaQRW7FH jxoijWR340zYhhgea2HYfE7WDUQNlraqtZ9re OX5RX3KQA4sPw3pcVMcUbiCDr3vVBVaWtEaAaIsh1LxPgwWQ2egFarmMkHHEUTLkISe2WqrOFKd3PJ9vgj6h4 EF0 C3qKt55pRsLTh9r828Mo7E7AROaSJK5XG/x/5xE5sYyV2kqCFIs11xdfTn00FEmUgK caW2lKYj9KqSuKu8UyJcOdrk2LhZlHhJO16LXrTsItJZPhJJ03FYi4 w0TNY7Eg87BC38nz5NAgMBAAGjHTAbMAwGA1UdEwEB/wQCMAAwCwYDVR0PBAQDAgbAMA0GCSqGSIb3DQEBCwUAA4ICAQCF9 3Q0bUnTVREjva/R2fNjnoksrHUwviYkRGOTfQeJbOF76gcz1aqkMpuhk9uvbnxLtNXTrpJZaW/9f7aVSIEP7x9u5XcikC5NOw4g0r/8uhCbeF41yIIAdCufg22YSMyfzAtzmW96tO1Q/PYaloCpEjtiWcufSVpmOFP/oQszWP8g4z3XG/DDPrhvO8Lk2ZghfsCuhi4KvNnBUZOn7Vm4kadt3QIfb4 gCkMn4uA0GLCIGWH/yvF4dQdc6if1ev/1FtGHe7CP54e PuOm8JjBFkzfXNiXIZq8NdnUNnwEUl zeXm1H4qog4WC/sceOtUVbRQy9sUYeX13 1yNtRQxGMebD tsTvY4xtwqjMw7TeOcLe27rJsPAV62vFIsL8HlDuYNC3mZJzg40pxlPjqt xhQAXM3MOYfQq 89t 4Cr4xaglLZ2NHAY7MZk0SN62c7f8yL4EA81Yg6LeIgDei3ZiNXcLeFKtCvDwbuIoD9Oqane7QH/jfOrIlRRxHeGb7Nmob PXe7BN9VYqQ0/dOSPRKol0DLRRpwA/2Qn8nbq3nl0ZCbJYfC/8psh/hUAkPUXPRH2 JoWORVzKjBDZ5FaLxV9t5q3rV/YeMVuS9qqzjs1/RvM5aa4cLh3Cc6S1vojBy1QitiGwRxzy4SHkF69dyxnQbyUVOMkuBiMyWg==" SubTotal="200.0" Moneda="AMD" TipoCambio="1" Total="200.16" TipoDeComprobante="I" MetodoPago="PPD" LugarExpedicion="20000" xmlns:cfdi="http://www.sat.gob.mx/cfd/3" xsi:schemaLocation="http://www.sat.gob.mx/cfd/3 http://www.sat.gob.mx/sitio_internet/cfd/3/cfdv33.xsd">
    <cfdi:Emisor Rfc="WERX631016S30" Nombre="XAIME WEIR ROJO" RegimenFiscal="605"/>
    <cfdi:Receptor Rfc="EKU9003173C9" Nombre="ESCUELA KEMPER URGATE" UsoCFDI="G03"/>
    <cfdi:Conceptos>
        <cfdi:Concepto ClaveProdServ="50211503" Cantidad="1" ClaveUnidad="H87" Unidad="Pieza" Descripcion="Cigarros" ValorUnitario="200.0" Importe="200.0">
            <cfdi:Impuestos>
                <cfdi:Traslados>
                    <cfdi:Traslado Base="1" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="0.16"/>
                </cfdi:Traslados>
                <cfdi:Retenciones>
                    <cfdi:Retencion Base="1" Impuesto="001" TipoFactor="Tasa" TasaOCuota="0.100000" Importe="0.00"/>
                    <cfdi:Retencion Base="1" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.106666" Importe="0.00"/>
                </cfdi:Retenciones>
            </cfdi:Impuestos>
        </cfdi:Concepto>
    </cfdi:Conceptos>
    <cfdi:Impuestos TotalImpuestosRetenidos="0.00" TotalImpuestosTrasladados="0.16">
        <cfdi:Retenciones>
            <cfdi:Retencion Impuesto="001" Importe="0.00"/>
            <cfdi:Retencion Impuesto="002" Importe="0.00"/>
        </cfdi:Retenciones>
        <cfdi:Traslados>
            <cfdi:Traslado Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="0.16"/>
        </cfdi:Traslados>
    </cfdi:Impuestos>
    <cfdi:Complemento>
        <tfd:TimbreFiscalDigital xsi:schemaLocation="http://www.sat.gob.mx/TimbreFiscalDigital http://www.sat.gob.mx/sitio_internet/cfd/TimbreFiscalDigital/TimbreFiscalDigitalv11.xsd" Version="1.1" UUID="f425cd6d-ed30-4a0d-8135-8dc7229b79ff" FechaTimbrado="2022-05-23T18:42:50" RfcProvCertif="SPR190613I52" SelloCFD="dUxav04E0XcbOhMsXjXDlWbFlZdcXqOk/R8DHLd3TILZ7gg5/aw T gI9yMLpVLvPkeEm5 A  QWHi0I/26TVTlsx6ejFWxXTEdewmarCuSrRO24CSFPz2dLX6ojy5Eg6QGTV0EQiS3LRtLsnTyqXH2QE7Ne Y7cqXXEcXWZ63AIAkkWY4U9uQu1/3F0jiHFfMCf4lBuSgDQ8c8LniBKoAsFoTySZmyhJv4h2koz1bE0Yl6Rvrr0NNlmYSH SGqbkYVsYKM9fbHIDJbNXosRqIGlugOlLIT2499j3gQfTn7d3sdQLtVqn4tcqaDGx2Du2dm1XFJRwT7aB62btNVW1Q==" NoCertificadoSAT="30001000000400002495" SelloSAT="gfFwozFJpvd1zpwvQPCkUWBfBG72/bn1 0CwrgnPx466uzjrL0RMsoSRHplN4dpTiqfAT/ bhGA8KjDDG6p 3RvlVxx1dmUIVffRcTv2Jd1D zZQRlt3RkHuANjCtOSVZKiW181WUyqzmH7ehL S7lyBL7odWswW4CGr5UPrMBFaSDSR6K 3aw2nd7SJce/2 rWN4szUIE4YlUS2whPLznlOdsRrSSy6OHJLkptSJaGn86F8bcp46Idz4lDNkI1G72lN3Kmat5cTrA8f9VTj8BfCzlmKdXg/jD7/1vOHm4mF3qmaLUym84Yj370ax7dGxYb4KoNLAxuYRJjJq1wkbw==" xmlns:tfd="http://www.sat.gob.mx/TimbreFiscalDigital" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
    </cfdi:Complemento>
</cfdi:Comprobante>

CodePudding user response：

First of all - get rid of that Image datatype! It's deprecated and will be removed in a future version of SQL Server.

Image is also a binary type - why use that to store textual information like XML ?? Makes no sense.... use XML datatype for best results.

Once you do have your XML in a T-SQL variable @data XML, you can then use this XQuery to get the values you're interested in:

WITH XMLNAMESPACES('http://www.sat.gob.mx/cfd/3' AS cfdi)
    SELECT
        Total = xc.value('@Total', 'decimal(20,2)'),
        TipoDeComprobante = xc.value('@TipoDeComprobante', 'varchar(20)')
    FROM
        @data.nodes('(/cfdi:Comprobante)') AS XT(XC)

CodePudding user response：

You can also use @marc_s's answer with your existing data type, although I agree the table should be changed if at all possible

WITH XMLNAMESPACES('http://www.sat.gob.mx/cfd/3' AS cfdi)
SELECT
    Total             = xc.value('@Total'            , 'decimal(20,2)'),
    TipoDeComprobante = xc.value('@TipoDeComprobante', 'varchar(20)')
FROM tb_cfdi c
CROSS APPLY (
    SELECT CAST(CAST(xml AS varbinary(max)) AS xml) AS data
) v
CROSS APPLY v.data.nodes('/cfdi:Comprobante') AS xt(xc)
WHERE c.uuid = 'f425cd6d-ed30-4a0d-8135-8dc7229b79ff';