Why I can't retrive the value of Signature Value

Category: sql server xml

Question

haichu1 on Fri, 17 Feb 2017 19:24:25


I have this xml file
<?xml version="1.0"?>
<nfeProc xmlns="http://www.portal" versao="3.10">
<NFe xmlns="http://www.portal">
<infNFe versao="3.10" Id="NFe33160823219874XXXX130650010001557041232872340">
<ide>
<cUF>33</cUF>
<dhEmi>2016-08-01T00:04:49-03:00</dhEmi>
</ide>
<emit>
<CNPJ>99999999999999</CNPJ>

</emit>
<det nItem="1">
<prod>
<cProd>135</cProd>
<cEAN/>
</prod>
</det>
<total>
<ICMSTot>
<vBCST>0.00</vBCST>
</ICMSTot>
</total>
</infNFe>
<Signature xmlns="http://www.w3.org/XX/xmldsig#">
<SignedInfo>
<SignatureMethod Algorithm="http://www.w3.org//xmldsig#XXX-XXX1"/>
</SignedInfo>
<SignatureValue>Caocnw==</SignatureValue>
</Signature>
</NFe>
</nfeProc>


I develop this command to see some values bot I can't load Signature Value..

;WITH XMLNAMESPACES(DEFAULT 'http://www.portal') 
SELECT
@chvnfe    = SUBSTRING(X.emit.query('data(@Id)').value('.', 'VARCHAR(50)'),4,44),
@dtemissao  = X.emit.query('ide/dhEmi').value('.', 'CHAR(19)'),
@totdes     = CONVERT(numeric(16,2),COALESCE(X.emit.query('total/ICMSTot/vDesc').value('.','float'),0))
@signac     = X.emit.query('../Signature/SignatureValue').value('.', 'CHAR(344)')
FROM @xml.nodes('nfeProc/NFe/infNFe') AS X(emit)

What I do wrong?

Replies

Yitzhak Khabinsky on Fri, 17 Feb 2017 20:13:26


Hi haichu1,

Because it has its own additional namespace.

SQL:

DECLARE @xml XML = '<?xml version="1.0"?>
<nfeProc xmlns="http://www.portal" versao="3.10">
	<NFe xmlns="http://www.portal">
		<infNFe versao="3.10" Id="NFe33160823219874XXXX130650010001557041232872340">
			<ide>
				<cUF>33</cUF>
				<dhEmi>2016-08-01T00:04:49-03:00</dhEmi>
			</ide>
			<emit>
				<CNPJ>99999999999999</CNPJ>
			</emit>
			<det nItem="1">
				<prod>
					<cProd>135</cProd>
					<cEAN/>
				</prod>
			</det>
			<total>
				<ICMSTot>
					<vBCST>0.00</vBCST>
				</ICMSTot>
			</total>
		</infNFe>
		<Signature xmlns="http://www.w3.org/XX/xmldsig#">
			<SignedInfo>
				<SignatureMethod Algorithm="http://www.w3.org//xmldsig#XXX-XXX1"/>
			</SignedInfo>
			<SignatureValue>Caocnw==</SignatureValue>
		</Signature>
	</NFe>
</nfeProc>';

DECLARE @chvnfe VARCHAR(50)
	, @dtemissao VARCHAR(30)
	, @totdes DECIMAL(16,2)
	, @signac VARCHAR(1024);

;WITH XMLNAMESPACES(DEFAULT 'http://www.portal', 'http://www.w3.org/XX/xmldsig#' AS ns) 
SELECT @chvnfe    = col.value('@Id', 'VARCHAR(50)')
	, @dtemissao  = col.value('(ide/dhEmi)[1]', 'VARCHAR(30)')
	, @totdes     = col.value('(total/ICMSTot/vBCST)[1]','DECIMAL(16,2)')
	, @signac     = col.value('(../ns:Signature/ns:SignatureValue)[1]', 'CHAR(344)')
FROM @xml.nodes('/nfeProc/NFe/infNFe') AS tab(col);

SELECT @chvnfe AS chvnfe
	, @dtemissao  AS  dtemissao
	, @totdes AS totdes
	, @signac AS signac;

Output:

chvnfe	dtemissao	totdes	signac
NFe33160823219874XXXX130650010001557041232872340	  2016-08-01T00:04:49-03:00	0.00	Caocnw==