To import XML file (Hierarchical structured) into SAS

[Jessy]

Hi,
I am trying to import XML file (which looks like the one attached) into SAS EG 6.1, to convert into a SAS dataset. This attached XML file, I am importing is not a well formatted file, it has a hierarchical XML structure and hence using the libname statement does  not work.

I have two options to do this,

1. To open the XML file and then save it as Excel file and then import it into SAS EG.
2. Use a SAS XML Mapper tool to import this XML file and then generate a XML map from the XML structure.
   -Copy the generated SAS code and use it in SAS EG 5.1, make necessary changes to the code and then go from there.

Neither of the above options work for me, because it had to be done manually. I am supposed to write a program in SAS EG, to import that XML file into a SAS data set and this same program will be scheduled to run every month (automated program).

Can somebody suggest me on how to import a hierarchical XML file into SAS programmatically.

Or, if you have any other options, like any other tools or software to work with XML files, please share here.

Thank you.

[Tom]

I would follow the XML mapper process.  That is "writing a program" that you can run in EG to read the XML data.  It just happens that part of the program is the XML map file needed to make the XML engine work for this file.  If you need to you can create a program that uses a data step to write the xml map file.

filename xmlmap temp;

data _null_;

  file xmlmap;

  put '......';

run;

libname in xml 'xml.file.xml' xmlmap=xmlmap;

[Jessy]

Hi Tom,

Thanks for looking into it. I am not an expert with XML stuff. Can you be more clear on this. How does this converts the XML into a SAS data set?

Thank you.

[Tom]

The XML libname engine will allow you to read XML files into datasets.  But for it to work you need to create instructions for how the items and attributes in your XML sheet map to SAS variables in the table.  You can begin reading more about it here:  SAS(R) 9.3 XML LIBNAME Engine: User's Guide, Second Edition

Once you have created a map that you can use to convert your XML to a nice rectangular table structure (or multiple tables if need be) then you can being working with the data in SAS.

[Reeza]

Does the map have any correspondence to an XSD file?

[Jessy]

Yes, it has a corresponding XSD file. Does it had to do something with XML and SAS dataset.

[Reeza]

The XSD file should help you generate your XML map to feed the map portion. You'll still have to read up on how to do this though.

[RobertHuey]

How can you use the XSD file to import your XML file?

[Reeza]

I never figured that out.

Bruno's method worked for me.

[BrunoMueller]

Since SAS9.3 there is an AUTOMAP= option for the XMLV2 libname engine. This will create the map file automatically for you. See more details here SAS(R) 9.3 XML LIBNAME Engine: User's Guide, Second Edition the below sample code was taken from the doc:

filename myxml 'C:\temp\xmlexample.xml';
filename mymap 'C:\temp\xmlexample_generate.map';

libname myxml xmlv2 automap=replace xmlmap=mymap;

proc contents data=myxml._all_;
run;

Please be aware that this might create quite a number of datasets depending on the nesting levels of you XML, yours has quite some nesting. These data sets then need to be joined together.

Since the XMLMAP= file is only a text file, in can be easily created using a DATA Step like the one below:

filename mymap temp;
data _null_;
  infile cards;
  input;
  file mymap;
  put _infile_;
cards4;
<?xml version="1.0" encoding="UTF-8"?>
<SXLEMAP version="2.1">
    <!-- ############################################################ -->
    <TABLE description="CLASS" name="CLASS">
        <TABLE-PATH syntax="XPath">/TABLE/CLASS</TABLE-PATH>
        <COLUMN name="Name">
            <PATH syntax="XPath">/TABLE/CLASS/Name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>7</LENGTH>
        </COLUMN>
        <COLUMN name="Sex">
            <PATH syntax="XPath">/TABLE/CLASS/Sex</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>1</LENGTH>
        </COLUMN>
    </TABLE>
</SXLEMAP>
;;;;

libname myxml xmlv2 "c:\temp\myclass.xml" xmlmap=mymap;
proc copy in=myxml out=work;
run;
libname myxml clear;

[ptimusk]

I can also import schemes this way to make maps that work in XML mapper.

[BillM_SAS]

As Bruno points out, the AUTOMAP option can produce a large number of SAS data sets in the process of flattening the XML file. If your XML files are of the same format, they should be able to use just 1 XML Mapper file. So by creating a custom XML Mapper file using the XML Mapper application, you could produce an XML Mapper file that creates only the data sets you need. There is no need to create the XML Mapper file via DATA step code. Once the file exists, just reference it from the XMLMAP option. I discuss these issues and have examples in my 2013 SAS Global Forum paper.

http://support.sas.com/resources/papers/proceedings13/024-2013.pdf

For the AUTOMAP option, see pages 5-10. For tailoring an XML Mapper file, see pages 14-16. Note that you can download all the example code I use in the paper from here: http://support.sas.com/rnd/papers/sasgf13/024_2013_paper_examples.zip