I have a table called
SPDNFE with a column
DsXML that contains all contents of a
SELECT top 1 SPDNFE.DsXML FROM SPDNFE where SPDNFE.CdIdNFe = 'NFe32170710490181000569550040001475221002513963'
Return of this query is a
Xml as follows. ( Only one part )
How do I find out what is inside the tag
<qVol> which would be the number
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
XMLinstance stored in a column,
parameter or variable of type xml. This way, you can specify
SELECT queries that combine or compare
XMLdata with data in
And how would my
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())', 'int') SELECT @ProdID qVol
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
VARCHAR you can use
LIKE in the query:
SELECT SPDNFE.* FROM SPDNFE WHERE SPDNFE.DsXML LIKE '%<qVol>3</qVol>%'
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.
% 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>','')