The SAS Output Delivery System and reporting techniques

Exporting an XML Document with Separate Metadata

Reply
Occasional Contributor
Posts: 5

Exporting an XML Document with Separate Metadata

My ride with XML has been rather bumpy till now and it seems I even need to simply reproduce a sample program from the documentation (the item in the title).

I'm running a local installation of SAS 9.2 TS Level 1M0 on a Vista Enterprise workstation with SP1.

Before running the sample program I've copied the suppliers table from the Northwind 2007.accdb sample data base for MS Access to my WORK library.

The sample code with my choice of directories is as follows:

filename xsd 'C:\Users\Raateland\Documents\SAS\XML Testing\suppliers.xsd';

libname output xml 'C:\Users\Raateland\Documents\SAS\XML Testing\suppliers.xml'
xmltype=msaccess xmlmeta=schemadata xmlschema=xsd;

data output.suppliers;
set suppliers;
run;

This runs OK and produces the files suppliers.xsd and suppliers.xml.
The content of suppliers.xsd is just like the xsd-file reproduced in the on-line documentation except for the data, probably because the supplier data in the documentation was taken from an earlier version of MS Access.
Suppliers.xml has one what looks like an important difference compared to the file reproduced in the documentation:



xmlnsSmiley Surprisedd="urn:schemas-microsoft-comSmiley Surprisedfficedata"
xsi:noNamespaceSchemaLocation="suppliers.xsd">

1
Supplier A
....

Line 4 starts with xsi: whereas in the documentation it starts with xs:. Both have xs: in the text on line 2.

When I try to open the XML file (in IE) an error message is produced saying the XML page cannot be displayed. At the end of the error screen text is the line with noNamespaceSchemaLocation. When on a hunch I edit line 4 and remove the i from xsi: making it read xs:, the xml file will be opened by IE without problems.

Unless I edit out the i from xsi: on line 4, XML Mapper will not open the XML file without error either: XML parser encountered XML fatal error (it also mentions the prefix 'xsi' as the cause).

When I try to import the unedited XML file into MS Access 2007, it complains about the same thing: an undefined prefix.

The edited xml file OTOH can be imported into Access 2007 without problems and XML mapper doesn't complain about it either.

I ran into this problem after I had first been trying to work the example given by Richard Foley on page 4 of his paper 'Power Up SAS with XML' without success. The Foley example is also somewhere in the on-line documentation, I think. I can't remember the details of the problems with that example, though.

The XML Mapper I've installed is the latest from the SI site.

The end purpose of my XML tests is finding out how to produce XML files with content from appropriate SAS data sets when we're just given their schema files. I don't know yet how complicated these schemas will be. For now it feels like quite a challenge.

Thanks for your kind help and information.
Arjen Raateland
Finnish Environment Institute, Helsinki
SAS Super FREQ
Posts: 8,743

Re: Exporting an XML Document with Separate Metadata

Hi:
You might consider working with Tech Support on this question.The SAS XML LIBNAME engine did introduce some new features with 9.2.

I could not test your code because the machine where I have MS Access does not have 9.2 and the machine with 9.2 does not have Access.

Tech Support will be able to trace down the documentation example and help you figure out whether the XML that you're generating is valid and correct for the version of Northwind suppliers that you're using compared to whatever file they used for the example in the doc.

cynthia
Occasional Contributor
Posts: 5

Re: Exporting an XML Document with Separate Metadata

Thanks!

I've described the problem to Tech Support yesterday and they're working on it.

My next/real problem is how to produce XML files that adhere to schema's that we're being given by the party that we need to report data to (e.g. EU Environment Agency). It seems much trickier to do than the example regarding I haven't found any nice examples about it.
First we're given an XML schema plus documentation, we then find the appropriate source data to use, retrieve and transform the data and then output it to an XML file that follows the schema definition ... easier said than done.
I'm not even sure yet if it can be done in SAS ... Message was edited by: ArjenR
SAS Super FREQ
Posts: 8,743

Re: Exporting an XML Document with Separate Metadata

Hi:
I -have- done a custom XML tagset that conforms to a schema. But I did it by READING the schema and building the tagset events manually.

In other words, every REGION, should get the <REG> tag and every subsidiary gets the <SUB> tag:
[pre]
<Asia>
<SUB>Tokyo</SUB>
</Asia>
<Asia>
<SUB>Kyoto</SUB>
</Asia>
<Canada>
<SUB>Montreal</SUB>
</Canada>
[/pre]

I believe they're working on the ability to use an XMLMAP for both INPUT (available in SAS 8, SAS 9.1.3, SAS 9.2) and OUTPUT (I thought this was available for SAS 9.2). What I don't know is whether the XMLMAP application will read the schema for you and derive the correct tags. You may still need an XMLMAP for this. That would certainly make the job easier.

But as I said, I have successfully written a custom tagset template to produce XML without having an XMLMAP, but just by reading the Schema (and having a sample of what the VALID final file should look like). Of course, reading the Schema is not as helpful as having the sample VALID output. Then I mapped (manually) the variables to the XML. Then I wrote my own custom tagset template with custom events.

The type of tagset template I wrote depended on whether I was going to use the SAS XML LIBNAME engine (fewer events to contend with) versus ODS (more events to contend with).

However, it -is- possible. For some custom XML examples created for use with the SAS XML Libname engine, see this paper:
http://www.lexjansen.com/pharmasug/2006/technicaltechniques/tt24.pdf

I can send you a zip file of the programs used in this paper (the zip file may be out on the lexjansen.com site -- so check there first). But if you can't find the zip file, you can email me at the email address in the paper and I will send it to you.

cynthia
Ask a Question
Discussion stats
  • 3 replies
  • 203 views
  • 0 likes
  • 2 in conversation