I need import xml files to SQL but I need verify if exists a tag cancCFe e chCanc
Category: sql server xml
Question
haichu1 on Tue, 02 May 2017 14:04:54
Hi
I need import xml files to SQL but I need verify if exists a tag cancCFe e chCanc
Currently I veryfy this form:
declare @fullnamexml varchar(500)
declare @lote int = 0
declare @tipo varchar(2)
declare @xml varchar(MAX )
declare @sql nvarchar(MAX )
set @fullnamexml = 'c:\dados\teste.xml'
SET @sql = N'SELECT @xmlOut = CAST(BulkColumn AS varchar(MAX)) FROM OPENROWSET(BULK ''' + @fullnamexml + ''', SINGLE_BLOB) AS Arquivo'
EXEC sp_executesql @sql , N'@xmlOut varchar(max) Output' ,@xmlOut = @xml OUTPUT
SET @tipo = SUBSTRING(@xml,CHARINDEX('<mod>', @xml)+5,2)
SET @lote = CHARINDEX('<idLote>',@xml)
So I can check if the file is model 65 or 59 and if it has batch or not .. now I need to check if the file is canceled can you help me?
Follow example file that I will read
<?xml version="1.0"?>
<cancCFe versao="0.07" xmlns="http://www.fazenda.br/sat">
<tpAmb>1</tpAmb>
<idLote>1996</idLote>
<LoteCFeCanc>
<CFeCanc>
<infCFe versao="0.07" Id="CFe35171234" chCanc="CFe35170987">
<dEmi>20170227</dEmi>
<hEmi>112717</hEmi>
<ide>
<cNF>999999</cNF>
<mod>59</mod>
<cDV>0</cDV>
<CNPJ>12345678000199</CNPJ>
<signAC>CXqQ/55555/00000+EPxL+/ywCuA==</signAC>
<assinaturaQRCODE>SJ1234+4567/8V==</assinaturaQRCODE>
<numeroCaixa>001</numeroCaixa>
</ide>
<emit>
<CNPJ>98765432000212</CNPJ>
<xNome>ABCD LTDA</xNome>
<enderEmit>
<xLgr>RUA xxx 77</xLgr>
<nro>88</nro>
</enderEmit>
<IE>8888888887</IE>
</emit>
<dest/>
<total>
<vCFe>99.84</vCFe>
</total>
</infCFe>
<Signature xmlns="http://www.w3.org/9999/ig#">
<SignedInfo>
<CanonicalizationMethod Algorithm="http://www.w3.org/TR/REC-xml-20010315"/>
</SignedInfo>
</Signature>
</CFeCanc>
</LoteCFeCanc>
<cUF>35</cUF>
<nSeg/>
<dhEnvio>20170227142706</dhEnvio>
<nserieSAT>000252866</nserieSAT>
</cancCFe>
Replies
Yitzhak Khabinsky on Tue, 02 May 2017 14:29:23
Hi haichu1,
Assuming that your XML file is saved on the file system: Forum_cancCFe.xml.
Here we go:
DECLARE @xml XML;
SELECT @xml = XmlDoc
FROM OPENROWSET (BULK N'd:\Temp\Forum_cancCFe.xml', SINGLE_BLOB) AS Tab(XmlDoc);
declare @f bit;
set @f = @xml.exist('declare namespace ns="http://www.fazenda.br/sat"; //ns:cancCFe');
select @f;
haichu1 on Wed, 03 May 2017 20:38:28
Hi, Yitzhak
Thanks for solution but I need transform it at dynamic command because the xml file is dynamic, but I recieve this error
DECLARE @xml XML;
declare @fullnamexml varchar(500);
DECLARE @A nVARCHAR(MAX);
SET @fullnamexml = 'C:\Users\Dados\XMLTMP\arquivo.xml'
SET @A = 'select @xml = XmlDoc FROM OPENROWSET (BULK N''' + @fullnamexml + ''', SINGLE_BLOB) AS Tab(XmlDoc)';
EXEC sp_executesql @A , N'@xmlOut varchar(max) Output' ,@xmlOut = @xml OUTPUT
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@xml".
Can you help me?
declare @f bit;
set @f = @xml.exist('declare namespace ns="http://www.fazenda.sp.gov.br/sat"; //ns:cancCFe');
select @f;
Erland Sommarskog on Wed, 03 May 2017 21:52:37
SET @A = 'select @xml = XmlDoc FROM OPENROWSET (BULK N''' + @fullnamexml + ''', SINGLE_BLOB) AS Tab(XmlDoc)';
EXEC sp_executesql @A , N'@xmlOut varchar(max) Output' ,@xmlOut = @xml OUTPUT
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@xml".
It is always worth trying reading the error message. The message says that the variable @xml is not declared. And this occurs on line 1. It cannot happen in the main query batch, since line 1 is that batch is the declaration of a variable called no less than @xml. But there is also a batch of dynamic SQL. And indeed, it includes a variable @xml. But is it declared?
José Diz on Thu, 04 May 2017 10:49:11
-- code #1 DECLARE @xml XML; declare @fullnamexml varchar(500); DECLARE @A nVARCHAR(MAX); SET @fullnamexml = 'C:\Users\Dados\XMLTMP\arquivo.xml' SET @A = N'select @xmlOut = XmlDoc FROM OPENROWSET (BULK N''' + @fullnamexml + N''', SINGLE_BLOB) AS Tab(XmlDoc)'; EXEC sp_executesql @A , N'@xmlOut varchar(max) Output', @xmlOut= @xml OUTPUT