?
Solved

SQL functions to extract data from a column

Posted on 2011-05-12
22
Medium Priority
?
637 Views
Last Modified: 2012-05-11
HI all,

new'ish to SQL, so not sure if this is going to be possible at all!

Im using SQL Squirrel or SQL Developer to query some tables on my DB.

Ive got a table with a field with a "CLOB" type.

The data in the field is a load of HTML, eg:

 <fromAddress>notifications@xx.com</fromAddress>
  <subject>New monkey generated</subject>
  <toRecipients>
    <string>jpryer@xx.com</string>
  </toRecipients>
  <body>
                                    &lt;html&gt;&lt;body&gt; blah blah
</body>

etc...

Questions: Is it possible to extract specific sections of this feild into new columns, eg.
could i run some SQL to give me all this information, each in their own column?

From  |    Subject    |     To      |
0
Comment
Question by:jamiepryer
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 35747149
Yes, you have libraries that would help you with that task.
In MySQL, take a look on this:
XML processing in MySQL
http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html

Dealing with CLOBS
http://forums.mysql.com/read.php?118,212486,212486
http://dev.mysql.com/doc/refman/5.0/en/blob.html
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35747535
Please give credit to above, just throwing in another resource here on EE:
http://www.experts-exchange.com/Database/MySQL/A_3574-A-MySQL-Tidbit-In-line-XML-Parsing.html

I need to finalize the series with import from XML which was showing that you can load XML data into a CLOB type field and then using the parsing technique shown in my Article pull rows and/or columns of data out of the XML.

Again, I feel the question was answered here http:#a35747149 as my approach utilizes the XML libraries discussed in the links provided for MySQL 5.1.5 or higher; however, I just wanted to give an example of EXTRACTVALUE() in action.

Good luck!
0
 

Author Comment

by:jamiepryer
ID: 35752877
HI,
thanks for replies.
i still cant work out how to read from this CLOB field tho? sorry!

The HTML stuff is useful (extractValue function), but this is not my field type im afraid and this doesnt work on my CLOB field.

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:jamiepryer
ID: 35753051
this works well, IF my datatype is XML, so is there a function i can wrap around this to convert my clob to a xml maybe?


SELECT
      extractValue(object_value,'/com.server.channel.message.EmailMessage/fromAddress') "FROM",
  extractValue(object_value,'/com.server.channel.message.EmailMessage/subject') "Subject"
FROM
 clob_test
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35754180
Okay, I double checked my XML testing database and I used a LONGBLOB.  I will see what is happening with CLOB in my environment and post back.
0
 

Author Comment

by:jamiepryer
ID: 35754366
Cheers
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35754520
Worked just fine for me using TEXT and LONGTEXT.  Make sure you have valid XML in the field.  If you do not, then you will have to manually use string functions to find the index of '<fromAddress>' and then basically get content from that point to the close tag.

e.g.,
select substring(object_value, 
                 instr(object_value, '<fromAddress>')+13,
                 instr(object_value, '</fromAddress>')-instr(object_value, '<fromAddress>')-13
                ) as `from`
from clob_test
;

Open in new window


13 == the length of the string '<fromAddress>' in characters.
0
 

Author Comment

by:jamiepryer
ID: 35768405
still not working!
could it be to do with the fact the table im calling from has multiple fields in either and other columns, that not CLOB.

im trying this and it doesnt like it:


does work:
SELECT 
    extractValue(object_value, '/com.aveksa.server.channel.message.EmailMessage/fromAddress') as "From",
  	extractValue(object_value, '/com.aveksa.server.channel.message.EmailMessage/subject') as "Subject"
FROM
  clob_test
 

-----
DOESNT work:

SELECT 
    t1.(extractValue(object_value, '/com.aveksa.server.channel.message.EmailMessage/fromAddress')) as "From",
  	t1.(extractValue(object_value, '/com.aveksa.server.channel.message.EmailMessage/subject')) as "Subject"
FROM
  clob_test t1

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35768912
The second syntax is incorrect.  What is it you are trying to accomplish with that?  If it is because you have multiple tables, the alias goes on the column name.

SELECT
    extractValue(t1.object_value, '/com.aveksa.server.channel.message.EmailMessage/fromAddress') as "From",
        extractValue(t1.object_value, '/com.aveksa.server.channel.message.EmailMessage/subject') as "Subject"
FROM
  clob_test t1
0
 

Author Comment

