Read Xml on SQL Server with namespace

Question

I trying to read a XML file in SQL Server using this guide.

http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

Works fine, but I have a XML file with a namespace and doesn´t works this code with namespace.

Some solution?

Thanks Remus. Now I have this code

 DECLARE @MyXML XML
SET @MyXML = '<cfdi:Comprobante xmlns:cfdi="http://www.sat.gob.mx/cfd/3"   Moneda="USD">
    <cfdi:Impuestos totalImpuestosRetenidos="0.00" totalImpuestosTrasladados="1143.06">
    </cfdi:Impuestos>
</cfdi:Comprobante> ' 

;with xmlnamespaces('http://www.sat.gob.mx/cfd/3' as cfdi)
SELECT
a.b.value('@Moneda','varchar(100)') Moneda,
a.b.value('Impuestos[1]/@totalImpuestosTrasladados','float') totalImpuestosTraslados
FROM @MyXML.nodes('cfdi:Comprobante') a(b)

Works, but the value of totalImpuestosTraslados is NULL.

Moneda totalImpuestosTraslados

USD NULL

Remus Rusanu · Accepted Answer · 2012-08-27 16:01:45Z

2

Use WITH XMLNAMESPACES. Post details (what you tried, what error you got) if this information is insufficient.

answered Aug 27, 2012 at 16:01

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

Comments

Floern · Accepted Answer · 2017-05-15 21:17:26Z

0

DECLARE @MyXML XML
SET @MyXML = '<cfdi:Comprobante xmlns:cfdi="http://www.sat.gob.mx/cfd/3"   Moneda="USD">
    <cfdi:Impuestos totalImpuestosRetenidos="0.00" totalImpuestosTrasladados="1143.06">
    </cfdi:Impuestos>
</cfdi:Comprobante> ' 

;with xmlnamespaces('http://www.sat.gob.mx/cfd/3' as cfdi)
SELECT
a.b.value('@Moneda','varchar(100)') Moneda,
a.b.value('cfdi:Impuestos[1]/@totalImpuestosTrasladados','float') totalImpuestosTraslados
FROM @MyXML.nodes('cfdi:Comprobante') a(b)

edited May 15, 2017 at 21:17

Floern

34k24 gold badges106 silver badges122 bronze badges

answered May 15, 2017 at 21:14

Daniel Sepulveda

1

Comments

Stephen Rauch · Accepted Answer · 2018-03-02 02:02:34Z

0

"Impuestos" also have cfdi as namespace, so you have to include it too

DECLARE @MyXML XML
SET @MyXML = '<cfdi:Comprobante xmlns:cfdi="http://www.sat.gob.mx/cfd/3"   Moneda="USD">
<cfdi:Impuestos totalImpuestosRetenidos="0.00" totalImpuestosTrasladados="1143.06">
</cfdi:Impuestos>
</cfdi:Comprobante> ' 

;with xmlnamespaces('http://www.sat.gob.mx/cfd/3' as cfdi)
SELECT
a.b.value('@Moneda','varchar(100)') Moneda,
a.b.value('<b>/cfdi:</b>Impuestos[1]/@totalImpuestosTrasladados','float') totalImpuestosTraslados
FROM @MyXML.nodes('cfdi:Comprobante') a(b)

edited Mar 2, 2018 at 2:02

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

answered Mar 2, 2018 at 1:38

Reyes

1

Collectives™ on Stack Overflow

Read Xml on SQL Server with namespace

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related