SQL - Searching for XML data

Question

SQL - Searching for XML data

Navigation

#1 by (0 votes)

0

I have a table where one of the fields is an XML

ThestructureofXMListheBrazilianstandardschemaofCTE(KnowledgeofElectronicTransport)

<cteProcxmlns="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 set up a query in which I can tabulate the data within the column. For example:

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

Of the searches I did, I saw that the CROSS APPLY function exists and I tried to run this query but I 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

sql-server query xml

asked by anonymous 08.05.2018 / 17:29

1 answer

C - Stack Smashing Detected - How to correctly initialize a graph by adjacency list and insert edges? Datepicker update dates according to return from an ajax

score 0 · Accepted Answer

Namespace missing

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)