Search code examples
sqldatabaset-sqlsybase

Sybase select value from XML field

I have a table named data_values stored in SYBASE in the following format:

data1 | data2 | data3 | value

The value field has the following XML format:

<T key1=value1 key2=value2 .... keyn=valuen /T>

I want to select from the value field values like value1, value2, etc. but I can't find a correct query to do this.

I have tried:

SELECT value.value('(/T//value1/node())[1]', 'varchar(255)') as value1 from data_values

also value is a reserved keyword I think and I need to escape it somehow. Other things I have tried is a value.query function which didn't work in Sybase. Also, I am pretty new to Sybase and didn't work a lot with complex sql queries.

Solution

  • To extract a tag in Sybase ASE the following xmlextract function can be used:

    e.g for key1 and value1 :

    select xmlextract('/T/@key1', value returns <DATATYPE>) as value1 from data_values

    Where is the type of the data you want to return ( INT, FLOAT, VARCHAR, ETC) and value is the name of the XML column.