How to read in an XML file with namespaces

[kingcu]

I am trying to read in an XML file generated by the SAS Excelxp tagset, basically it's an excel file but in xml format with a few namespaces:

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="http://www.w3.org/TR/REC-html40">

<Worksheet ss:Name="Sheet1">...</Worksheet>
<Worksheet ss:Name="Sheet2">...</Worksheet>
<Worksheet ss:Name="Sheet3">...</Worksheet>

</Workbook>

I only want to import data from Sheet2, following the example on the SAS docs, I created the below xmlmap:

<?xml version="1.0" ?>
<SXLEMAP version="2.1">
    <NAMESPACES count="1">
        <NS id="1" prefix="ss">"urn:schemas-microsoft-com:office:spreadsheet"</NS>
    </NAMESPACES>

    <TABLE name="sheet2">
        <TABLE-PATH syntax="XPath">
            /Workbook/Worksheet[@{1}Name="Sheet2"]/Table/Row
        </TABLE-PATH>
        ...
    </TABLE>
</SXLEMAP>

but unfortunately, it didn't work, error log says xpath is invalid. 

Can someone please advise how to filter on an attribute value with namespace? 

[ChrisNZ]

1. Do you miss a space?

/Workbook/Worksheet [@{1}Name="Sheet2"]/Table/Row

2. Do you need to use the namespace? Can't you point to something like

/Workbook/Worksheet ss:Name="Sheet2"/Table/Row

3. Have you tried asking SAS to generate a map and use that as your starting point?

[kingcu]

Tried 1 and 2, neither of them fixed issue. By 3, do you mean the SAS mapper tool? Unfortunately, I can't install it on my work laptop, and honestly, I doubt it can solve problem like this. 

BTW, below is a few line of code that can generate an XML spreadsheet to try things out:

ods Tagsets.ExcelXP file='/***path to save xml file***/test.xml';

title 'Listing of Class data';

proc print data=sashelp.class;
run;

ods Tagsets.ExcelXP close;

[Tom]

That seems easy enough to read.

filename xml temp;
ods Tagsets.ExcelXP file=xml ;
title 'Listing of Class data';
proc print data=sashelp.class(obs=3);
run;
title;
ods Tagsets.ExcelXP close;

filename xmlmap temp;
libname xml xmlv2 xmlmap=xmlmap automap=reuse;
proc sql;
  create table cell_values as
  select * from xml.data natural join xml.cell natural join xml.row
 ;
quit;
proc print;
  var table_ordinal row_ordinal cell_index data_type data ;
run;

Result

        Table_      Row_     Cell_    Data_
Obs    ORDINAL    ORDINAL    Index     Type     Data

  1       1          1         1      String    Obs
  2       1          1         2      String    Name
  3       1          1         3      String    Sex
  4       1          1         4      String    Age
  5       1          1         5      String    Height
  6       1          1         6      String    Weight
  7       1          2         1      Number    1
  8       1          2         2      String    Alfred
  9       1          2         3      String    M
 10       1          2         4      Number    14
 11       1          2         5      Number    69.0
 12       1          2         6      Number    112.5
 13       1          3         1      Number    2
 14       1          3         2      String    Alice
 15       1          3         3      String    F
 16       1          3         4      Number    13
 17       1          3         5      Number    56.5
 18       1          3         6      Number    84.0
 19       1          4         1      Number    3
 20       1          4         2      String    Barbara
 21       1          4         3      String    F
 22       1          4         4      Number    13
 23       1          4         5      Number    65.3
 24       1          4         6      Number    98.0

[kingcu]

OK, just did some further testing, and it turned out the issue may not be with the namespace, it's with the attribute filtering, i.e., selecting elements based on certain attribute value. So, what I did is just stripping out all the namespace stuff generated by Excelxp tagset, only keep the bare-bones of the xml, like this:

