DATA Step, Macro, Functions and more

Reading XML into SAS

Reply
Contributor
Posts: 56

Reading XML into SAS

Hi,

We have about 1,600 xml files with different map to be read into SAS. It is extremely painful to run the XML mapper manualy and then use Libname XML to import into SAS. Is there any macro sample that can run batch  XML mapper and XML engine to sutomate the import of these XML files ? or there are other better idea ?

Thanks for all your help !

David

SAS Super FREQ
Posts: 8,743

Reading XML into SAS

Hi,

  I do not believe that the XML Mapper works in batch mode -- it is a GUI application that allows you to construct a map. There is no "batch" syntax for the XML Mapper.

  If you understand the layouts (schemas) of the 1600 XML files, you can 1) construct the maps manually with Notepad or 2) use some 3rd party product (like XMLSpy, etc) to transform your XML into the form of XML that the Libname engine can read without a map (this web site explains what that simple structure is: http://support.sas.com/documentation/cdl/en/engxml/63177/HTML/default/viewer.htm#p09k7u3i36vvjqn18ai... ). For more information, the FAQ about the SAS XML Libname Engine is here: http://support.sas.com/documentation/cdl/en/engxml/63177/HTML/default/viewer.htm#p1vxxcj6ammc8jn1b2f...

  How are you getting the 1600 XML files? Is there a possibility that the process that creates the XML could send you XML files in the structure that can be read without a map? There are some XMLTYPES that the XML Libname Engine does know about. For example, three such external XML Types are shown here: http://support.sas.com/documentation/cdl/en/engxml/63177/HTML/default/viewer.htm#n1p6kbmn43fz0en1taj... and they are CDISCODM, MSACCESS and ORACLE -- so if the process that creates the XML could generate MSACCESS type of XML, you would use the LIBNAME engine with XMLTYPE=MSACCESS option (and would not need an XMLMAP).

cynthia

Contributor
Posts: 56

Reading XML into SAS

Hi Cynthia,

Thanks for your info. Unfortunately it is impossible to get the XML file stucture. What we basically need is a command line xml mapper (instead of the interactive) .

1. Do you think the PROC XSL has any potential to map the xml  files ?

2. Is the SAS ACCESS/ODBC has any capabilities to convert XML files ?

We try to stay with SAS to maintain data consistency  instead of going out to third party such XLMSPY unless all resource is exhausted.

Thanks

david

SAS Super FREQ
Posts: 8,743

Reading XML into SAS

Hi:

  I thought that PROC XSL did transformations from one structure to another -- not the same as generating a MAP file. http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003356144.htm

  These really are questions for the experts in Tech Support.

cynthia

Super User
Posts: 9,681

Re: Reading XML into SAS

Hi. David.

When I open XML Mapper to import a xml file.

I found a Sheet named SAS Code example at the bottom of Windows.

In it there is some code generated by SAS automatically. Like:

/********************************************************************************

*  Generated by XML Mapper, 9.2.0.000000_v920c_20110125_21893

********************************************************************************/

/*

*  Environment

*/

filename  aedata 'C:\aedata.xml';

filename  SXLEMAP '<mapName>.map';

libname   aedata xml xmlmap=SXLEMAP access=READONLY;

/*

*  Catalog

*/

proc datasets lib=aedata; run;

/*

*  Contents

*/

/*

*  Printing

*/

/*

*  Local Extraction

*/

Maybe you can copy them out and use macro to loop them.

Ksharp

Contributor
Posts: 56

Reading XML into SAS

Thanks.

Super Contributor
Posts: 356

Reading XML into SAS

But doesn't this code rely on the map being defined?  I thought the OP wanted an automatice way of defining the XML Map which is required to read in the data with code.

Barry

SAS Super FREQ
Posts: 8,743

Reading XML into SAS

Yes, that's the way I read the OP, as well. In order to get to the code tab in the XML Mapper, you have to point and click your way around the tool to build a map. I thought the original desire was NOT to use the tool because the 1600+ XML files all had a different structure. Although, given the nature of XML and automated systems, it seems really hard to understand a system that spits out a differently structured XML file for each of 1600 files. If the 1600 files had the same structure, but different XML data, then you'd only need to use the XML Mapper 1 time to get a working map file and then you would automate using the same map file on each of the 1600 XML files in an automated fashion, probably with a SAS macro. The folks who generated the 1600 XML files should be able to provide some structure information, even if they did not share it with the OP.

cynthia

Trusted Advisor
Posts: 1,300

Reading XML into SAS

I cannot test the option I will outline because it is not available under my OS.

Using DDE open the xml file in Excel and use it to convert the schema into a usable for and then extract the data from excel.  This will only be possible on a windows machine with excel installed.

Another option: use a preprocess in another lanuage to conver the xml to csv and the use proc import.  There are a lot of tools available to do this in a number of different languages.

Contributor
Posts: 56

Reading XML into SAS

Thanks for your answer.

I think the DDE only works if the XML file is generic. According to SAS support, the mapping capable will be incorprated into the XML engine in a few months and the process will be seamless.

Valued Guide
Posts: 2,175

Reading XML into SAS

If these 1600 XML files are valid workbooks for excel then probably one map will do.

Good luck

Contributor
Posts: 56

Reading XML into SAS

The challenge for us is we don't know how many variations of mapping in these 1600 XML files. We know for sure there is more than 1 mapping type that is not conform to excel format.

Thanks

SAS Super FREQ
Posts: 8,743

Reading XML into SAS

I agree with Peter. If you can open the XML files in Excel, then the XML should conform to either the Excel XML 2003 spec or the 2007 spec, as set forth by Microsoft. You should only need 1 map, and only for Excel 2003 XML files. The SAS Libname Engine should be able to read XLSX files (2007 flavor of Office XML) directly, without an XML map - -at least if you have SAS 9.2 phase 2 or higher, per this Tech Support note:  http://support.sas.com/kb/32/455.html

cynthia

Valued Guide
Posts: 2,175

Reading XML into SAS

if it does not conform to excel format, how would DDE help?

Trusted Advisor
Posts: 1,300

Reading XML into SAS

Excel can open any valid XML document.  There is no reason for it to be required to be in the Excel XML or Office XML spec.  Those are for opening XML files as if they were native excel documents.  Give it a try and open an XML document in Excel and you will see that it will convert the document to a usable table format.

Try this file for example: http://www.w3schools.com/xml/plant_catalog.xml

When you open it with Excel you will be prompted:

Untitled.png

Untitled.png

Untitled.png

So, Excel can absolutley open the file and derive a schema from a regular XML file.  Can DDE automate this and extract the data?  That I do not know and cannot test...

Ask a Question
Discussion stats
  • 16 replies
  • 853 views
  • 0 likes
  • 7 in conversation