473,395 Members | 1,774 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 3254
Dormilich
8,658 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,658 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,658 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,658 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,658 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

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

Similar topics

3
by: Joe | last post by:
I'm trying to extract part of html code from a tag to a tag code begins with <span class="boldyellow"><B><U> and ends with TD><TD> <img src="http://whatever/some.gif"> </TD></TR></TABLE> I was...
3
by: Lauren Quantrell | last post by:
This a a long convoluted string parsing question... I have a string in an Access 2K database table field that I use for noting if a user has checked a record. The string goes like this: xy So...
2
by: Bob | last post by:
How do I extract the value of a table field and save it to a variable of similar data type in VBA ? Thank you in advance, Bob.
1
by: Ori | last post by:
Hi, I have a HTML text which I need to parse in order to extract data from it. My html contain a table contains few rows and two columns. I want to extract the data from the 2nd column in...
2
by: Thief_ | last post by:
I've got this type of info on a web page: ---------------------------------------------------------------------------- -------------------------------------------- <tr height="25"> <td nowrap...
2
by: Alan | last post by:
I'm having a bit of difficulty getting the results I need from our database. In a nutshell I'm trying to work out trends in what people buy next. So, for example, I'm trying to run a query that...
0
by: savvy | last post by:
I am developing a Recruitment Agency Website I am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word...
1
by: csgraham74 | last post by:
Hi Guys, I want to populate a nodelist so that i can extract various details. The xml document i have is similar to the one below. baiscally i want to extract the first instance of <PP>...
18
by: Ecka | last post by:
Hi everyone, I'm trying to write a PHP script that connects to a bank's currency convertor page using cURL and that part works fine. The issue is that I end up with a page that includes a lot...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.