BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
val_nikolajevs
Obsidian | Level 7

Hello,

How to generate a xml file from multiple SAS tables?

I have created a xml map file in SAS XML Mapper using auto generate function. The results contain 3 different tables (x,y,z), with added columns that end with ORDINAL--which looks like IDs to map tables together.

Reading from xml file to sas tables is seamless and seems to be working as expected.

 

How do I assemble the xml file back from 3 SAS tables after I add/modify records in any of  the tables?

the SAS XML Mapper has only one output table, so if in the output table I set up a specific table let say x1 as an output, I am able to generate xml file that would contain data only from x1 table. How to add and link data from all 3 tables?

Thank you.

filename out '\Imports\SampleFileOut.xml';
filename  SXLEMAP '\Imports\sample.map';
libname out xmlv2  xmlmap=SXLEMAP;

data out.x1;
   set  SampleFi.x1;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Seems simple enough to generate.

Let's make some data to work with.

data have;
  ExternalId=123;
  do sequence=10,20,30;
    output;
  end;
run;

Now let's make an XML file.

filename want 'example.xml';
data _null_;
  set have end=eof;
  by ExternalId ;
  file want;
  if _n_=1 then put 
  '<UploadFile>'
  ;
  if first.ExternalId then put
  '  <UploadGroup>'
/ '    <ExternalId>' ExternalId +(-1) '</ExternalId>'
  ;
  put
  '    <UPLOAD_QTY>'
/ '      <sequence>' sequence +(-1) '</sequence>'
/ '    </UPLOAD_QTY>'
  ;
  if last.ExternalId then put
  '  </UploadGroup>'
  ;
  if eof then put 
  '</UploadFile>'
  ;
run;

Result

<UploadFile>
  <UploadGroup>
    <ExternalId>123</ExternalId>
    <UPLOAD_QTY>
      <sequence>10</sequence>
    </UPLOAD_QTY>
    <UPLOAD_QTY>
      <sequence>20</sequence>
    </UPLOAD_QTY>
    <UPLOAD_QTY>
      <sequence>30</sequence>
    </UPLOAD_QTY>
  </UploadGroup>
</UploadFile>

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

I doubt you can.  On this help page  https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/engxml/n0kt78umyfvt91n1d5fyqamp3za8.htm

It says

Restriction: The engine supports exporting from one SAS data set only.

 

Requirements If you specify the OUTPUT element, you must specify one (and no more than one) TABLEREF element.

 

I suspect if you want to write a more complicated XML file just write the file yourself using a data step and PUT statement(s).   XML is just text after all.

 

I believe there are other tools for transforming XML files.  So perhaps you could merge your datasets together and then EXPORT the one dataset.  Then call one of those external XML re-mapper tools to restructure it into what you want.

val_nikolajevs
Obsidian | Level 7

Thank you for the reply;

I have found this article: https://support.sas.com/documentation/cdl/en/engxml/62845/HTML/default/viewer.htm#a002593019.htm

and was able to create a map file that generates a single table for the data in to SAS;however, My output, which based on only one table, still not getting desired results; in the article they were able to divide by Conference, division and only after by teams. Essentially creating nested/hierarchical structure.

 

The output I am looking to get is below (simplified--the hierarchy is more complex); how to create output from a single table that creates below.

<UploadFile>
     <UploadGroup>
         <ExternalId>123</ExternalId>
        <UPLOAD_QTY>
            <sequence>10</sequence>
       </UPLOAD_QTY>

        <UPLOAD_QTY>
            <Quantity>20</Quantity>
       </UPLOAD_QTY>

        <UPLOAD_QTY>
            <Quantity>30</Quantity>
       </UPLOAD_QTY>

 </UploadGroup>

</UploadFile>

 

Using the following map,

<SXLEMAP name="AUTO_GEN" version="2.1">

<!-- ############################################################ -->
<OUTPUT>
<TABLEREF name="UPLOAD_QTY"/>
</OUTPUT>

<NAMESPACES count="0"/>

<!-- ############################################################ -->
<TABLE description="UPLOAD_QTY" name="UPLOAD_QTY">
<TABLE-PATH syntax="XPath">/UploadFile/UploadGroup/UPLOAD_QTY</TABLE-PATH>

<COLUMN name="ExternalId" retain="YES">
<PATH syntax="XPath">/UploadFile/UploadGroup/ExternalId</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>10</LENGTH>
</COLUMN>

<COLUMN name="Quantity">
<PATH syntax="XPath">/UploadFile/UploadGroup/UPLOAD_QTY/Quantity</PATH>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>


</TABLE>

</SXLEMAP>

The above map for some reason does not separate by UploadGroup as in the article by Conference. How to set xpath or another parameter/s so UploadGroups are separated?

Instead of geting:

<UploadFile>
     <UploadGroup>
         <ExternalId>123</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>

 </UploadGroup>

<UploadGroup>
         <ExternalId>234</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>

 </UploadGroup>

</UploadFile>

I got

<UploadFile>
     <UploadGroup>
         <ExternalId>123</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>
         <ExternalId>234</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>

 </UploadGroup>

</UploadFile>

Thank you.

 

Tom
Super User Tom
Super User

Seems simple enough to generate.

Let's make some data to work with.

data have;
  ExternalId=123;
  do sequence=10,20,30;
    output;
  end;
run;

Now let's make an XML file.

filename want 'example.xml';
data _null_;
  set have end=eof;
  by ExternalId ;
  file want;
  if _n_=1 then put 
  '<UploadFile>'
  ;
  if first.ExternalId then put
  '  <UploadGroup>'
/ '    <ExternalId>' ExternalId +(-1) '</ExternalId>'
  ;
  put
  '    <UPLOAD_QTY>'
/ '      <sequence>' sequence +(-1) '</sequence>'
/ '    </UPLOAD_QTY>'
  ;
  if last.ExternalId then put
  '  </UploadGroup>'
  ;
  if eof then put 
  '</UploadFile>'
  ;
run;

Result

<UploadFile>
  <UploadGroup>
    <ExternalId>123</ExternalId>
    <UPLOAD_QTY>
      <sequence>10</sequence>
    </UPLOAD_QTY>
    <UPLOAD_QTY>
      <sequence>20</sequence>
    </UPLOAD_QTY>
    <UPLOAD_QTY>
      <sequence>30</sequence>
    </UPLOAD_QTY>
  </UploadGroup>
</UploadFile>
val_nikolajevs
Obsidian | Level 7

Thank you Tom ! 🙂

Due to time constraints for xml mapping end up  diving in to the "put" solution too. 

THANK you.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 243 views
  • 1 like
  • 2 in conversation