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.
CodePudding user response:
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.