Tip: Adventures with DI Studio 4.3 - XML and Nicknames

I came across an interesting situation recently with SAS 9.3 and DI Studio 4.3.  I needed to create a DI Studio job that would access the XML output from a (non-SAS) web service. I've done this previously, with the steps I usually follow as:

• » Manually obtain the XML output from the web service
• » Save this as a local XML file
• » Use SAS XML Mapper to create an XML Map based on the local file
• » Create an XML Library in DI Studio, registering and using the XML Map from XML Mapper
• » Register the XML source in DI Studio using the Register Tables wizard
• » Develop my process flow

Unfortunately DI Studio is not (yet) flexible enough around how the XML file will be accessed and will only let you read a local XML file. For this reason I would then usually hand code the LIBNAME and/or FILENAME statements for when I actually access the XML data in a job, which would be a good topic for another post...

I went to go through this process in DI Studio 4.3, and found that when I tried to register a table against my XML library it failed because it claimed the XML Map was invalid!  How could that be? I only just created it with the SAS 9.3 tool (XML Mapper) specifically designed for the job!

First I created an XML library object pointing to my local XML file:

Next I go into the Advanced Options so I can register and use my new XML Map:

Once I've defined the library I like to check that it's generating the LIBNAME/FILENAME statements that I expect:

All looking good so far. Now let's register the XML data table:

What the?!?!?!

Ok, show me the log:

Hmmm… I only just created the map with XML Mapper, but I'll play and see if I can spot anything out of the ordinary...

Nope, looks like a well formed map file and searching the SAS Knowledge Base for this error isn't really giving me any hints.

I then went reading through the 9.3 XML Engine Libname User Guide, which mentioned that the XML nickname calls the SAS 9.1.3 XML engine while the XMLV2 nickname calls the "post 9.1.3" XML engine. Is the XML Map I just generated not compatible with the standard "XML" engine? If you haven't heard of nicknames before, these are what we use as the "Engine" parameter when writing a LIBNAME or FILENAME statement, and tell SAS which module to use.  You can see which Nicknames are available in your SAS Environment by executing the following code:

PROC NICKNAME;RUN;

And this is what I did in DI Studio. I opened a blank code editor and ran the following code:

I then went looking for the XML nickname:

And you can see from the listing that the XML nickname uses the SASEXML module while the XML92 and XMLV2 nicknames use the new SAS92XML module. Ok, now all I need to do is change the Engine (nickname) to either XML92 or XMLV2 on the generated LIBNAME statement and (hopefully) my new XML Map should work. But… if we look back at the library definition, you will see that the Engine parameter can;t be changed from XML!

Well, I do like a challenge...  How about I change the XML nickname so that it also uses the new module? Since the site I am at has never used the XML engine before there is little risk that they have some 9.1.3 code/maps that will break - and like they say on TV, "don't try this at home" - unless you know what you're doing :)  Here is the code I need to run to update the XML nickname:

If I then do a check by running PROC NICKNAME again, I see that it's been updated:

Now to try registering the table again in DI Studio… and it now works!

If I find out later that something did break, I can always change the nickname back by running the following code:

Well that was an interesting detour… now what was I trying to do again with this XML data? :)

This article was originally posted 2May2012 at http://www.scorpiosoftware.com.au/2012/05/adventures-with-di-studio-4-3-xml-and-nicknames/