How to recover a value from within a tag in XML?

Question

I have a table called SPDNFE with a column DsXML which contains all the 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 below.(Only part)

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

How I search just what’s inside the tag <qVol> that would be the number 3.

Is there any way to substring or something like that ?

Answer 1

value() Method (xml Data Type)

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

And how it would look mine SQL @Marconi?

Your SQL must be 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 of the beginning of the answer.

Answer 2

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 must make a CAST of your field for the xml type, if it is varchar or nvarchar

Answer 3

If your column is the type NVARCHAR or VARCHAR you can use a LIKE in consultation:

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

---

LIKE

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

---

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

Answer 4

From what I understand you want the return of the query to be just what is inside the tag; therefore, I believe that the simplest way is to use replace to remove the tags of the result:

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