topic Re: XML data extraction without data map in SAS Data Management

XML data extraction without data map

DMoovendhan — Tue, 16 Feb 2016 15:46:18 GMT

hi, I need to extract data from an XML file. Is there any way that I can do it without an XML map and without the default XML variables that comes in the dataset. Thanks, Moovendhan Devaraj

Re: XML data extraction without data map

LinusH — Tue, 16 Feb 2016 16:13:08 GMT

Can you please motivate your requirement?

Re: XML data extraction without data map

Reeza — Tue, 16 Feb 2016 18:17:10 GMT

Are you referring to an external XML map or one that SAS maps?

Re: XML data extraction without data map

ballardw — Tue, 16 Feb 2016 22:36:01 GMT

A guess and likely not the most helpful if you have many files, but open the XML in a spreadsheet program. If the data looks "good" save to a CSV and import that perhaps with datastep code to set variable names as you would like.

Re: XML data extraction without data map

Ksharp — Wed, 17 Feb 2016 02:23:12 GMT

Yes. Data step can do that . But that depend on how complicated the XML file is .

Or you could check xml2 engine. Like : libname x xml2 '......' automap=replace;

Re: XML data extraction without data map

DMoovendhan — Wed, 17 Feb 2016 07:00:35 GMT

Let me try this, hope this works....

Re: XML data extraction without data map

DMoovendhan — Wed, 17 Feb 2016 07:10:50 GMT

Sorry for the confusion,

I should have given more specific requirment.

I have zipped and attached an example XML, and this needs to be imported without an XML map.

I have to automate this in such a way that any xml file that is been provided needs to be imported without any issue.

The column names would change with different XML files.

Kindly help.

Re: XML data extraction without data map

Ksharp — Wed, 17 Feb 2016 08:44:21 GMT

OK. Here is .

libname nhl xmlv2 '/folders/myfolders/LabReviewMicroSampleData.xml'
automap=replace
xmlmap='/folders/myfolders/NhlGenerate.map';
proc contents data=nhl._all_ nodetails;
run;
proc print data=nhl.Sex;run;

Re: XML data extraction without data map

DMoovendhan — Wed, 17 Feb 2016 11:12:37 GMT

Hi Xia,

In this solution you have used 'nhlgenerate.map' import the data form the XML file.

Is there any way that we can import the data without any XML map been created?

When I tried creating a xml map using XML automap, I getting error due to unavailability of formats and informats. Is it possible for you to share the map, that you used?

Re: XML data extraction without data map

DMoovendhan — Wed, 17 Feb 2016 11:15:55 GMT

tried this soultion and sas throws an error as below,

ERROR 22-7: Invalid option name AUTOMAP.

I am using sas version 9.3 in my windows PC. does this option work for you, or is it used in some different version of SAS.

Re: XML data extraction without data map

DMoovendhan — Wed, 17 Feb 2016 11:22:52 GMT

Hi,

the XML file is not properly organised when I opened it as an excel file.

I am attaching the XML file for your reference.

Thanks,

Moovendhan Devaraj

Re: XML data extraction without data map

Reeza — Wed, 17 Feb 2016 13:50:29 GMT

Run the code as is. SAS is creating the map using the automap feature.

Re: XML data extraction without data map

BillM_SAS — Wed, 17 Feb 2016 22:04:58 GMT

The AUTOMAP option was not available in the XMLV2 LIBNAME engine until the second maintenance release of SAS 9.3. Based on the error message, I suspect you are not at that maintenance level of SAS 9.3.

Re: XML data extraction without data map

Ksharp — Thu, 18 Feb 2016 01:01:03 GMT

xmlmap='/folders/myfolders/NhlGenerate.map';

is genereated by SAS . You can assign it any name like x.map yy.map . No need to consider about it.

If you SAS doesn't has such ability ,try SAS University Edition.

Re: XML data extraction without data map

DMoovendhan — Thu, 18 Feb 2016 06:57:41 GMT

you are correct, I am using sas 9.3. Probably not that version that has auto map option.

Is there any way that I can have that enabled in the current version of SAS, is there any path that needs to be installed to enable this.

Re: XML data extraction without data map

BillM_SAS — Tue, 01 Mar 2016 19:26:01 GMT

As noted, the easiest way to do this is with the XMLV2 LIBNAME engine and the AUTOMAP option. By using AUTOMAP=REPLACE, the map file is regenerated each time and is specific to the XML file specified. If you don't like the idea of keeping the map file around between runs, you can automatically throw it away by using a temporary map file:

filename test 'c:\LabReviewMicroSampleData.xml';
filename tempmap temp;
libname test xmlv2 xmlmap=tempmap automap=replace;

I had no problems using this method to create data sets from your posted XML file.