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.  ... View more

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?  ... View more

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?    ... View more

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; ... View more

Ok, I just tested this on Mac by using the sftp command line and I can log in the remote server with the same credential with no problem. And when I logged in, I type ls command and it listed all the folders and files there. ... View more

I don't see any of those notes. I did validate the username and password using WinSCP client.  ... View more