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>

Sponsored



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