Reading XML files via XML92 getting 0 observation datasets

[JohnG]

SAS v9.22, WinXP, XML Mapper

I've manually created map file from a complex schema and am using the XML92 engine to read in the XML data files. I have successfully tested this method on 3 XML files, 1 of which is close to 450MB in size. Recently, I received a new sample file (only 14Mb) and now it's failing (well, it's failing in the sense that no data observations are being read by SAS).

Code:

libname incoming xml92 "&xml_file"
xmlmap="&xml_map"
xmlschema="&xml_schema"
xmltype=xmlmap
xmlmeta=schemadata;
proc print data=incoming.x_headerinfo; run;

...where the x_headerinfo is the first node of data in the file.

Log:
NOTE: Processing XMLMap version 1.9.
NOTE: Libref INCOMING was successfully assigned as follows:
Engine: XML92
Physical Name: W:\Data_Management\test.xml
2111 proc print data=incoming.x_headerinfo; run;

NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
NOTE: No observations in data set INCOMING.x_headerinfo.
NOTE: There were 0 observations read from the data set INCOMING.x_headerinfo.

Interestingly, within XML Mapper, I can use the Table View tab to see the data, correctly mapped. But Base SAS is unable to replicate this. Even SAS Explorer is unable to open any 'tables' to view.

I've added an End Path for the table, which is the same as the Path, set as End. And added an automatic enumerator ot the table. No luck on the Base SAS side but I see correct mapping in the Table View of XML Mapper.

I've been researching this problem for the past 2 weeks and have read several really good papers on the subject, but haven't found reference to this problem.

3 Questions:
1) What are the reasons why Base SAS is unable to achieve access by observation number in an XML file? (something to do with Sequential Reading of the file instead of Random reading?)
2) Any references to suggest?
3) Any suggestions for the above problem?

I'm considering having the sender re-create their XML file. the only thing I can find in their file that might be problematic is that the order of nodes is not the same as one of the other test files that does work.

[Cynthia_sas]

The XPATH statements for one file may not work with a new XML file -- especially if the nodes are not in the same order as the original file.

Your best resource for help with using XMLMapper and XML Maps is SAS Technical support.

I believe that access by observation number is just NOT available in the XML file -- I don't know why.

cynthia

[JohnG]

Thanks Cynthia.

I may have misspoke about nodes being out of order. I believe the nodes that I think are out of order are based on the data - some data allows additional child nodes.

The XML file does validate under the given schema though.

It's not practical to create a new Map file per XML data file. I have one schema file and all incoming XML data should be based on the schema. Having one map file to read in all the data is the ideal for this project.

[Cynthia_sas]

Hi:
I don't actually know how the XPATH in the XML Mapper would work if the path was there in the XML file sometimes and sometimes not. I -think- that the non-existent node would cause some column value to be missing. But, that would be a good question for Tech Support.

cynthia

[JohnG]

Solution found.

Apparently, it was the creation of the problem XML file. The process used to create the file include running separate SQL queries using the FOR XML clause and then pasting the results into the file. The file passed XML validation in several of the XML parsers (XML Spy, SAS XML Mapper, etc). But the XML92 engine in Base SAS was unable to read the data.

The file was recreated whole and now Base SAS can read the data from the XML file. No changes were made to the map file nor the SAS program I had developed. Important to note: the XML92 engine does not pass an error message to the log.

As of this morning, I still do not know what prevented the engine from reading the data. But I did receive the SAS Note email this morning which had this reference: http://support.sas.com/kb/41/067.html

********
Problem Note 41067: Using the XML92 engine might generate an error or shut down SAS when the data contains specific characters

Using the XML92 LIBNAME engine might generate errors and shut down SAS if the data contains specific characters. The characters that potentially could cause the problem are characters that require escaping, such as the following: & < > "

The XML92 LIBNAME engine can overwrite memory when it escapes these characters, which causes the problem.

A hot fix is planned for this issue.
*******

This may have been the issue. I had found “&” within a data field, but when I had replaced it with an appropriate fix, the XML file was still not read correctly. Maybe there was an errant “ (double quote) somewhere in the file that I did not find.

Thanks for all assistance…

[ram143]

Hi John,

I am facing same issue while reading an xml file into SAS, using 9.1.3. I am so new to xml and been researching since 2 weeks. I mapped schema document using xml mapper and trying to read xml file with the map I created. But not getting any observations into the dataset, proc contents also giving missing observations. I don't know how to validate the map also. Could you assist me, please.

Ram