Writing multiple SAS Tables into XML with more than one layer

Question

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

EDIT: When changing to:

DATA XMLOUT.FXMLTEST;
    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>.

Post on communities.sas.com for faster answers and help from the developers. For anything related to XML/JSON you'll get faster more in depth answers there. — Reeza
– Reeza, Commented Oct 7, 2021 at 16:10
Are you trying to write multiple independent tables into one XML file using XML92 engine? Are you sure that is even possible? Why not just generate the XML yourself using data steps and PUT statements? — Tom
– Tom, Commented Oct 7, 2021 at 22:01
@Reeza thank you so much for that hint. I will try that as well. — Nils
– Nils, Commented Oct 11, 2021 at 7:06
@Tom The whole program I am working on is much more complex and I fear it will be very difficult to maintain the code when putting the XML generation together with PUT statements. However, I am not sure if it will be possible to generate the XML using XML92 engine. If not, of course I will have no choice but using PUT statements. — Nils
– Nils, Commented Oct 11, 2021 at 7:12

Joe · Accepted Answer · 2021-10-07 16:40:21Z

1

If the issue is that it's only reading one row from each, then the reason is pretty simple and not related to XML...

DATA XMLOUT.FXMLTEST;
    SET TMP.RootTable;
    SET TMP.Table1;
    SET TMP.Table2;
    SET TMP.Table3;
RUN;

That reads until one of the tables runs out of rows. That happens at row 2!

Try this:

DATA XMLOUT.FXMLTEST;
    if _n_ eq 1 then SET TMP.RootTable;
    SET TMP.Table1;
    SET TMP.Table2;
    SET TMP.Table3;
RUN;

I'm not sure if that will fix your issue - I wasn't able to directly use your map, it caused some xml parsing errors I don't have the time to fix - but it is why you have only one row from the latter tables.

answered Oct 7, 2021 at 16:40

Joe

63.5k7 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nils Over a year ago

Thank you so much for your time. I checked the XML Map and fixed the errors. Now it should work :)

Nils Over a year ago

Maybe you find the time to take a look at it again. Also I tested your suggestion and added some text to my original question. I hope that that will clarify my issue.

Collectives™ on Stack Overflow

Writing multiple SAS Tables into XML with more than one layer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related