SAS xml mapper XPATH problem

[mtajouri]

Hello,

I would like to import a xml file. Therefore I use the SAS XML- Mapper to create a map.

I was able to retrieve data with direct Xpath. Like : “/records/record/Data/code” in the example below.

But I have a problem with complexe path (XPATH containing queries)  : “/records/record/Data/action[type="TYPE_A"]/coefficient”

----------------------------------xml file  data.xml----------------------------------

<?xml version="1.0" encoding="utf-8"?>
<records>
<record>
<Data>
<code>AAA00001</code>
<action>
<type>TYPE_A</type>
<coefficient>0.75</coefficient>
</action>
<action>
<type>TYPE_B</type>
<coefficient>1</coefficient>
</action>

<coverage>
<isSelected>true</isSelected>
<type>OPTIONAL</type>
<deductible>
<value>1.5</value>
</deductible>
<label>Label_1</label>
<identifier>ID_001</identifier>
</coverage>
</Data>
</record>
</records>

------------------------------------------------------------------------------

----------------------------------map file  data.map----------------------------------

<?xml version="1.0" encoding="windows-1252"?>
<SXLEMAP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="nameTest" version="1.2" xsi:noNamespaceSchemaLocation="http://www.sas.com/xml/schema/sxle12.xsd">
<TABLE name="DataTable">
<TABLE-PATH syntax="XPath">/records/record</TABLE-PATH>

<COLUMN name="code">
<PATH syntax="XPath">/records/record/Data/code</PATH>
<LABEL>code</LABEL>
<TYPE>character</TYPE>
<DATATYPE>STRING</DATATYPE>
<LENGTH>16</LENGTH>
</COLUMN>

<COLUMN name="TYPE_A">
<PATH syntax="XPath">/records/record/Data/action[type="TYPE_A"]/coefficient</PATH>
<LABEL>Type A</LABEL>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>

<COLUMN name="TYPE_B">
<PATH syntax="XPath">/records/record/Data/action[type="TYPE_B"]/coefficient</PATH>
<LABEL>Type B</LABEL>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>

<COLUMN name="OPTION">
<PATH syntax="XPath">/records/record/Data/coverage[isSelected="true"][type="OPTIONAL"]/label</PATH>
<LABEL>OPTIONAL label</LABEL>
<TYPE>character</TYPE>
<DATATYPE>STRING</DATATYPE>
<LENGTH>250</LENGTH>
</COLUMN>

<COLUMN name="deductible">
<PATH syntax="XPath">/records/record/Data/coverage[(isSelected="true") and (identifier="ID_001" or identifier="ID_002")]/deductible/value</PATH>
<LABEL>deductible value</LABEL>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>

</TABLE>

</SXLEMAP>

------------------------------------------------------------------------------

I want to convert it into SAS table like this:

Code                    TYPE_A                TYPE_B                OPTION               deductible

AAA00001           0.75                      1                            Label_1               1.5

Is there any possiblity to get this done either directly in the XML-Mapper or as an workaround?

Thanks in advance!

[SASJedi]

I can't figure this our with the XML mapper, but I can code for it:

libname x xmlv2 "c:/temp/mtajouri.xml" automap=REPLACE xmlmap="c:/temp/mtajouri.map";

proc sql;
create table temp1 as 
   select distinct 
          a.data_ordinal
         ,code 
         ,a.type
         ,coefficient as Type_Value
      from x.Action a
          ,x.Coverage c
          ,x.Data 
      where a.Data_ordinal=c.Data_ordinal
        and a.Data_ordinal=data.Data_ordinal
      order by Code, a.data_ordinal
;
create table t2 as 
   select code 
         ,label as Option
      from x.Coverage c
          ,x.Data 
      where c.Data_ordinal=data.Data_ordinal
      order by Code, c.data_ordinal
;
create table t3 as 
   select code 
         ,value as Deductible
      from x.coverage c
          ,x.Data 
          ,x.Deductible d
      where data.Data_ordinal=c.Data_ordinal
        and c.Coverage_ordinal=d.coverage_ordinal
      order by Code
;
quit;
libname x clear;

proc transpose data=temp1 out=t1(drop=_:) ;
  by code;
  id type;
  var Type_Value;
run;

proc sql;
create table want as
  select t1.Code
        ,TYPE_A
        ,TYPE_B
        ,OPTION
        ,Deductible
    from t1 inner join t2
       on t1.code=t2.code
       inner join t3 
       on t1.code =t3.code
   order by t1.code
;
drop table temp1;
drop table t1;
drop table t2;
drop table t3;
quit;