BookmarkSubscribeRSS Feed
mtajouri
Calcite | Level 5

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!

1 REPLY 1
SASJedi
Ammonite | Level 13

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;
Check out my Jedi SAS Tricks for SAS Users

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1394 views
  • 0 likes
  • 2 in conversation