I need to import xml to temp table but the tag is dynamic. How can I do it?

• lucyoura 86267

  SSC Veteran

  Points: 203

  More actions

  November 29, 2016 at 9:18 am

  #401132

  I have 2 XML structure:

  1) case 1

  <nfeProc xmlns="http://www.portal.br/nfe" versao="2.10">

  <NFe xmlns="http://www.portal.inf.br/nfe">

  <infNFe Id="NFe33" versao="2.10">

  <emit>

  <CNPJ>23</CNPJ>

  <enderEmit>

  <xLgr>RUA</xLgr>

  <nro>179</nro>

  </enderEmit>

  <IE>87008347</IE>

  <CRT>3</CRT>

  </emit>

  <det nItem="1">

  <prod>

  <cProd>123</cProd>

  </prod>

  </det>

  <pag>

  <tPag>03</tPag>

  <vPag>31.40</vPag>

  <card>

  <tpIntegra>1</tpIntegra>

  <tBand>02</tBand>

  <cAut>012</cAut>

  </card>

  </pag>

  </infNFe>

  </NFe>

  </nfeProc>

  2) case 2

  <nfeProc xmlns="http://www.portal.br/nfe" versao="2.10">

  <NFe xmlns="http://www.portal.inf.br/nfe">

  <infNFe Id="NFe33" versao="2.10">

  <emit>

  <CNPJ>23</CNPJ>

  <enderEmit>

  <xLgr>RUA</xLgr>

  <nro>179</nro>

  </enderEmit>

  <IE>87008347</IE>

  <CRT>3</CRT>

  </emit>

  <det nItem="1">

  <prod>

  <cProd>123</cProd>

  </prod>

  </det>

  <pag>

  <tPag>03</tPag>

  <vPag>31.40</vPag>

  </pag>

  </infNFe>

  </NFe>

  </nfeProc>

  I have a query

  SELECT

  ROW_NUMBER() OVER ( ORDER BY X.pgto.query('pgto[1]/tPag').value('.', 'VARCHAR(02)') ) LINHA2,

  X.pgto.value('../tPag[1]','varchar(02)') MeioPgto,

  X.pgto.value('../vPag[1]','varchar(16)') VlrPgto,

  X.pgto.value('tBand[1]','varchar(02)') CodAdm

  INTO #pgto

  FROM @xml.nodes('nfeProc/NFe/infNFe/pag/card') AS X(pgto)

  It's OK at Case 1

  Result of Case 1

  LINHA2 MeioPgto CodAdm VlrPgto

  1 03 02 31.40

  but at Case 2 when I don't have

  <card>

  <tpIntegra>1</tpIntegra>

  <tBand>02</tBand>

  <cAut>012</cAut>

  </card>

  no result return.

  Result of Case 2

  LINHA2 MeioPgto CodAdm VlrPgto

  I would like to return at Case 2

  LINHA2 MeioPgto CodAdm VlrPgto

  1 03 31.40

  Help me Please...

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 29, 2016 at 10:06 am

  #1914996

  Here is a quick solution that should get you passed this hurdle

  😎

  Note that it is around 4 times more efficient than the code you posted

  USE TEEST;

  GO

  SET NOCOUNT ON;

  DECLARE @TXML TABLE

  (

  TXML_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,TXML_VAL XML NOT NULL

  );

  INSERT INTO @TXML(TXML_VAL)

  VALUES

  ('<nfeProc xmlns="http://www.portal.br/nfe" versao="2.10">

  <NFe xmlns="http://www.portal.inf.br/nfe">

  <infNFe Id="NFe33" versao="2.10">

  <emit>

  <CNPJ>23</CNPJ>

  <enderEmit>

  <xLgr>RUA</xLgr>

  <nro>179</nro>

  </enderEmit>

  <IE>87008347</IE>

  <CRT>3</CRT>

  </emit>

  <det nItem="1">

  <prod>

  <cProd>123</cProd>

  </prod>

  </det>

  <pag>

  <tPag>03</tPag>

  <vPag>31.40</vPag>

  <card>

  <tpIntegra>1</tpIntegra>

  <tBand>02</tBand>

  <cAut>012</cAut>

  </card>

  </pag>

  </infNFe>

  </NFe>

  </nfeProc>')

  ,('<nfeProc xmlns="http://www.portal.br/nfe" versao="2.10">

  <NFe xmlns="http://www.portal.inf.br/nfe">

  <infNFe Id="NFe33" versao="2.10">

  <emit>

  <CNPJ>23</CNPJ>

  <enderEmit>

  <xLgr>RUA</xLgr>

  <nro>179</nro>

  </enderEmit>

  <IE>87008347</IE>

  <CRT>3</CRT>

  </emit>

  <det nItem="1">

  <prod>

  <cProd>123</cProd>

  </prod>

  </det>

  <pag>

  <tPag>03</tPag>

  <vPag>31.40</vPag>

  </pag>

  </infNFe>

  </NFe>

  </nfeProc>')

  ;

  --Result of Case 1

  --LINHA2 MeioPgto CodAdm VlrPgto

  --10302 31.40

  ;WITH XMLNAMESPACES(DEFAULT 'http://www.portal.inf.br/nfe')

  SELECT

  TX.TXML_ID

  ,PAG.DATA.value('(tPag/text())[1]' ,'CHAR(2)' ) AS MeioPgto

  ,PAG.DATA.value('(vPag/text())[1]' ,'VARCHAR(16)') AS VlrPgto

  ,PAG.DATA.value('(card/tBand/text())[1]' ,'CHAR(2)' ) AS CodAdm

  FROM @TXML TX

  OUTER APPLY TX.TXML_VAL.nodes('*:nfeProc/*:NFe/*:infNFe') NFE(DATA)

  OUTER APPLY NFE.DATA.nodes('*:pag') PAG(DATA);

  Output

  TXML_ID MeioPgto VlrPgto CodAdm

  ----------- -------- ---------------- ------

  1 03 31.40 02

  2 03 31.40 NULL

• lucyoura 86267

  SSC Veteran

  Points: 203

  More actions

  November 29, 2016 at 12:01 pm

  #1915021

  Hi, thanks for answer but I would like know if you can resolve my problem without import the xml at sql table because I have many process before this condition and I am working with varchar(max) variable .

  Example of code that I have before this condition

  ;WITH XMLNAMESPACES(DEFAULT 'http://www.portalfiscal.inf.br/nfe')

  SELECT

  @chvnfe = Substring(X.emit.query('data(../@Id)').value('.', 'VARCHAR(44)'),4,47),

  @cnpjcli = X.emit.query('../dest/CNPJ').value('.', 'CHAR(14)'),

  @cnpjemp = X.emit.query('CNPJ').value('.', 'CHAR(14)'),

  @emissao = X.emit.query('../ide/dhEmi').value('.', 'CHAR(19)'),

  @doc = X.emit.query('../ide/nNF').value('.', 'CHAR(06)'),

  @serpdv = X.emit.query('../ide/serie').value('.', 'CHAR(09)'),

  @VlrTot = X.emit.query('../total/ICMSTot/vProd').value('.','NUMERIC(16,2)')

  FROM @xml.nodes('nfeProc/NFe/infNFe/emit') AS X(emit)

  Is it possible?

  Thanks