<?xml version="1.0" encoding="utf-8"?>
<Workbook>
<Worksheet>
<Table>
<Row>
<Cell Index="1"><Data>Obs</Data></Cell>
<Cell Index="2"><Data>Name</Data></Cell>
<Cell Index="3"><Data>Sex</Data></Cell>
<Cell Index="4"><Data>Age</Data></Cell>
<Cell Index="5"><Data>Height</Data></Cell>
<Cell Index="6"><Data>Weight</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>1</Data></Cell>
<Cell Index="2"><Data>Alfred</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>69.0</Data></Cell>
<Cell Index="6"><Data>112.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>2</Data></Cell>
<Cell Index="2"><Data>Alice</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>13</Data></Cell>
<Cell Index="5"><Data>56.5</Data></Cell>
<Cell Index="6"><Data>84.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>3</Data></Cell>
<Cell Index="2"><Data>Barbara</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>13</Data></Cell>
<Cell Index="5"><Data>65.3</Data></Cell>
<Cell Index="6"><Data>98.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>4</Data></Cell>
<Cell Index="2"><Data>Carol</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>62.8</Data></Cell>
<Cell Index="6"><Data>102.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>5</Data></Cell>
<Cell Index="2"><Data>Henry</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>63.5</Data></Cell>
<Cell Index="6"><Data>102.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>6</Data></Cell>
<Cell Index="2"><Data>James</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>57.3</Data></Cell>
<Cell Index="6"><Data>83.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>7</Data></Cell>
<Cell Index="2"><Data>Jane</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>59.8</Data></Cell>
<Cell Index="6"><Data>84.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>8</Data></Cell>
<Cell Index="2"><Data>Janet</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>62.5</Data></Cell>
<Cell Index="6"><Data>112.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>9</Data></Cell>
<Cell Index="2"><Data>Jeffrey</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>13</Data></Cell>
<Cell Index="5"><Data>62.5</Data></Cell>
<Cell Index="6"><Data>84.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>10</Data></Cell>
<Cell Index="2"><Data>John</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>59.0</Data></Cell>
<Cell Index="6"><Data>99.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>11</Data></Cell>
<Cell Index="2"><Data>Joyce</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>11</Data></Cell>
<Cell Index="5"><Data>51.3</Data></Cell>
<Cell Index="6"><Data>50.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>12</Data></Cell>
<Cell Index="2"><Data>Judy</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>64.3</Data></Cell>
<Cell Index="6"><Data>90.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>13</Data></Cell>
<Cell Index="2"><Data>Louise</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>56.3</Data></Cell>
<Cell Index="6"><Data>77.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>14</Data></Cell>
<Cell Index="2"><Data>Mary</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>66.5</Data></Cell>
<Cell Index="6"><Data>112.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>15</Data></Cell>
<Cell Index="2"><Data>Philip</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>16</Data></Cell>
<Cell Index="5"><Data>72.0</Data></Cell>
<Cell Index="6"><Data>150.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>16</Data></Cell>
<Cell Index="2"><Data>Robert</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>64.8</Data></Cell>
<Cell Index="6"><Data>128.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>17</Data></Cell>
<Cell Index="2"><Data>Ronald</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>67.0</Data></Cell>
<Cell Index="6"><Data>133.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>18</Data></Cell>
<Cell Index="2"><Data>Thomas</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>11</Data></Cell>
<Cell Index="5"><Data>57.5</Data></Cell>
<Cell Index="6"><Data>85.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>19</Data></Cell>
<Cell Index="2"><Data>William</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>66.5</Data></Cell>
<Cell Index="6"><Data>112.0</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

And defining the below map to read it:

<?xml version="1.0" ?>
<SXLEMAP version="2.1">
    <TABLE name="class">
        <TABLE-PATH syntax="XPath">
            /Workbook/Worksheet/Table/Row
        </TABLE-PATH>
        
        <COLUMN name="var1">
            <PATH>/Workbook/Worksheet/Table/Row/Cell[@Index="2"]/Data</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>STRING</DATATYPE>
            <LENGTH>30</LENGTH>
        </COLUMN>
    </TABLE>
