Hi
I need to convert all the master files into XML file format.This XML file should have the Column names , Column alais , Column Data type and database conncetion details .

How to achive this one?.Please help me on this.

Thanks
Gobinath Velusamy

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

You could use SYSTABLE and SYSCOLUM, and hold the report FORMAT XML.

Not sure about the connection details

Waz, I'm curious about this. SYSTABLE does give me a list of tables, but SYSCOLUM returns no rows. Do I have to allocate something before querying SYSCOLUM?

SYSCOLUM is a multisegment file, if you used print * it will probably not work.

Try

TABLE FILE SYSCOLUM
PRINT SEG.NAME
END

or use SET ALL=ON

Come of the child segments are very specific to certain masters.

Gobinath,

Below you'll find an example of how to get the info. I'm not sure if all the data is in the end report, but maybe you can use this as a first step. And ofcourse, the end XML file has to be created.

Cheers,

-Fred-

PS: Tested this on my laptop with a limited number of files. Not sure how this will act on environments with hundreds of mas/acx files

-* --- Get all acx files from server-path
SQL FMI SET SYSFILES ACCESS
TABLE FILE SYSFILES
  PRINT
    FILENAME
    PHNAME
  ON TABLE HOLD AS HLDACX FORMAT ALPHA
END
-RUN
-SET &ACX_COUNT = &LINES;


-* --- Assign ALL acx-files to variables
-REPEAT :ACX_READ FOR &I FROM 1 TO &ACX_COUNT;
-READ HLDACX &FINA.&I.A64 &PHNA.&I.A80
-:ACX_READ


-* --- Create default MASTER for ACX-files
FILEDEF masforacx DISK masforacx.mas
-RUN
-WRITE masforacx FILENAME=MASFORACX, SUFFIX=DFIX    , $
-WRITE masforacx  SEGMENT=MASFORACX, SEGTYPE=S0, $
-WRITE masforacx  FIELDNAME=E1, ALIAS=E01, USAGE=A64, ACTUAL=A64, $
-WRITE masforacx  FIELDNAME=E2, ALIAS=E02, USAGE=A64, ACTUAL=A64, $
-WRITE masforacx  FIELDNAME=DELIMITER ,ALIAS=',' ,USAGE=A1 ,ACTUAL=A1 ,$


-* --- Get ACX-files 1-by-1 and append to HOLD-file
FILEDEF ACXTOTAL DISK acxtotal.ftm (APPEND
-RUN


-*--- Loop for each found ACX-file
-REPEAT :ACX_WRITE FOR &I FROM 1 TO &ACX_COUNT;
FILEDEF masforacx DISK &PHNA.&I
-RUN


TABLE FILE masforacx
  PRINT
    COMPUTE FILENAME/A80 = '&FINA.&I';
    COMPUTE PHNAME/A80   = '&PHNA.&I';
    COMPUTE CONN/A80     = IF E01 CONTAINS 'CONNECTION' THEN E01 ELSE
                           IF E02 CONTAINS 'CONNECTION' THEN E02 ELSE '';
  WHERE TOTAL CONN NE '';
  ON TABLE HOLD AS ACXTOTAL FORMAT ALPHA
  ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-:ACX_WRITE


-* --- Dump to HOLD-file for MATCHing
TABLE FILE ACXTOTAL
  PRINT
    PHNAME
    CONN
  BY FILENAME AS TBNAME
  ON TABLE SET HOLDLIST PRINTONLY
  ON TABLE SET ASNAMES ON
  ON TABLE HOLD AS ACXFILES FORMAT ALPHA
END
-RUN


-* --- Get all column info for MATCHing
TABLE FILE SYSCOLUM
  PRINT
    TBNAME/A80
    NAME            AS 'COLUMNNAME'
    ALIAS
    COLNO
    COLTYPE
    LENGTH
    NULLS
  ON TABLE HOLD AS COLNAMES FORMAT ALPHA
  ON TABLE SET HOLDLIST PRINTONLY
  ON TABLE SET ASNAMES ON
END
-RUN

-*--- Match both HOLD-files
MATCH
  FILE ACXFILES
    SUM
      PHNAME
      CONN
    BY TBNAME
RUN
  FILE COLNAMES
    PRINT
      COLUMNNAME
      ALIAS
      COLNO
      COLTYPE
      LENGTH
      NULLS
    BY TBNAME
  AFTER MATCH HOLD AS HLDTOTAL
END
-RUN


TABLE FILE HLDTOTAL
  PRINT *
  BY TBNAME NOPRINT
END

This message has been edited. Last edited by: <FreSte>,

Hi Waz

For the particular Table how to modify the above code

Thanks
Gobinath Velusamy

CHECK FILE CAR HOLD

TABLE FILE HOLD
PRINT
*
ON TABLE PCHOLD FORMAT XML
END

You might have to get the necessary coulmns instead of * .

Save this to an EXCEL anf get the reqd column names.

quote:

For the particular Table how to modify the above code

Hi Gobinath,

Which table, and is it Freds code above ?

I found this old question and tried the solution FreSte has posted.
It works perfect even on tons of masters
The only change I made was to read more fields form the Acx file, since some have 3 or more fields .
The is one thing I need to solve and in my case some Acx files have also sometimes more then one line, and I did not get all these lines in my report.
I suppose there is a way to change that and get it done.
I try to add one other functionality and that would be to find all the fexes related to a certain master file.
I know it can be done by a text search, but I hope there is a better way, if I use the resource analyser in combination with the above.
Any suggestions are welcome.