Writing multiple SAS Tables into XML with more than one layer XML92 engine

Nils2 — Tue, 12 Oct 2021 15:25:32 GMT

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>`.

topic Writing multiple SAS Tables into XML with more than one layer XML92 engine in SAS Programming

Writing multiple SAS Tables into XML with more than one layer XML92 engine