BookmarkSubscribeRSS Feed
Kwok
Calcite | Level 5

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

16 REPLIES 16
Cynthia_sas
SAS Super FREQ

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

Kwok
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

Ksharp
Super User

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

Kwok
Calcite | Level 5

Thanks.

twocanbazza
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

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

FriedEgg
SAS Employee

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.

Kwok
Calcite | Level 5

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.

Peter_C
Rhodochrosite | Level 12

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

Good luck

Kwok
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

Peter_C
Rhodochrosite | Level 12

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

FriedEgg
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 16 replies
  • 2632 views
  • 0 likes
  • 7 in conversation