Trying to parse the EXTENDEDDATA field with SQL

Shawn_Filliol

Hello LiveReport gurus,

I'm trying to parse the DTREE's EXTENDEDDATA field. The contents are the following:

A<1,?,'Comments'='Bla bla bla ','Instructions'='Bla bla bla.'>

Does anyone have any experience parsing the "subfields" from EXTENDEDDATA into their own columns with SQL?

Thanks!

Shawn

Find more posts tagged with

Comments

Colin_Schmidt

In Oracle, that would involve REGEXP_like.

In MS/SQL, I'm not sure. It would help if you knew which keywords you want the values on, otherwise, if all you want are all parameters, that would make for a LOOP.

The type of parsing needed for the method OT uses is something I've never tried to tackle.

Colin J.

On Wed, Apr 1, 2015 at 9:07 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Trying to parse the EXTENDEDDATA field with SQL Posted bysfilliol@seaway.ca (Filliol, Shawn)On 04/01/2015 09:00 AM

Hello LiveReport gurus,I'm trying to parse the DTREE's EXTENDEDDATA field. The contents are the following:A<1,?,'Comments'='Bla bla bla ','Instructions'='Bla bla bla.'>Does anyone have any experience parsing the "subfields" from EXTENDEDDATA into their own columns with SQL?Thanks! Shawn
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

eLink User

if you have webreports they provide you with syntax on oscript data structures.It is a oscript Assoc so only a programming language or some cleverly crafted regexp parsing will allow you to do it.

..Appu

I wonder how one could debug livelink if Fiddler, Wireshark and Softerra LDAP browser was not invented and not free

On Wed, Apr 1, 2015 at 8:24 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Trying to parse the EXTENDEDDATA field with SQL Posted bycolin.schmidt@mwv.com (Schmidt, Colin)On 04/01/2015 09:23 AM

In Oracle, that would involve REGEXP_like.In MS/SQL, I'm not sure. It would help if you knew which keywords you want the values on, otherwise, if all you want are all parameters, that would make for a LOOP.

The type of parsing needed for the method OT uses is something I've never tried to tackle.

Colin J.

On Wed, Apr 1, 2015 at 9:07 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
Trying to parse the EXTENDEDDATA field with SQL Posted bysfilliol@seaway.ca (Filliol, Shawn)On 04/01/2015 09:00 AM

Hello LiveReport gurus,I'm trying to parse the DTREE's EXTENDEDDATA field. The contents are the following:A<1,?,'Comments'='Bla bla bla ','Instructions'='Bla bla bla.'>Does anyone have any experience parsing the "subfields" from EXTENDEDDATA into their own columns with SQL?Thanks! Shawn
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Trying to parse the EXTENDEDDATA field with SQL
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Shawn_Filliol

If anyone's interested the following queries worked for me (I'm using Oracle):

select dataid, ownerid, parentid, Name, status, regexp_substr (extendeddata, 'Comments(.*?)'',''Instr', 1, 1) vComments from dtree where dataid = '490161';

And:

select dataid, ownerid, parentid, Name, status, REPLACE(regexp_substr (extendeddata, 'Comments''=''(.*?)'',''In', 1, 1,null,1), '\''', '''') vComments from dtree where dataid = '490161';

I had to use the second query with the REPLACE command because for some reason the first query has a problem when my data contained an single quote and would return this (note the backslash before the '):

My comment\'s, number 1. My commend number 2.

Regards,

Shawn

Colin_Schmidt

I tried this to produce a string stripped of single quotes:

select dt.dataid, dt.name, dt.extendeddata "Plain",

REPLACE(dt.extendeddata, chr(39)) "Stripped"

from dtree dt

where dt.dataid = <number>

Not using the replacement character makes the function strip out the searched-for character, so then you have a string of parameters separated by commas. I think that string would then be easier to extract the data you need.

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Office)

+1 918.904 9223 (Cell)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Thu, Apr 2, 2015 at 8:53 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Trying to parse the EXTENDEDDATA field with SQL Posted bysfilliol@seaway.ca (Filliol, Shawn)On 04/02/2015 08:51 AM

If anyone's interested the following queries worked for me (I'm using Oracle):select dataid, ownerid, parentid, Name, status, regexp_substr (extendeddata, 'Comments(.*?)'',''Instr', 1, 1) vComments from dtree where dataid = '490161';And:select dataid, ownerid, parentid, Name, status, REPLACE(regexp_substr (extendeddata, 'Comments''=''(.*?)'',''In', 1, 1,null,1), '\''', '''') vComments from dtree where dataid = '490161';I had to use the second query with the REPLACE command because for some reason the first query has a problem when my data contained an single quote and would return this (note the backslash before the '):My comment\'s, number 1. My commend number 2.Regards, Shawn
[To post a comment, use the normal reply function]
Topic: Trying to parse the EXTENDEDDATA field with SQL
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center