I am trying to export 4 SAS Tables (all with unique Columns) as one XML using the XML92 generator. The tricky part is that one of the tables is supposed to act as a higher layer in the XML file.
As an example to test:
First creating some tables
DATA TMP.RootTable;
ATTRIB RTabCol1 Format = $20.;
ATTRIB RTabCol2 Format = $20.;
INPUT RTabCol1 RTabCol2;
DATALINES;
RTabCol1Val1 RTabCol2Val1
;
DATA TMP.Table1;
ATTRIB Tab1Col1 Format = $20.;
ATTRIB Tab1Col2 Format = $20.;
INPUT Tab1Col1 Tab1Col2;
DATALINES;
Tab1Col1Val1 Tab1Col2Val1
Tab1Col1Val2 Tab1Col2Val2
;
DATA TMP.Table2;
ATTRIB Tab2Col1 Format = $20.;
ATTRIB Tab2Col2 Format = $20.;
INPUT Tab2Col1 Tab2Col2;
DATALINES;
Tab2Col1Val1 Tab2Col2Val1
Tab2Col1Val2 Tab2Col2Val2
Tab2Col1Val3 Tab2Col2Val3
;
DATA TMP.Table3;
ATTRIB Tab3Col1 Format = $20.;
ATTRIB Tab3Col2 Format = $20.;
ATTRIB Tab3Col3 Format = $20.;
INPUT Tab3Col1 Tab3Col2 Tab3Col3;
DATALINES;
Tab3Col1Val1 Tab3Col2Val1 Tab3Col3Val1
Tab3Col1Val2 Tab3Col2Val2 Tab3Col3Val2
;
I am using the XML Generator
libname XMLOUT XML92 XMLTYPE=xmlmap XMLMAP=MyMAP;
With this map:
<?xml version="1.0"?>
<SXLEMAP name="xmlmap" version="1.9">
<OUTPUT>
<TABLEREF name="XMLTEST" />
</OUTPUT>
<TABLE name="XMLTEST">
<TABLE-PATH syntax="XPath">
/RootTable
</TABLE-PATH>
<COLUMN name="RTabCol1">
<PATH>/RTabCol1</PATH>
<DESCRIPTION>RTabCol1</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="RTabCol2">
<PATH>/RTabCol2</PATH>
<DESCRIPTION>RTabCol2</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab1Col1">
<PATH>/Table1/Tab1Col1</PATH>
<DESCRIPTION>Tab1Col1</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab1Col2">
<PATH>/Table1/Tab1Col2</PATH>
<DESCRIPTION>Tab1Col2</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab2Col1">
<PATH>/Table2/Tab2Col1</PATH>
<DESCRIPTION>Tab2Col1</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab2Col2">
<PATH>/Table2/Tab2Col2</PATH>
<DESCRIPTION>Tab2Col2</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab3Col1">
<PATH>/Table3/Tab3Col1</PATH>
<DESCRIPTION>Tab3Col1</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab3Col2">
<PATH>/Table3/Tab3Col2</PATH>
<DESCRIPTION>Tab3Col2</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
<COLUMN name="Tab3Col3">
<PATH>/Table3/Tab3Col3</PATH>
<DESCRIPTION>Tab3Col3</DESCRIPTION>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>20</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
Finally putting everything together:
DATA XMLOUT.FXMLTEST;
SET TMP.RootTable;
SET TMP.Table1;
SET TMP.Table2;
SET TMP.Table3;
RUN;
Now the SAS code works so far and that is what my results look like (I skipped the header):
<RootTable>
<RTabCol1>RTabCol1Val1</RTabCol1>
<RTabCol2>RTabCol2Val1</RTabCol2>
<Table1>
<Tab1Col1>Tab1Col1Val1</Tab1Col1>
<Tab1Col2>Tab1Col2Val1</Tab1Col2>
</Table1>
<Table2>
<Tab2Col1>Tab2Col1Val1</Tab2Col1>
<Tab2Col2>Tab2Col2Val1</Tab2Col2>
</Table2>
<Table3>
<Tab3Col1>Tab3Col1Val1</Tab3Col1>
<Tab3Col2>Tab3Col2Val1</Tab3Col2>
<Tab3Col3>Tab3Col3Val1</Tab3Col3>
</Table3>
</RootTable>
Now the problem is that in the XML only the first dataline is written. However I have only one dataline in the RootTable and multiple Datalines in the other tables, which belong to that one Dataline in the RootTable. I tried just copying the data from the RootTable into multiple lines, however then the Element <RootTable> is closed and reopens. So my desired Outcome would look something like this:
<RootTable>
<RTabCol1>RTabCol1Val1</RTabCol1>
<RTabCol2>RTabCol2Val1</RTabCol2>
<Table1>
<Tab1Col1>Tab1Col1Val1</Tab1Col1>
<Tab1Col2>Tab1Col2Val1</Tab1Col2>
</Table1>
<Table2>
<Tab2Col1>Tab2Col1Val1</Tab2Col1>
<Tab2Col2>Tab2Col2Val1</Tab2Col2>
</Table2>
<Table3>
<Tab3Col1>Tab3Col1Val1</Tab3Col1>
<Tab3Col2>Tab3Col2Val1</Tab3Col2>
<Tab3Col3>Tab3Col3Val1</Tab3Col3>
</Table3>
<Table1>
<Tab1Col1>Tab1Col1Val2</Tab1Col1>
<Tab1Col2>Tab1Col2Val2</Tab1Col2>
</Table1>
<Table2>
<Tab2Col1>Tab2Col1Val2</Tab2Col1>
<Tab2Col2>Tab2Col2Val2</Tab2Col2>
</Table2>
<Table3>
<Tab3Col1>Tab3Col1Val2</Tab3Col1>
<Tab3Col2>Tab3Col2Val2</Tab3Col2>
<Tab3Col3>Tab3Col3Val2</Tab3Col3>
</Table3>
<Table2>
<Tab2Col1>Tab2Col1Val3</Tab2Col1>
<Tab2Col2>Tab2Col2Val3</Tab2Col2>
</Table2>
</RootTable>
I do not know whether my mistake is in the XMLMAP or if there is a way to hand an option to the XML generator to flag the RootTable as an XML-Root.
Any help is highly appreciated, please consider that I am new to SAS. So I might have done some rookie mistakes.
When changing to:
DATA XMLOUT.XMLTEST;
if _n_ eq 1 then SET TMP.RootTable;
SET TMP.Table1;
SET TMP.Table2;
SET TMP.Table3;
RUN;
The Output changes to:
<RootTable>
<RTabCol1>RTabCol1Val1</RTabCol1>
<RTabCol2>RTabCol2Val1</RTabCol2>
<Table1>
<Tab1Col1>Tab1Col1Val1</Tab1Col1>
<Tab1Col2>Tab1Col2Val1</Tab1Col2>
</Table1>
<Table2>
<Tab2Col1>Tab2Col1Val1</Tab2Col1>
<Tab2Col2>Tab2Col2Val1</Tab2Col2>
</Table2>
<Table3>
<Tab3Col1>Tab3Col1Val1</Tab3Col1>
<Tab3Col2>Tab3Col2Val1</Tab3Col2>
<Tab3Col3>Tab3Col3Val1</Tab3Col3>
</Table3>
</RootTable>
<RootTable>
<RTabCol1>RTabCol1Val1</RTabCol1>
<RTabCol2>RTabCol2Val1</RTabCol2>
<Table1>
<Tab1Col1>Tab1Col1Val2</Tab1Col1>
<Tab1Col2>Tab1Col2Val2</Tab1Col2>
</Table1>
<Table2>
<Tab2Col1>Tab2Col1Val2</Tab2Col1>
<Tab2Col2>Tab2Col2Val2</Tab2Col2>
</Table2>
<Table3>
<Tab3Col1>Tab3Col1Val2</Tab3Col1>
<Tab3Col2>Tab3Col2Val2</Tab3Col2>
<Tab3Col3>Tab3Col3Val2</Tab3Col3>
</Table3>
</RootTable>
So yes, now I have more Values from the other Tables, however my Issue is that `<RootTable>` opens and closes again. However I need the other Values to be in the first (and only) bracket of `<RootTable></RootTable>`.
