Hi ,

I have an xml file like this :

<RECORDS>  
   <RECORD>
      <PROP NAME="dimension_id">
         <PVAL>9927083</PVAL>
      </PROP>
      <PROP NAME="external_id">
         <PVAL>34257_34259</PVAL>
      </PROP>
   </RECORD>
  <RECORD>
      <PROP NAME="dimension_id">
         <PVAL>9927082</PVAL>
      </PROP>
      <PROP NAME="external_id">
         <PVAL>34257_34260</PVAL>
      </PROP>
   </RECORD>
   <RECORD>
      <PROP NAME="dimension_id">
         <PVAL>9997319</PVAL>
      </PROP>
      <PROP NAME="external_id">
         <PVAL>620270</PVAL>
      </PROP>
   </RECORD>
   <RECORD>
      <PROP NAME="dimension_id">
         <PVAL>9997319</PVAL>
      </PROP>
      <PROP NAME="external_id">
         <PVAL>620273</PVAL>
      </PROP>
      <PROP NAME="internal_id">
         <PVAL>9975999</PVAL>
      </PROP>
   </RECORD>
<RECORD>
      <PROP NAME="dimension_id">
         <PVAL>9997319</PVAL>
      </PROP>
      <PROP NAME="external_id">
         <PVAL>620274</PVAL>
      </PROP>
      <PROP NAME="internal_id">
         <PVAL>9975998</PVAL>
      </PROP>
   </RECORD>
</RECORDS>

I am creating a master file out of this and the output looks like this

NAME PVAL
dimension_id 9927083
external_id 34257_34259
dimension_id 9927082
external_id 34257_34260
dimension_id 9997319
external_id 620270
dimension_id 9997319
external_id 620273
internal_id 9975999
dimension_id 9997319
external_id 620274
internal_id 9975998


For the below code , am getting teh output like

  
SET ACROSSPRT = COMPRESS

TABLE FILE TEST_XML
PRINT
PVAL
ACROSS NAME
END

Output :

dimension_id external_id internal_id
9927083 34257_34259 9975999
9927082 34257_34260 9975998
9997319 620270 .
9997319 620273 .
9997319 620274 .

But i need the output like this :

dimension_id external_id internal_id
9927083 34257_34259 .
9927082 34257_34260 .
9997319 620270 .
9997319 620273 9975999
9997319 620274 9975998

Can anyone help me on this ?
This is in version 7.6.9

Thanks!

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

Adding on to this , i tried the following way ,

 
DEFINE FILE EGEE_XML_AUTOGENDIMENSIONEXTERNAL
DIMID/A100 = IF NAME EQ 'dimension_id' THEN PVAL ELSE '';
-*DIMID/A100 = IF NAME EQ 'dimension_id' THEN PVAL ELSE IF NAME EQ 'external_id' THEN PVAL ELSE IF NAME EQ 'internal_id' THEN PVAL ELSE '';
EXTID/A100 = IF NAME EQ 'external_id' THEN PVAL ELSE '';
INTID/A100 = IF NAME EQ 'internal_id' THEN PVAL ELSE '' ;
A/A2='A';
END

TABLE FILE EGEE_XML_AUTOGENDIMENSIONEXTERNAL
PRINT
RECORD NOPRINT
DIMID
EXTID
INTID
BY A
-*BY DIMID NOPRINT
-*BY EXTID NOPRINT
-*ACROSS NAME
-*ON TABLE HOLD AS HLD_TEST
END

The out put comes in the below format . How do i merge these into a singlr row for a dimension id

dimension_id external_id internal_id
9927083
34257_34259
.
9927082
34257_34260
.

Thanks!

The first result you get is in fact a field name and a value in one table
You need to convert that firts to a real table that holds two fields and a value

I tried that as well and it didnt work .

Example:

DEFINE FILE wsdemo/XMLTEST2
RECID/I5 WITH RECORD = LAST RECID + 1;
END
TABLE FILE wsdemo/XmlTEST2
SUM PVAL
ACROSS NAME
BY RECID
END
  

My master

FILENAME=WSDEMO_XMLTEST2, SUFFIX=XML     ,
 DATASET=wsdemo/xmltest2.xml, $
  SEGMENT=RECORDS, SEGTYPE=S0, $
    FIELDNAME=RECORDS, ALIAS=RECORDS, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL), $
  SEGMENT=RECORD, SEGTYPE=S0, PARENT=RECORDS, $
    FIELDNAME=RECORD, ALIAS=RECORD, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=RECORDS, PROPERTY=ELEMENT,  $
  SEGMENT=PROP, SEGTYPE=S0, PARENT=RECORD, $
    FIELDNAME=PROP, ALIAS=PROP, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=RECORD, PROPERTY=ELEMENT,  $
    FIELDNAME=PVAL, ALIAS=PVAL, USAGE=A10, ACTUAL=A10,
      REFERENCE=PROP, PROPERTY=ELEMENT,  $
    FIELDNAME=NAME, ALIAS=NAME, USAGE=A10, ACTUAL=A10,
      REFERENCE=PROP, PROPERTY=ATTRIBUTE,  $
  

Result

  PAGE     1
  
  
         NAME 
         dimension_  external_i  internal_i  
  RECID                                      
  -------------------------------------------
      1  9927083     34257_3425  .           
      2  9927082     34257_3426  .           
      3  9997319     620270      .           
      4  9997319     620273      9975999     
      5  9997319     620274      9975998      

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