469,265 Members | 1,994 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,265 developers. It's quick & easy.

How to extract details from XML string in table

132 100+
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)
Oct 14 '09 #1
10 3047
Dormilich
8,651 Expert Mod 8TB
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.
Oct 14 '09 #2
E11esar
132 100+
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 :)
Oct 14 '09 #3
E11esar
132 100+
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 :)
Oct 14 '09 #4
Dormilich
8,651 Expert Mod 8TB
first you have to decide, if you want to treat the XML as String or as XML.
Oct 14 '09 #5
E11esar
132 100+
Hi there.

This would be as a string.

Thank you for your help with this.

M :)
Oct 14 '09 #6
Dormilich
8,651 Expert Mod 8TB
then you need the string manipulation functions of your programming language (though I can imagine that RegEx will be used)
Oct 14 '09 #7
E11esar
132 100+
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 :)
Oct 14 '09 #8
Dormilich
8,651 Expert Mod 8TB
try the MySQL XML Functions
Oct 14 '09 #9
E11esar
132 100+
Yes I saw that page but I am doing this in Oracle, so the ideas there didn't work for me.

Thank you.

M :)
Oct 14 '09 #10
Dormilich
8,651 Expert Mod 8TB
ah, sorry (I'm so used to MySQL...) but maybe you can find something in the oracle documentation.
Oct 14 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Joe | last post: by
3 posts views Thread by Lauren Quantrell | last post: by
2 posts views Thread by Bob | last post: by
1 post views Thread by Ori | last post: by
2 posts views Thread by Thief_ | last post: by
1 post views Thread by csgraham74 | last post: by
18 posts views Thread by Ecka | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.