by:jamiepryer
ID: 35768933
thanks, im trying to do this.
im pullling the data from a table with multiple columns, if thats possible?
"MESSAGE_OBJECT " is the only column in the table T_MESSAGES i want


SELECT
    extractValue(t1.value, '/com.server.channel.message.EmailMessage/fromAddress') as "From",
        extractValue(t1.value, '/com.server.channel.message.EmailMessage/subject') as "Subject"
FROM
  (SELECT MESSAGE_OBJECT FROM T_MESSAGES) T1
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35768957
Is this simpler version not working?

SELECT
    extractValue(t1.MESSAGE_OBJECT, '/com.server.channel.message.EmailMessage/fromAddress') as "From",
        extractValue(t1.MESSAGE_OBJECT, '/com.server.channel.message.EmailMessage/subject') as "Subject"
FROM T_MESSAGES T1

If not, please post the error message you are getting.
0
 

Author Comment

by:jamiepryer
ID: 35768983
ORA-00932: inconsistent datatypes: expected - got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 2 Column: 4
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35769075
So this is Oracle and not MySQL?
0
 

Author Comment

by:jamiepryer
ID: 35769087
yes - sorry! thought i had added that in my opener, but obviously not.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35769291
Thanks, Mod_MarlEE!  I am definitely out of my element with Oracle.  

@jamiepryer, I hope you don't mind, but I requested attention to have the zones changed on your behalf to hopefully attract the Oracle geniuses about to see what is missing.  If you don't get a response in the next 24-48 hours, I have asked the Moderator's to attract some Experts on your behalf.  Until then, please see if this is helpful: http://e-e.com/Q_22914044.html

Being not as strong with Oracle, one thing to note for me in that thread is XmlType(...) function is used to convert the CLOB to XML first before using EXTRACTVALUE(...).

i.e.,
SELECT 
    extractValue(XmlType(t1.MESSAGE_OBJECT), '/com.server.channel.message.EmailMessage/fromAddress') as "From",
        extractValue(XmlType(t1.MESSAGE_OBJECT), '/com.server.channel.message.EmailMessage/subject') as "Subject"
FROM T_MESSAGES T1

Open in new window

0
 

Author Closing Comment

by:jamiepryer
ID: 35769317
perfect!
xmltype function did the job!!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35769332
Please post sample data and expected results.  Also your Oracle version (all 4 numbers please: ex/ 10.2.0.4).
0
 

Author Comment

by:jamiepryer
ID: 35769378

SELECT distinct
  extractValue(XmlType(MESSAGE_OBJECT), '/com.xx.server.channel.message.EmailMessage/fromAddress') as "From",
  extractValue(XmlType(MESSAGE_OBJECT), '/com.xx.server.channel.message.EmailMessage/subject') as "Subject"
FROM   
  T_MESSAGES


notifications@xx.com	New xxxx
notifications@xx.com	xxxxx Item(s) removed
jpryer@xx.com	test
xx@xxx 	0005 - Work Reminder

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35769487
How about the sample input?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35769504
Steve, I used what was in the question body coupled with the XPath shown and so tested with something like this:

<com.server.channel.message.EmailMessage>
<fromAddress>notifications@xx.com</fromAddress>
  <subject>New monkey generated</subject>
  <toRecipients>
    <string>jpryer@xx.com</string>
  </toRecipients>
  <body>&lt;html&gt;&lt;body&gt; blah blah</body>
  </com.server.channel.message.EmailMessage>

Open in new window

0
 

Author Comment

by:jamiepryer
ID: 35770161
sorry, a sample line of the "clob"
<com.xxxxx.server.channel.message.EmailMessage>
  <fromAddress>notifications@xxxxx.com</fromAddress>
  <subject>New review generated</subject>
  <toRecipients>
    <string>JPryer@xxxx.com</string>
  </toRecipients>
  <body>
						&lt;html&gt;&lt;body&gt;
						New review has been generated.&lt;/br&gt;&lt;/br&gt;
						Review Details: &lt;/br&gt;&lt;/br&gt;
						Review Definition: Test Review&lt;/br&gt;
						Owner:   xxxAdmin&lt;/br&gt;
						Review : Test Review-2&lt;/br&gt;
						Date Generated: 2011-04-18 12:12:55&lt;/br&gt;
						Reviewers: Betty Stevenson, 
					</body>
  <contentType>text/html</contentType>
  <id>0</id>
</com.xxxxx.server.channel.message.EmailMessage>

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question