DATA Step, Macro, Functions and more

Load sharepoint URL with XML into SAS using Base SAS

Reply
Occasional Contributor
Posts: 8

Load sharepoint URL with XML into SAS using Base SAS

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-...
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:

 

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<sSmiley Frustratedchema 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

 


Capture.JPG
Contributor hbi
Contributor
Posts: 66

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

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

 

Occasional Contributor
Posts: 8

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 198 views
  • 0 likes
  • 2 in conversation