Extract xml tag from a varchar (max)

2

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.

    
asked by anonymous 26.06.2018 / 22:04

1 answer

1

I got the answer, it worked like this:

select  
SUBSTRING (@coluna,CHARINDEX('<MT_USADA>', @coluna)+LEN('<MT_USADA>'),(CHARINDEX('</MT_USADA>', @coluna)-(CHARINDEX('<MT_USADA>', @coluna)+LEN('<MT_USADA>')))) FROM @tabela
    
27.06.2018 / 18:40