Importing a .xml file in SAS EG 7.15

[ghartge]

Good morning everyone!

Can I get some help with importing a XML file using SAS EG? I have tried a couple of things I found here, but I cannot get them to work. I have never used an XML file so it is all new. I only have the XML file, there are no other accompanying files.

I have attempted to use the import functionality in EG to no avail. I have also attempted to import the file into Excel to then import to SAS without any success either.

Here is the top of the file. This top section is then followed by STYLE tags and the data is at the bottom in TABLE, ROW and CELL tags. It seems to have been created by SAS.

Thank you as always.

<?xml version="1.0" encoding="windows-1252" ?>
<?mso-application progid="Excel.Sheet"?>
- <!-- Generated by the SAS Excelxp Tagset .v1.130, 08/02/2013
-->
- <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
- <DocumentProperties xmlns="urn:schemas-microsoft-com:office">
<Author>victoria.garcia</Author>
<LastAuthor>victoria.garcia</LastAuthor>
<Created>2021-02-22T11:00:54</Created>
<LastSaved>2021-02-22T11:00:54</LastSaved>
<Company>SAS Institute Inc. http://www.sas.com</Company>
<Version>9.04.01M1P12042013</Version>
</DocumentProperties>

[Ksharp]

filename z temp;
libname x xmlv2 'D:\XiaKeShan\my_code\define_xml\define_xml\define.xml' automap=replace xmlmap=z;
proc copy in=x out=work noclone;
run;

[ghartge]

Thank you Ksharp,

As I mentioned, I have no experience with XML files so the code you provide has me scratching my head. The ERRLST.XML is the only file I have. It appears you reference a "define.xml" file?

Specifically;

ERROR: With the AUTOMAP= option, the specified XML document must exist.

here is the log;

32 filename z temp;
33 libname x xmlv2 'D:\ERRLST.XML' automap=replace xmlmap=z;
ERROR: With the AUTOMAP= option, the specified XML document must exist.
ERROR: Error in the LIBNAME statement.

34 proc copy in=x out=work noclone;
35 run;

ERROR: Libref X is not assigned.

[Ksharp]

Your code looks right.

Make sure the path of XML file (D:\ERRLST.XML) is right .

XML file must be located at SAS Server, not your local pc .

and " D:\ " is also the path at  SAS Server side , and not your local pc .

Here is my log.

1    filename z temp;
2    libname x xmlv2 'D:\XiaKeShan\my_code\define_xml\define_xml\define.xml' automap=replace xmlmap=z;
NOTE: Processing XMLMap version 2.1.
NOTE: Libref X was successfully assigned as follows:
      Engine:        XMLV2
      Physical Name: D:\XiaKeShan\my_code\define_xml\define_xml\define.xml
3    proc copy in=x out=work noclone;
NOTE: Writing HTML Body file: sashtml.htm
4    run;

NOTE: Copying X.Alias to WORK.ALIAS (memtype=DATA).
NOTE: System Options for BUFSIZE and REUSE were used at user's request.
NOTE: Libname and/or system options for compress, pointobs, data representation and encoding attributes were used at user's request.
NOTE: There were 1 observations read from the data set X.Alias.
NOTE: The data set WORK.ALIAS has 1 observations and 4 variables.

[Sajid01]

From the following it is my understanding that your xml has been generated by SAS ExcelXP tagset.
- <!-- Generated by the SAS Excelxp Tagset .v1.130, 08/02/2013
-->

There may be many approaches to import these into SAS.
One simple approach is to open this xml file in Excel and save as xlsx file.
(It can be done programmatically using vb script if it is desired to automate.).
Then it can be imported as xlsx..

There is a forum posting on this
https://communities.sas.com/t5/Integration-with-Microsoft/How-to-import-XML-file-produced-by-ods-tag... 

My under

[AlanC]

This somewhat looks like an Excel file saved as XML. Try something simple to start. Change the file extension to xlsx and see if Excel opens it. I doubt it but worth a shot.

[UPDATE: You need to make sure you zip the file first then change the extension]

Excel xlsx format is merely a zip containing XML files. This looks like the main body file of an Excel file.

Also, don't get too worried about XML. It is merely 2 parts: elements and attributes. Elements are like:

<LastName>Smith</LastName>

Attributes occur within the element:

<LastName type="string">

There are no secrets with XML. It is 100% dependent on the engine that reads them. The developer determines what elements and attributes to use. If you want to have an application handle it for you, and convert it to something else, look at the online XML engines like: XML To CSV Converter (convertcsv.com). There are lots out there (search XML to CSV, for example). The XML you have is Excel XML. Try and use its engine to start and don't try and parse it yourself. 

[Sajid01]

The xls files generated by SAS Excelxp Tagset are xml files.
They have xls extension instead of xml.
The simplest approach is to open the file in excel and save either manually or programmatically.
Have a look here https://support.sas.com/kb/43/496.html