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

Asked

Viewed 1,722 times

3

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 ?

4 answers

4


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.

1

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

0

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.

  • 1

    it’s a good idea as long as the column is varchar. As he didn’t mention, his response would be the best option, but if the column is of the type xml, would be better the exists with xml xpath. Just to leave it as an alternative

  • @Ricardopunctual I put a note regarding this

  • I tried but it is very slow to query how to read the entire table that has a lot of record is unviable with like.

0

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.