SQL - Searching XML data

Question

I have a table in which one of the fields is an XML

The XML structure is the Brazilian standard schema of CTE (Electronic Transport Knowledge)

<cteProc xmlns="http://www.portalfiscal.inf.br/cte" versao="2.00">
  <CTe xmlns="http://www.portalfiscal.inf.br/cte">
    <infCte versao="2.00" Id="CTe0000000000000000000000000000000">
      <ide>
        <cUF>10</cUF>
        <cCT>00040034</cCT>
        <CFOP>5353</CFOP>
        <natOp>0</natOp>
        <forPag>2</forPag>
        <mod>57</mod>
     </ide>
      <compl>
        <xObs>"Transporte Subcontratado PAGBEM"</xObs>
      </compl>

I’m trying to build a query where I can tabulate the data inside the column. For example:

> cCt-----|CFOP----|cUF----|XObs
> 00040034|5353    |10     |"Transporte Subcontratado PAGBEM"

From the searches I did, I saw that there is the CROSS APPLY function and tried to run this query but could not find the error

SELECT
    a.ID_Reg,
    ide.value('(./cUF)[1]', 'nvarchar(max)') as UF
FROM  
      TB_CTR_CTE_XML a 
      CROSS APPLY   [XML].nodes('/cteProc/CTe/infCte/ide') AS Cte(ide)
WHERE a.ID_Reg=1
GO

Answer 1

Missing the namespace

DECLARE @XMLCTE AS XML = '<cteProc xmlns="http://www.portalfiscal.inf.br/cte" versao="2.00"><CTe xmlns="http://www.portalfiscal.inf.br/cte"><infCte versao="2.00" Id="CTe0000000000000000000000000000000"><ide><cUF>10</cUF><cCT>00040034</cCT><CFOP>5353</CFOP><natOp>0</natOp><forPag>2</forPag><mod>57</mod></ide><compl><xObs>"Transporte Subcontratado PAGBEM"</xObs></compl></infCte></CTe></cteProc>'

;WITH XMLNAMESPACES('http://www.portalfiscal.inf.br/cte' as ns)
SELECT
    cte.ide.value('(ns:cUF)[1]', 'nvarchar(max)') as UF
FROM  
      @XMLCTE.nodes('//ns:cteProc/ns:CTe/ns:infCte/ns:ide') AS Cte(ide)