How to extract details from XML string in table

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"><COL NAM="this_col_one" TYP="c"><OLD>old-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)

re: How to extract details from XML string in table

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.

re: How to extract details from XML string in table

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 :)

re: How to extract details from XML string in table

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 :)

re: How to extract details from XML string in table

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

re: How to extract details from XML string in table

Hi there.

This would be as a string.

Thank you for your help with this.

M :)

re: How to extract details from XML string in table

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

re: How to extract details from XML string in table

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.Query('//OLD') from table_name

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

Thank you.

M :)

re: How to extract details from XML string in table

try the MySQL XML Functions

re: How to extract details from XML string in table

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

Thank you.

M :)

re: How to extract details from XML string in table

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