POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

[Dmitry]

I'm trying to import xml-file to SAS. Xml-file is a Google "georesponse" on address request. Here's its fragment:

    <address_component>

      <long_name>1025</long_name>

      <short_name>1025</short_name>

      <type>street_number</type>

    </address_component>

    <address_component>

      <long_name>Gilford Street</long_name>

      <short_name>Gilford St</short_name>

      <type>route</type>

     </address_component>

     <address_component>

      <long_name>West End</long_name>

      <short_name>West End</short_name>

      <type>neighborhood</type>

      <type>political</type>

    </address_component>

    <address_component>

      <long_name>Vancouver</long_name>

      <short_name>Vancouver</short_name>

      <type>locality</type>

      <type>political</type>

    </address_component>

I've attached the full xml-file.

I want to convert it into SAS-dataset like this:

type	long_name
street_number	1025
route	Gilford St
neighborhood	West End

etc

As you can see, some <address_component>-elements have inside only one <type>-element (like street_number or route), but others have two: first one with value of interest (e.g. 'neighborhood') and the second - with value 'political', which I don't need.

So I created XML-map in XML Mapper, using function POSITION()=1, to ensure that only first occurence of <type>-tag used:

<?xml version="1.0" encoding="windows-1252"?>

<!-- ############################################################ -->

<!-- 2014-01-03T17:07:57 -->

<!-- SAS XML Libname Engine Map -->

<!-- Generated by XML Mapper, 903000.0.0.20110518190000_v930 -->

<!-- ############################################################ -->

<!-- ###  Validation report                                   ### -->

<!-- ############################################################ -->

<!-- XMLMap validation completed successfully. -->

<!-- ############################################################ -->

<SXLEMAP description="Google Georesponse" name="GeocodeResponse" version="2.1">

    <NAMESPACES count="0"/>

    <!-- ############################################################ -->

    <TABLE name="GeoResponse">

        <TABLE-PATH syntax="XPath">/GeocodeResponse/result/address_component</TABLE-PATH>

        <COLUMN name="type">

            <PATH syntax="XPath">/GeocodeResponse/result/address_component/type[position()=1]</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>27</LENGTH>

        </COLUMN>

        <COLUMN name="long_name">

            <PATH syntax="XPath">/GeocodeResponse/result/address_component/long_name</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>17</LENGTH>

        </COLUMN>

    </TABLE>

</SXLEMAP>

And it works properly in XML Mapper itself (in the tab Table View  you can see the expected values). But when I run the code using this map in SAS EG 4.3 or SAS Base 9.3, column 'type' is empty.

If I don't use POSITION()=1 in the map, then everything works well (but for all items except strett_number, route and postal_code I've got 'political' as type, not 'city', 'country' etc).

I use this code for checking:

filename  gilfordl 'R:\gilfordl.xml';

filename  SXLEMAP 'R:\georesponsemap.map';

libname   gilfordl xmlv2 xmlmap=SXLEMAP access=READONLY;

DATA GeoResponse; SET gilfordl.GeoResponse; run;

When tried the same with XML Mapper 9.2 (and verison of SXLEMAP 1.2 ) and SAS 9.2 everything works perfectly. But not with SAS 9.3. For the latter I tried to change version of SXLEMAP to 1.2 in the map-file and to use XML engine instead of XMLV2, but still no luck - type is empty. But as soon as I delete position()=1, everything works.

Does anybody have any clue where can a problem be? Some SAS 9.3 specific?

[BrunoMueller · Accepted solution]

Hi Dmitry

As a workaround you can use the XML engine as you and a map file with version 1.2, this work for me with SAS9.4

[Dmitry]

Hi Bruno,

that's the case: I tried to do it  - run LIBNAME with XML and XML-map version=1.2 on SAS 9.3, but it didn't work.

Well, it definitely works, not sure why I decided before that it didn't

[BrunoMueller]

Dmitry

I recommend to contact tech support about this

Bruno

[BillM_SAS]

I checked and there is an existing defect filed for this issue with the XMLV2 LIBNAME engine. I am adding your posting to the defect information.

I put together a short SAS program that allows you to do what you want with the XMLV2 LIBNAME engine in the latest version of SAS V9.3 (maintenance 2). In the attached SAS program, I create a XML Mapper file within the SAS session using the AUTOMAP option (introduced in SAS 9.3 maintenance 2). Since this is a default XML Mapper file, it does not use the POSITION function. The results from the SAS program appear to match your desired output:

                     Obs    type                           long_name

                      1     street_number                  1025

                      2     route                          Gilford Street

                      3     neighborhood                   West End

                      4     locality                       Vancouver

                      5     administrative_area_level_2    Greater Vancouver

                      6     administrative_area_level_1    British Columbia

                      7     country                        Canada

                      8     postal_code                    V6G 1R2

I did not spend a lot of time on the SAS program, so I suspect that there are better and/or more efficient ways in the SQL procedure code I wrote to get the desired results. I'm not a SQL procedure expert. I just wanted a proof-of-concept to show how to get around the problem you note with the POSITION function and the XMLV2 LIBNAME engine.

[Dmitry]

Thanks, BillM@SAS.

I'm sure it works, but I can't use it, I'm receiving error message about wrong option in LIBNAME statement. My SAS version is  9.3 (TS1M0), probably that's why, since you said that AUTOMAP was introduced in SAS 9.3 maintenance 2.

[BillM_SAS]

The AUTOMAP option just saved me the step of running the XML Mapper application, creating a default XML Mapper file, and saving the XML Mapper file. Since you are not running SAS v9.3 maintenance 2, just create the XML Mapper file manually with the "Automatically generate and XMLMap from the XML structure" button on the toolbar in the XML Mapper application (see attached image). Once the XML Mapper file is created and saved and the LIBNAME statement adjusted to use your created XML Mapper file, the rest of the code should run fine.