</SXLEMAP>

where Cell[@Index="2"] is the standard way in Xpath to select an element based on its attribute value, but I got an error saying that:

ERROR: Xpath construct /Workbook/Worksheet/Table/Row/Cell[@Index="2"]/Data for column var1 is an invalid, unrecognized, or 
 unsupported form.
 
 ERROR: Error in the LIBNAME statement.

It looks to me that SAS XML map may not support this attribute filtering. Can someone please confirm and possibly advise on a solution? 

[ChrisNZ]

Not all Xpath syntaxes are supported. The documentation states:

CAUTION

These forms are the only XPath forms that the XML engine supports.

If you use any other valid W3C form, the results will be unpredictable.

Unsure what's wrong with your code as I don't have time to check, but this works:

data _null_;
  file '~/kk.xml';
  put '<team>';
  put '<name type="first">';
  put 'Albert';
  put '</name>';
  put '<name type="last">';
  put 'Einstein';
  put '</name>';
  put '</team>';
  
  file '~/kkmap.xml';
  put '<?xml version="1.0" ?>';
  put '<SXLEMAP version="2.1">';
  put '<TABLE name="TEAM">';
  put '<TABLE-PATH syntax="XPath">';
  put '/team';
  put '</TABLE-PATH>';
  put '<COLUMN name="VAR1">';
  put '<PATH>/team/name[@type="first"]</PATH>';  * <=== namespace ;
  put '<TYPE>character</TYPE>';
  put '<DATATYPE>STRING</DATATYPE>';
  put '<LENGTH>30</LENGTH>';
  put '</COLUMN>';
  put '</TABLE>';
  put '</SXLEMAP>';
run;
filename XML "~/kk.xml";
filename MAP "~/kkmap.xml";
libname XML xmlv2 xmlmap=MAP;
proc print data=XML.TEAM;
run;

Obs	VAR1
1	Albert

Expand from this working code to yours and see where things break.

[kingcu]

I don't think there is anything wrong with my code, just did some more testing and also found a post on stack overflow talking about the exact same issue, so here is what I found out: the Xpath attribute selector only works on the "end" node in the path. For example, this works as Cell is the "end" or deepest node in the path:

*** XML ***;
<Workbook><Worksheet><Table><Row>
    <Cell Index="2">John Smith</Cell>
</Row></Table></Worksheet></Workbook>

*** XML map ***;
...
<PATH syntax="XPath">/Workbook/Worksheet/Table/Row/Cell[@Index="2"]</PATH>
...

but if "John Smith" is contained in another level of node further down, it doesn't work:

*** XML ***;
<Workbook><Worksheet><Table><Row>
    <Cell Index="2"><Data>John Smith</Data></Cell>
</Row></Table></Worksheet></Workbook>

*** XML map ***;
...
<PATH syntax="XPath">/Workbook/Worksheet/Table/Row/Cell[@Index="2"]/Data</PATH>
...

It looks to me the Xpath attribute selector in SAS XML Map is NOT properly implemented. Is there any workaround? If not, how can I raise a bug report or enhancement request? 

[Tom · Accepted solution]

I don't have any trouble getting XMLV2 to read your example file.

Spoiler

