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>','')