Problem with a XML.

• ricardoalvestemoteo

  Newbie

  Points: 4

  More actions

  July 15, 2010 at 1:40 pm

  #100278

  Hi guys, I am facing a problem. I can´t execute following query:

  DECLARE @XML XML;

  SET @XML =

  N'

  <nfeProc versao="1.10" xmlns="http://www.portalfiscal.inf.br/nfe">

  <NFe>

  <infNFe Id="NFe25100710529813000127550010000429403000710000" versao="1.10">

  <ide>

  <cUF>25</cUF>

  <cNF>300071000</cNF>

  <natOp>Venda de mercadoria adquirida ou recebida de terceiros.</natOp>

  <indPag>0</indPag>

  <mod>55</mod>

  <serie>1</serie>

  <nNF>42940</nNF>

  <dEmi>2010-07-07</dEmi>

  <dSaiEnt>2010-07-07</dSaiEnt>

  <tpNF>1</tpNF>

  <cMunFG>2507507</cMunFG>

  <tpImp>1</tpImp>

  <tpEmis>1</tpEmis>

  <cDV>0</cDV>

  <tpAmb>1</tpAmb>

  <finNFe>1</finNFe>

  <procEmi>3</procEmi>

  <verProc>1.4.2</verProc>

  </ide>

  </infNFe>

  </NFe>

  </nfeProc>';

  SELECT

  R.Node.value('(natOp/text())[1]','varchar(100)') AS natOp,

  R.Node.value('(cNF/text())[1]','varchar(100)') AS cNF

  FROM @XML.nodes('//././ide') R(Node);

  When I retreat "versao="1.10" xmlns="http://www.portalfiscal.inf.br/nfe" and "Id="NFe25100710529813000127550010000429403000710000" versao="1.10" attributes, inside the tags, i have sucess!

  I can't change the XML as an official document.

  Help me please.

  Ricardo.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 15, 2010 at 3:45 pm

  #1193808

  Since you're dealing with a typed xml document, you'd need to declare the namespace(s) defined within that document:

  ;WITH xmlnamespaces(

  DEFAULT 'http://www.portalfiscal.inf.br/nfe'

  )

  SELECT

  R.Node.value('(natOp/text())[1]','varchar(100)') AS natOp,

  R.Node.value('(cNF/text())[1]','varchar(100)') AS cNF

  FROM @XML.nodes('//././ide') R(Node);

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• ricardoalvestemoteo

  Newbie

  Points: 4

  More actions

  July 16, 2010 at 6:09 am

  #1194023

  Thanks Lutz, his answer helped me a lot!