topic Re: Load sharepoint URL with XML into SAS using Base SAS in SAS Programming

Load sharepoint URL with XML into SAS using Base SAS

Cornelis — Thu, 01 Oct 2015 14:43:15 GMT

Dear all,

I have received URL from Sharepoint that looks like this:

http://intranet.croda.com/help/teamroom/_vti_bin/owssvr.dll?XMLDATA=1&List={036B8C80-CB89-41BE-8F65-4C3D530D4B6B}&View={078AE2C2-E991-4A85-8347-966728F67960}&RowLimit=0&RootFolder=%2fhelp%2fteamroom%2fLists%2fCockTestData
Selection={036B8C80-CB89-41BE-8F65-4C3D530D4B6B}-{078AE2C2-E991-4A85-8347-966728F67960}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://intranet.croda.com/help/teamroom/_vti_bin
SharePointListView={078AE2C2-E991-4A85-8347-966728F67960}
SharePointListName={036B8C80-CB89-41BE-8F65-4C3D530D4B6B}
RootFolder=/help/teamroom/Lists/CockTestData

That is, indeed, a very long URL.....

When I copy this URL in the webbrowser and subsequently press on Enter, then I see an XML output on the screen:

<s:Schema id="RowsetSchema">

<s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">

....

Experienced user will directly recognize the XML form.

The XML loaded into excel will look like as demonstrated in the attachment.

The question:

how can I import this URL into SAS and to get a SAS table that shows only the columns and rows as that from excel? (ignore auto filter, sort option in the header).

Your comment will be greatly appreciated.

Best regards,

Cornelis Verboom

Re: Load sharepoint URL with XML into SAS using Base SAS

hbi — Mon, 05 Oct 2015 19:39:37 GMT

Are you trying to parse the URL to extract specific key-value pairs? I looked at the URL string that you provided, and the parameters Title, TestColumn1, TestColumn2, Category, Item Type and Path are not among the parameters in the URL.

Or perhaps you are trying to have SAS act as the "user agent" that sends HTTP "get" requests back and forth to the Sharepoint site, in which case, your objective is to scrape web page output? This might be technically challenging if there are permissions on your Sharepoint site and/or if SAS is running on a non-Windows box. One of the reasons for this is that SAS would be an "agent" that is acting on one's behalf, and the default behavior is that SAS does not run under the same context as you. (I think my comment may apply if you are using SAS EG; I am not sure if it applies to PC SAS as I have not used the latter in a number of years).

-- hbi

Re: Load sharepoint URL with XML into SAS using Base SAS

Cornelis — Wed, 07 Oct 2015 05:41:27 GMT

Hi hbi,

Correct, I'm using SAS as an "user agent". Your answers/comments give the impression that the problem cannot be easily solved.

a SAS technical support team is investigating this issue and if they have found the solution or the Sharepoint team is coming with an appropriate alternative, then I will let this site know.

Best regards,

Cornelis