How to extract details from XML string in table

[E11esar]

Hi there.

I have a table (in Oracle) with a column defined as a CLOB and which holds a string of XML data.

What I am looking to do is to take this string and search for the <OLD> and <NEW> tags in a string such as the following:

<CHG client="c;GK" id="c;12345"><C OL NAM="this_col_o ne" TYP="c"><OLD>ol d-value</OLD><NEW>new-value</NEW>"><OLD>old-value</OLD><NEW>new-value</NEW></COL>...

Could somebody advise on how to break down this XML string please, so that I can extract the OLD and NEW values and then input these into another table?

I am starting to read about XPATH but hopefully somebody can ease the learning and offer some examples.

Thank you for your help.

M :o)

[Dormilich]

basicly you access the <OLD> (<NEW>) tags with the //OLD (//NEW) XPath expression, which give you a list. nevertheless, the actual implementation depends on what (i.e. how) you want to do with the data.

[E11esar]

Xpath

Hi there.

I want to take the long xml string and break this down into the individual parts for old and new values, placing each of these into another table that has columns for old and new - hope that makes sense..?

This is for an amendment logging report which will reference the table I want to store these old and new values in, along with other details such as user_id and date, to correspond with who and when a change was made to a table elsewhere in the system.

Thank you.

M :)

[E11esar]

SQL query

I guess what I want is a select statement that will also grab the respective <OLD> and <NEW> value from a column called XML_DATA, so something like

select //OLD from aTable where something = anotherThing though ofcourse using the correct syntax.

M :)

[Dormilich]

first you have to decide, if you want to treat the XML as String or as XML.

[E11esar]

String or XML

Hi there.

This would be as a string.

Thank you for your help with this.

M :)

[Dormilich]

then you need the string manipulation functions of your programming language (though I can imagine that RegEx will be used)

[E11esar]

String

Thing is I am not using a program to grab this information but SQL that is within a trigger, hence why I am looking for a SQL solution to this one.

After some reading I can see there is a .QUERY function but if I try a command such as

select column_name.Que ry('//OLD') from table_name

then this doesn't work. Can you see what I am doing wrong here please?

Thank you.

M :)

[Dormilich]

try the MySQL XML Functions

[E11esar]

MySQL

Yes I saw that page but I am doing this in Oracle, so the ideas there didn't work for me.

Thank you.

M :)

[Dormilich]

ah, sorry (I'm so used to MySQL...) but maybe you can find something in the oracle documentation.