Focal Point
[SOLVED] XML extract

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

August 09, 2011, 05:14 AM
FRA-Sarwar
[SOLVED] XML extract
at the first Hi for all.
i have an extract that contain headr info and xml tags, so i decide to create a Master file that can read both header info which is in fix format and the other part is tags as the following:

header info
header info
header info
"
data....
"


how to do?

This message has been edited. Last edited by: Kerry,
August 09, 2011, 05:41 PM
Waz
Please post your code between the code tags.

Click on the button when entering the code or wrap your code in 
[CODE]..
[/CODE]


So your data has two formats ?
The header is text, and the data is XML ?


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!

August 09, 2011, 10:40 PM
FRA-Sarwar
at the first Hi for all.
i have an extract that contain headr info and xml tags, so i decide to create a Master file that can read both header info which is in fix format and the other part is tags as the following:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS>
<DTSERVER>20090318084717</DTSERVER>
<LANGUAGE>ENG</LANGUAGE>
<FI><ORG>fra</ORG><FID>00628</FID></FI>
<INTU.BID>006</INTU.BID>
<INTU.USERID>fra_sssss</INTU.USERID>
</SONRS>
</SIGNONMSGSRSV1>
</OFX>

the master file is as the followig:

FILENAME=OFX_TEST, SUFFIX=XML     , $
  SEGMENT=HEADERINF, SEGTYPE=S0, $
    FIELDNAME=HEADERINF, ALIAS=HEADERINF, USAGE=A50, ACTUAL=A50, MISSING=ON, $
  SEGMENT=OFX, SEGTYPE=S0, $
    FIELDNAME=OFX, ALIAS=OFX, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=SIGNONMSGSRSV1, ALIAS=SIGNONMSGSRSV1, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=OFX, PROPERTY=ELEMENT,  $
    FIELDNAME=SONRS, ALIAS=SONRS, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SIGNONMSGSRSV1, PROPERTY=ELEMENT,  $
    FIELDNAME=STATUS, ALIAS=STATUS, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=CODE, ALIAS=CODE, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=OFX.STATUS, PROPERTY=ELEMENT,  $
    FIELDNAME=SEVERITY, ALIAS=SEVERITY, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=OFX.STATUS, PROPERTY=ELEMENT,  $
    FIELDNAME=DTSERVER, ALIAS=DTSERVER, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=LANGUAGE, ALIAS=LANGUAGE, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=FI, ALIAS=FI, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=ORG, ALIAS=ORG, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=FI, PROPERTY=ELEMENT,  $
    FIELDNAME=FID, ALIAS=FID, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=FI, PROPERTY=ELEMENT,  $
    FIELDNAME=INTU.BID, ALIAS=INTU.BID, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=INTU.USERID, ALIAS=INTU.USERID, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $


when i run i got the following error:
(FOC42256) CONTENT NOT ALLOWED AT ROOT LEVEL. '<' EXPECTED

how to do?

This message has been edited. Last edited by: FRA-Sarwar,


WebFOCUS 7.67 & WebFOCUS 80 windows
August 09, 2011, 11:14 PM
Waz
I believe that the issue here is that you have non XML at the start of the XML document.

You can't combine the two.

Here is a possible solution.

EX -LINES 22 EDAPUT XML,OFX_TEST,CV,FILE
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS>
<DTSERVER>20090318084717</DTSERVER>
<LANGUAGE>ENG</LANGUAGE>
<FI><ORG>fra</ORG><FID>00628</FID></FI>
<INTU.BID>006</INTU.BID>
<INTU.USERID>fra_samini</INTU.USERID>
</SONRS>
</SIGNONMSGSRSV1>
</OFX>

-RUN

-* Write out a master to read the BSB_ACC list
EX -LINES 4 EDAPUT MASTER,OFX_TEMP,CV,FILE
FILENAME=OFX_TEMP, SUFFIX=FIX,$
SEGNAME=OFX_TEMP, $
  FIELD=LINE ,ALIAS=  ,A255 ,A255 ,$

FILEDEF OFX_TEMP DISK ofx_test.xml
FILEDEF OFX_XML  DISK ofx_xml.xml
FILEDEF OFX_FIX  DISK ofx_fix.ftm

TABLE FILE OFX_TEMP
 PRINT 
       COMPUTE FST_CHAR/A1 = EDIT(LJUST(255,LINE,'A255'),'9') ;
       COMPUTE PUT_FIX/I1 = IF FST_CHAR NE '<' THEN PUTDDREC('OFX_FIX',7,LINE,ARGLEN(255,LINE,'I9'),PUT_FIX) ELSE 0 ;
       COMPUTE PUT_XML/I1 = IF FST_CHAR EQ '<' THEN PUTDDREC('OFX_XML',7,LINE,ARGLEN(255,LINE,'I9'),PUT_XML) ELSE 0 ;
       
 ON TABLE HOLD 
END

-RUN

