Extract xml tag from a varchar(max) column

Asked

Viewed 456 times

2

Hello,

I have a series of xml records in a varchar(max) column and need to extract the tag <MT_USADA>16 MB</MT_USADA>, 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 in 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 using a CAST and CONVERT SELECT CAST(colunaxml AS xml) / SELECT CONVERT(xml,colunaxml), but I couldn’t match colunaxml.value()

The result I’m hoping for is something like

ID | memoria_usada

01 | 16 MB

Edit: I got something that brings well what I need, but I didn’t understand how to put 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.

sql sql-server select-sql

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

Browser other questions tagged sql sql-server select-sql

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