I have a table called SPDNFE with a column DsXML that contains all contents of a Xml .

Query:

SELECT top 1  SPDNFE.DsXML  FROM SPDNFE
where SPDNFE.CdIdNFe = 'NFe32170710490181000569550040001475221002513963'

Return of this query is a Xml as follows. ( Only one part )

<vol><qVol>3</qVol><esp>VL</esp><pesoL>43.000</pesoL><pesoB>43.000</pesoB></vol>

How do I find out what is inside the tag <qVol> which would be the number 3 .

Is there a way to substring or something like this?

value () Method (xml Data Type)

  

Executes an XQuery against an XML and returns a value of type SQL. That
  method returns a scalar value. You usually use this method to
  extract a value from a XML instance stored in a column,
  parameter or variable of type xml. This way, you can specify
  SELECT queries that combine or compare XML data with data in
  columns não-XML .

And how would my SQL @Marconi?

Your SQL should look like this:

DECLARE @myDoc xml  
DECLARE @ProdID int  
SET @myDoc = '<vol>
    <qVol>3</qVol>
    <esp>VL</esp>
    <pesoL>43.000</pesoL>
    <pesoB>43.000</pesoB>
</vol>'  

SET @ProdID =  @myDoc.value('(/vol/qVol/node())[1]', 'int')  
SELECT @ProdID qVol

SQL Fiddle

For more information, visit Link at the beginning of the answer.

You can use the query () …

declare @XML xml

set @XML = '<vol><qVol>3</qVol><esp>VL</esp><pesoL>43.000</pesoL><pesoB>43.000</pesoB></vol>'

select @XML.query('/vol/qVol')

You should make a CAST of your field for the xml type, if it is varchar or nvarchar

If your column is of type NVARCHAR or VARCHAR you can use LIKE in the query:

SELECT SPDNFE.*
  FROM SPDNFE
 WHERE SPDNFE.DsXML LIKE '%<qVol>3</qVol>%'

  

LIKE

  
  

Determines whether a specific string matches a specified pattern. A pattern can include normal characters and wildcards. During pattern matching, the normal characters must exactly match the characters specified in the string. However, wildcard characters can be matched to arbitrary string fragments. The use of wildcard characters makes the LIKE operator more flexible than the use of string comparison operators = e! =. If any of the arguments are not of the string data type, the SQL Server Database Engine will convert it to the string data type if possible.

The % character within the field that is being searched for in LIKE indicates that there may be any content in its location. In our example we are saying that the qVol tag can be located anywhere in the text.

So I understand you want the query return to be just what’s inside the tag ; so I think the simplest way is to use replace to remove the tags from the result:

select replace(replace(SPDNFE.DsXML, '<qVol>', ''), '</qVol>','')