-* Write out a master to read the BSB_ACC list
EX -LINES 41 EDAPUT MASTER,OFX_TEST,CV,FILE
FILENAME=OFX_TEST, SUFFIX=XML     , $
  SEGMENT=OFX, SEGTYPE=S0, $
    FIELDNAME=OFX, ALIAS=OFX, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=SIGNONMSGSRSV1, ALIAS=SIGNONMSGSRSV1, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=OFX, PROPERTY=ELEMENT,  $
    FIELDNAME=SONRS, ALIAS=SONRS, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SIGNONMSGSRSV1, PROPERTY=ELEMENT,  $
    FIELDNAME=STATUS, ALIAS=STATUS, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=CODE, ALIAS=CODE, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=OFX.STATUS, PROPERTY=ELEMENT,  $
    FIELDNAME=SEVERITY, ALIAS=SEVERITY, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=OFX.STATUS, PROPERTY=ELEMENT,  $
    FIELDNAME=DTSERVER, ALIAS=DTSERVER, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=LANGUAGE, ALIAS=LANGUAGE, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=FI, ALIAS=FI, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=ORG, ALIAS=ORG, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=FI, PROPERTY=ELEMENT,  $
    FIELDNAME=FID, ALIAS=FID, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=FI, PROPERTY=ELEMENT,  $
    FIELDNAME=INTU.BID, ALIAS=INTU.BID, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=INTU.USERID, ALIAS=INTU.USERID, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $

FILEDEF OFX_TEST DISK ofx_xml.xml

TABLE FILE OFX_TEST
 PRINT SEG.OFX
END

-* Write out a master to read the BSB_ACC list
EX -LINES 4 EDAPUT MASTER,OFX_FIX,CV,FILE
FILENAME=OFX_FIX, SUFFIX=FIX,$
SEGNAME=OFX_FIX, $
  FIELD=HEADERINF ,ALIAS=  ,A50 ,A50 ,$

TABLE FILE OFX_FIX
 PRINT HEADERINF
END


This splits the file into two parts.

The logic to split may have to be tweeked, but it works on the sample you gave.


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!

August 10, 2011, 10:47 PM
FRA-Sarwar
So...so thanks Waz, yes it is worked, i did as the following:
I Create a master file that could read all data as FIX FORMAT
FILENAME=OFX_FIX, SUFFIX=FIX, $
 SEGMENT=OFX_FIX,	SEGTYPE=S0,	$
  FIELD=LINE, ALIAS= , USAGE=A500,	,ACTUAL=A500	,$


FILEDEF OFX_FIX DISK C:\ibi\apps\for_the_test\MFA.qfx
FILEDEF XMLF DISK XMLF.QFX
FILEDEF OFX_XML DISK XMLF.QFX
FILEDEF HEADERF DISK HEADER.FTM
FILEDEF OFX_HFIX DISK HEADER.FTM

DEFINE FILE OFX_FIX
FST_CHAR/A1 = EDIT(LJUST(255,LINE,'A255'),'9') ;
END
-*Tags 
TABLE FILE OFX_FIX
 PRINT LINE
WHERE FST_CHAR EQ '<'
 ON TABLE HOLD AS XMLF FORMAT ALPHA
END

-* Header
TABLE FILE OFX_FIX
PRINT LINE
WHERE FST_CHAR NE '<'
ON TABLE HOLD AS HEADERF FORMAT ALPHA
END

-* i create two master file those are could rea the header and tagas
FILENAME=OFX_XML, SUFFIX=XML , $
  SEGMENT=OFX, SEGTYPE=S0, $
    FIELDNAME=OFX, ALIAS=OFX, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=SIGNONMSGSRSV1, ALIAS=SIGNONMSGSRSV1, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=OFX, PROPERTY=ELEMENT,  $
    FIELDNAME=SONRS, ALIAS=SONRS, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SIGNONMSGSRSV1, PROPERTY=ELEMENT,  $
    FIELDNAME=STATUS, ALIAS=STATUS, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=CODE, ALIAS=CODE, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=OFX.STATUS, PROPERTY=ELEMENT,  $
    FIELDNAME=SEVERITY, ALIAS=SEVERITY, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=OFX.STATUS, PROPERTY=ELEMENT,  $
    FIELDNAME=DTSERVER, ALIAS=DTSERVER, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=LANGUAGE, ALIAS=LANGUAGE, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=FI, ALIAS=FI, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=SONRS, PROPERTY=ELEMENT,  $
    FIELDNAME=ORG, ALIAS=ORG, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=FI, PROPERTY=ELEMENT,  $..........

FILENAME=OFX_HFIX,	SUFFIX=FIX, $
 SEGNAME=OFX_HFIX, $
  FIELD=HEADERINF,	ALIAS= , USAGE=A50, ACTUAL=A50, $

TABLE FILE OFX_HFIX
PRINT *
WHERE HEADERINF NE ''
ON TABLE HOLD AS HFIX FORMAT ALPHA
END
-RUN
DEFINE FILE OFX_XML
HEADERINF/A50='';
END

TABLE FILE OFX_XML
PRINT HEADERINF *
ON TABLE HOLD AS HXML FORMAT ALPHA
END

MODIFY FILE HXML
FIXFORM FROM HFIX
INCLUDE *
DATA ON HFIX
END
-RUN

TABLE FILE HXML
LIST *
END


so so thanks for your help


WebFOCUS 7.67 & WebFOCUS 80 windows
August 10, 2011, 11:20 PM
Waz
Be careful, as some XML documents may have carriage returnes in the text, and this technique may not get the data.


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!