How to query a xml node with different namespace in sql server

Question

 <?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/cfdv32.xsd" version="3.2" folio="29027" fecha="2014-03-06T18:09:27" tipoDeComprobante="ingreso" formaDePago="Pago en una sola exhibición" metodoDePago="EFECTIVO" noCertificado="00001000000201571214" subTotal="346.42" TipoCambio="1.0" Moneda="MXP" total="400" LugarExpedicion="AGUASCALIENTES,AGUASCALIENTES"  sello="a/koIf5I9dA4gdHBLmsPkmQ1Pg/u8Ftx/EXlY8KTAKYOFedpyLlfubXXYr8YNYuMpMjMTWksyWfqamFO5gNJtfR7pVtJ9RB4R+JnqPSb4lOdWz4+vZdwPueFhhsnicyXpessBeSBuyStRUa+tapxeW3CE0cQUEOu+zfEdkxLo+4=">
    <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/TimbreFiscalDigital/TimbreFiscalDigital.xsd" selloCFD="a/koIf5I9dA4gdHBLmsPkmQ1Pg/u8Ftx/EXlY8KTAKYOFedpyLlfubXXYr8YNYuMpMjMTWksyWfqamFO5gNJtfR7pVtJ9RB4R+JnqPSb4lOdWz4+vZdwPueFhhsnicyXpessBeSBuyStRUa+tapxeW3CE0cQUEOu+zfEdkxLo+4=" FechaTimbrado="2014-03-06T18:09:43" UUID="7C35D216-5B48-401A-9470-35DD6941EA08" noCertificadoSAT="00001000000202864530" version="1.0" selloSAT="ptTsZg67YnS9iQfnpUKH6HtBWIdt5uOwsnYvU4cMau8H99kb6I8zVE9IyPKGyxhEkIwOa5p8mjVzNZ7cclB3VO+m1r2LBt8s2j9NdTDlkQAT5TPoD8UveGkj8MlR5NVOCBQwCz29q6d+YqaY4S/f2IxokVLYy7iz1scDNma8VPc=" />
    </cfdi:Complemento>
</cfdi:Comprobante>

I have a XML variable with this xml, I need to get the values from the node TimbreFiscal, I have tried the following query but it doesn't work,

WITH XMLNAMESPACES    (  'http://www.sat.gob.mx/TimbreFiscalDigital' as Timbre) 
select   TimbreSelloSAT = Timbre.value('@selloSAT', 'varchar(100)'),
    TimbreNoCertificadoSAT = Timbre.value('@noCertificadoSAT', 'varchar(100)'),
    TimbreSelloCFD = Timbre.value('@selloCFD', 'varchar(100)'),
    TimbreFecha = Timbre.value('@FechaTimbrado', 'varchar(100)'),
    TimbreUUID = Timbre.value('@UUID', 'varchar(100)'),
    TimbreVersion = Timbre.value('@version', 'varchar(100)')  
FROM @xml.nodes('/Comprobante/Complemento/TimbreFiscalDigital') as a(Timbre)

Any help would be really apreciate.

Jayvee · Accepted Answer · 2014-03-13 23:20:41Z

1

this should work:

    ;WITH XMLNAMESPACES    (  'http://www.sat.gob.mx/TimbreFiscalDigital' as Timbre, 'http://www.sat.gob.mx/cfd/3' as cfdi) 
select   TimbreSelloSAT = Timbre.value('@selloSAT', 'varchar(100)'),
    TimbreNoCertificadoSAT = Timbre.value('@noCertificadoSAT', 'varchar(100)'),
    TimbreSelloCFD = Timbre.value('@selloCFD', 'varchar(100)'),
    TimbreFecha = Timbre.value('@FechaTimbrado', 'varchar(100)'),
    TimbreUUID = Timbre.value('@UUID', 'varchar(100)'),
    TimbreVersion = Timbre.value('@version', 'varchar(100)')  
FROM @xml.nodes('/cfdi:Comprobante/cfdi:Complemento/Timbre:TimbreFiscalDigital') a(timbre)

answered Mar 13, 2014 at 23:20

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query a xml node with different namespace in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related