Focal Point
[CLOSED] Extract XML from CLOB

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/709107915

September 01, 2010, 12:30 PM
slfmr
[CLOSED] Extract XML from CLOB
I've seen quite a few posts on this, but a lot of them seem unanswered.

I know how to pull the contents of a CLOB but what my problem is, is that it is removing the tags which I want.

So:

(ignore all spaces in the below lines, i had to do it because even here the tags were removed)

< Employee > Joe < /Employee >< PhoneNumber > 111-222-4444< / PhoneNumber >

is coming back as:

Joe111-222-4444

I actually need to keep the tags. Also I don't know what tags will be in the clob so I can't search for it then append it later.

Any thoughts on how to have webfocus bring back the xml tags?

This message has been edited. Last edited by: Kerry,


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
September 01, 2010, 01:56 PM
slfmr
So what I ended up doing was this (if anyone needs the same info):


-SET &RPLC = '< B>&' || 'lt;< /B>'; (with no spaces after the < )

Then did a replace in sql on my output:

replace (myfield, '<', '&RPLC')

and that printed fine so that it is not a < but an '& lt;' [again no spaces) with break tags.


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
September 01, 2010, 06:01 PM
Waz
Was this an HTML report ?

If so, the tags will be interpreted as HTML tags, and removed.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

September 02, 2010, 10:43 AM
WF_IL
Hi

If you have the XML schema (xsd) for the XML and you are also licensed to the Webfocus XML adaptor you could report directly against the CLOB field, each XML tag would become a field in the master file.
This is documented in the adaptors manual,under the XML adaptor, How to Access XML Data From an RDBMS .

(You create a master file against the xml document using xsd or directly against the XML itself and then combine it with the master you created against the Oracle table which contains the CLOB field )


Yours,
Eran
SRL Products

http://www.srl.co.il

September 07, 2010, 05:14 PM
slfmr
It was an excel report, but since it does create html first, I figured the same and replace the actual tag with the almost spelled out version.

We don't have the xsd or the adaptor license at this time, but that is something to look in to for the future.

Thank you


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6