filename xml temp;
options parmcards=xml;
parmcards4;
<?xml version="1.0" encoding="utf-8"?>
<Workbook>
<Worksheet>
<Table>
<Row>
<Cell Index="1"><Data>Obs</Data></Cell>
<Cell Index="2"><Data>Name</Data></Cell>
<Cell Index="3"><Data>Sex</Data></Cell>
<Cell Index="4"><Data>Age</Data></Cell>
<Cell Index="5"><Data>Height</Data></Cell>
<Cell Index="6"><Data>Weight</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>1</Data></Cell>
<Cell Index="2"><Data>Alfred</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>69.0</Data></Cell>
<Cell Index="6"><Data>112.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>2</Data></Cell>
<Cell Index="2"><Data>Alice</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>13</Data></Cell>
<Cell Index="5"><Data>56.5</Data></Cell>
<Cell Index="6"><Data>84.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>3</Data></Cell>
<Cell Index="2"><Data>Barbara</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>13</Data></Cell>
<Cell Index="5"><Data>65.3</Data></Cell>
<Cell Index="6"><Data>98.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>4</Data></Cell>
<Cell Index="2"><Data>Carol</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>62.8</Data></Cell>
<Cell Index="6"><Data>102.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>5</Data></Cell>
<Cell Index="2"><Data>Henry</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>63.5</Data></Cell>
<Cell Index="6"><Data>102.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>6</Data></Cell>
<Cell Index="2"><Data>James</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>57.3</Data></Cell>
<Cell Index="6"><Data>83.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>7</Data></Cell>
<Cell Index="2"><Data>Jane</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>59.8</Data></Cell>
<Cell Index="6"><Data>84.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>8</Data></Cell>
<Cell Index="2"><Data>Janet</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>62.5</Data></Cell>
<Cell Index="6"><Data>112.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>9</Data></Cell>
<Cell Index="2"><Data>Jeffrey</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>13</Data></Cell>
<Cell Index="5"><Data>62.5</Data></Cell>
<Cell Index="6"><Data>84.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>10</Data></Cell>
<Cell Index="2"><Data>John</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>59.0</Data></Cell>
<Cell Index="6"><Data>99.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>11</Data></Cell>
<Cell Index="2"><Data>Joyce</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>11</Data></Cell>
<Cell Index="5"><Data>51.3</Data></Cell>
<Cell Index="6"><Data>50.5</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>12</Data></Cell>
<Cell Index="2"><Data>Judy</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>14</Data></Cell>
<Cell Index="5"><Data>64.3</Data></Cell>
<Cell Index="6"><Data>90.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>13</Data></Cell>
<Cell Index="2"><Data>Louise</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>56.3</Data></Cell>
<Cell Index="6"><Data>77.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>14</Data></Cell>
<Cell Index="2"><Data>Mary</Data></Cell>
<Cell Index="3"><Data>F</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>66.5</Data></Cell>
<Cell Index="6"><Data>112.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>15</Data></Cell>
<Cell Index="2"><Data>Philip</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>16</Data></Cell>
<Cell Index="5"><Data>72.0</Data></Cell>
<Cell Index="6"><Data>150.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>16</Data></Cell>
<Cell Index="2"><Data>Robert</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>12</Data></Cell>
<Cell Index="5"><Data>64.8</Data></Cell>
<Cell Index="6"><Data>128.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>17</Data></Cell>
<Cell Index="2"><Data>Ronald</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>67.0</Data></Cell>
<Cell Index="6"><Data>133.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>18</Data></Cell>
<Cell Index="2"><Data>Thomas</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>11</Data></Cell>
<Cell Index="5"><Data>57.5</Data></Cell>
<Cell Index="6"><Data>85.0</Data></Cell>
</Row>
<Row>
<Cell Index="1"><Data>19</Data></Cell>
<Cell Index="2"><Data>William</Data></Cell>
<Cell Index="3"><Data>M</Data></Cell>
<Cell Index="4"><Data>15</Data></Cell>
<Cell Index="5"><Data>66.5</Data></Cell>
<Cell Index="6"><Data>112.0</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
;;;;

filename xmlmap temp;
libname xml xmlv2 xmlmap=xmlmap automap=reuse;

proc print data=xml.cell;
run;

Result

[kingcu]

Thanks for the workaround. The automap does the trick, although I have to join/filter a few datasets to get the portion of the xml I need, it does work around the issue.