Hello,
I have a series of xml records in a varchar (max) column and I need to extract the <MT_USADA>16 MB</MT_USADA>
tag, for example:
<?xml version="1.0" encoding="utf-8"?>
<consulta xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<MT_TOTAL>4 GB</MT_TOTAL>
<MT_USADA>16 MB</MT_USADA>
</consulta>
I tried two ways, but it only works on xml columns
SELECT colunaxml.value('(/consulta/MT_TOTAL)[1]', 'varchar(max)') as teste from tabela
or
SELECT colunaxml.value('(.)[1]', 'varchar(max)') FROM tabela
I tried to use a CAST and CONVERT SELECT CAST(colunaxml AS xml)
/ SELECT CONVERT(xml,colunaxml)
, but I could not match it with colunaxml.value()
The result I hope is something like
ID | memory_used
01 | 16 MB
Edit: I got something that brings well what I need, but I did not understand how to put it in a Select ... from table
declare @xml varchar(1000)
SET @xml='<consulta xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<MT_TOTAL>4 GB</MT_TOTAL>
<MT_USADA>16 MB</MT_USADA>
</consulta>'
select @xml,SUBSTRING (@xml,CHARINDEX('<MT_USADA>',@xml)+LEN('<MT_USADA>'),(CHARINDEX('</MT_USADA>',@xml)-(CHARINDEX('<MT_USADA>',@xml)+LEN('<MT_USADA>'))))
Thank you in advance.