0
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