Retrieve XML values in SQL Server

Question

I have this XML in my table:

    <?xml version="1.0" encoding="UTF-8"?>
<CompNfse xmlns="http://www.abrasf.org.br/nfse.xsd">
   <Nfse>
      <InfNfse Id="3874269800019313080681500000003">
         <Numero>00000003</Numero>
         <CodigoVerificacao>7999bbbbd</CodigoVerificacao>
         <CodigoControle>{900DA5A9-CBDA-4B62-8DAE-9451FEA34475}</CodigoControle>

I need to retrieve CodigoControle value. I've tried this piece of code without success

SELECT CAST(XMLNFE AS XML).value('(CompNfse/Nfse[1]/InfNfse[1]/CodigoVerificacao/node())[1]', 'VARCHAR(max)')

And some variation of it:

SELECT CAST(XMLNFE AS XML).value('(CompNfse/Nfse[1]/InfNfse[1]/CodigoVerificacao)[1]', 'VARCHAR(max)') 

SELECT CAST(XMLNFE AS XML).value('(CompNfse/Nfse/InfNfse/CodigoVerificacao)[1]', 'VARCHAR(max)')

Without success, I'm here trying to solve this simple query. Thanks.

Examples

I got the UDF from this post and it saved me: [http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server](http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server) — Daniel Carvalho, Jan 27 '17 at 14:04
@DanielCarvalho If this is UDF is saving you that'll be okay, but it is like using a road roller to iron a shirt :-) — Shnugo, Jan 30 '17 at 17:06

Shnugo · Accepted Answer · 2017-01-29T20:37:55.703

Your XML declares a default namespace, but your SELECT does not use it:

DECLARE @XMLNFE VARCHAR(MAX)=
'<?xml version="1.0" encoding="UTF-8"?>
<CompNfse xmlns="http://www.abrasf.org.br/nfse.xsd">
   <Nfse>
      <InfNfse Id="3874269800019313080681500000003">
         <Numero>00000003</Numero>
         <CodigoVerificacao>7999bbbbd</CodigoVerificacao>
         <CodigoControle>{900DA5A9-CBDA-4B62-8DAE-9451FEA34475}</CodigoControle>
      </InfNfse>
    </Nfse>
</CompNfse>';
WITH XMLNAMESPACES(DEFAULT 'http://www.abrasf.org.br/nfse.xsd')
,Casted AS(SELECT CAST(@XMLNFE AS XML) AS ToXML)
SELECT InfNfse.value('(CodigoVerificacao/text())[1]', 'VARCHAR(max)')
      ,InfNfse.value('(CodigoControle/text())[1]', 'uniqueidentifier')
      ,InfNfse.value('@Id', 'varchar(max)')
FROM Casted
CROSS APPLY ToXml.nodes('/CompNfse/Nfse/InfNfse') AS A(InfNfse)

Returns

7999bbbbd   900DA5A9-CBDA-4B62-8DAE-9451FEA34475    3874269800019313080681500000003

Btw

From your code I take, that the XML must be casted to be XML (What's the real column's data type?). If you can change this, you should store the XML within a column of type XML. And be aware, that - if this is under you control - you should not store the <?xml version="1.0" encoding="UTF-8"?>-declaration. If you XML includes non-ascii characters this might lead you into troubles... Change this to utf-16 or let it away...

Retrieve XML values in SQL Server

1 Answers1

Btw