--
In Part-I, I had covered how data\documents can be inserted\uploaded into Sharpoint. In this post, I will demonstrate how to extract data. Please read the previous post, if you have not read it.
The extraction can be done in two ways.
1) Use the code template mapping to invoke the webservice and store the XML in to a table. Then use a conventional mapping to extract the data using SQL.
2) Use the code template mapping to invoke the webservice to hold the data into C$ table and then parse it using a table function or leveraging XDB in the Default Oracle Code Template.
This blog post will demonstrate using the first approach.
I will be extracting the data that was inserted into sharepoint list "Employee" from the previous post.
Extracting data from sharepoint using GetListItems -
Selecting soap action "GetListItems" under "lists.asmx" webservice provides details which can be used to extract listitems from sharepoint.
CAML Format to extract the listitems -
Target is to extract all the listitems. The default row limit of SharePoint is 100 rows which can be overrided as shown below.
Note: If you want to extract data incrementally, all the list have inbuild column "Modified" which can be used as the filter in the CAML query.
Details of how CAML for GetListItems can be found here on msdn. CAML can be constructed to select the columns of your choice,order them , filter them , etc.
Data required to invoke the webservice to get listitems.
Logical View of the Mapping to extract sharepoint listitems -
Execution View of the Mapping to get listitems -
In Part-I, I had covered how data\documents can be inserted\uploaded into Sharpoint. In this post, I will demonstrate how to extract data. Please read the previous post, if you have not read it.
The extraction can be done in two ways.
1) Use the code template mapping to invoke the webservice and store the XML in to a table. Then use a conventional mapping to extract the data using SQL.
2) Use the code template mapping to invoke the webservice to hold the data into C$ table and then parse it using a table function or leveraging XDB in the Default Oracle Code Template.
This blog post will demonstrate using the first approach.
I will be extracting the data that was inserted into sharepoint list "Employee" from the previous post.
Extracting data from sharepoint using GetListItems -
Selecting soap action "GetListItems" under "lists.asmx" webservice provides details which can be used to extract listitems from sharepoint.
CAML Format to extract the listitems -
Target is to extract all the listitems. The default row limit of SharePoint is 100 rows which can be overrided as shown below.
Note: If you want to extract data incrementally, all the list have inbuild column "Modified" which can be used as the filter in the CAML query.
Details of how CAML for GetListItems can be found here on msdn. CAML can be constructed to select the columns of your choice,order them , filter them , etc.
Data required to invoke the webservice to get listitems.
Logical View of the Mapping to extract sharepoint listitems -
Execution View of the Mapping to get listitems -
After executing the mapping -
All the listitems (rows) returned in the XML are in the CLOB field.
Parsing the XML -
Conventional OWB Map using SQL Override feature to parse the XML.
Undbounded View Query -
The Unbounded view using the table EMPLOYEE_LISTITEMS. The query parses the XML to get all the listitems.
Executing the Map -
Integrating with SharePoint - change data can be easily tracked but there is no webservice which returns deleted data. Either a minus operation after complete extraction or creating a webservice on sharepoint server can easily fix this issue.
Webservices are slow by nature but again with parallel option and cdc techniques data can be easily pushed into sharepoint.
--
No comments:
Post a Comment