CODEWITHSUNDEEP
sql-serverxml
Rodrigo Curvello
Rodrigo Curvello

Reputation: 11

Select data from XML in SQL

I am having trouble in getting some data from a XML text saved in my SQL Server DB. My data is as follow:

<retConsReciCTe xmlns="http://www.portalfiscal.inf.br/cte" versao="3.00">
  <tpAmb>2</tpAmb>
  <verAplic>SP-CTe-25-05-2017</verAplic>
  <nRec>351000011484798</nRec>
  <cStat>104</cStat>
  <xMotivo>Lote processado</xMotivo>
  <cUF>35</cUF>
  <protCTe versao="3.00">
    <infProt>
      <tpAmb>2</tpAmb>
      <verAplic>SP-CTe-25-05-2017</verAplic>
      <chCTe>35170623838348000310570000000058731000058731</chCTe>
      <dhRecbto>2017-06-22T21:28:18-03:00</dhRecbto>
      <cStat>232</cStat>
      <xMotivo>Rejeição: IE do destinatário não informada</xMotivo>
    </infProt>
  </protCTe>
</retConsReciCTe>

I want to select only the content in . For this reason I am running the following query:

select top 10 retorno_sefaz_doc.value('(/retConsReciCTe/@xmln)[1]','varchar(10)') as [retorno] from documentos

And my return is always NULL. Someone could gently give me a help in telling me what is going wrong?

Upvotes: 1

Views: 51

Answers (1)

TriV
TriV

Reputation: 5148

You could define a default namespace and value to get the output like this

DECLARE @SampleData AS TABLE
(
    XmlValue xml
)

INSERT INTO @SampleData
(
    XmlValue
)
VALUES
(N'<retConsReciCTe xmlns="http://www.portalfiscal.inf.br/cte" versao="3.00">
  <tpAmb>2</tpAmb>
  <verAplic>SP-CTe-25-05-2017</verAplic>
  <nRec>351000011484798</nRec>
  <cStat>104</cStat>
  <xMotivo>Lote processado</xMotivo>
  <cUF>35</cUF>
  <protCTe versao="3.00">
    <infProt>
      <tpAmb>2</tpAmb>
      <verAplic>SP-CTe-25-05-2017</verAplic>
      <chCTe>35170623838348000310570000000058731000058731</chCTe>
      <dhRecbto>2017-06-22T21:28:18-03:00</dhRecbto>
      <cStat>232</cStat>
      <xMotivo>Rejeição: IE do destinatário não informada</xMotivo>
    </infProt>
  </protCTe>
</retConsReciCTe>')

;WITH XMLNAMESPACES (default 'http://www.portalfiscal.inf.br/cte')
select sd.XmlValue.value('(/retConsReciCTe/@versao)[1]','varchar(20)') 
                                                             AS Versao
FROM @SampleData sd

Returns

Versao
3.00

Upvotes: 2

Related Questions