Skip to Content
Technical Articles

OData Service in SAP Cloud Platform Integration with $expand and DeepInsert

This blog post covers how to expose an OData Service in SAP Cloud Platform Integration with $expand and deepInsert Scenario.

Refer OData Service for Beginners

OData Sender Behavior

Below is a sample integration flow with OData Sender.

  1. OData sender converts the incoming OData Request payload (atom+xml / json) ① to simple XML. ②
  2. For OData Operations which has response body (create, read), the final step in the IFlow has to be a simple XML which represents the Entities defined in OData sender Edmx.  ③
  3. This final XML is converted back to atom+xml / json to the client. ④

Contents

  1. Create OData Sender Artifact

  2. Query with $expand

  3. Create with DeepInsert

1. Create an OData Sender Artifact

  1. I used Northwind service edmx to create an OData Service “NorthwindCopy”.
  2. Choose the entities that you need. I have chosen Category, Customer, Employee, Order, Product, Supplier, Territory.
  3. Edit the metadata to define the navigation properties for each of the Entities as desired. I have edited the metadata as required and below is the graphical representation of my metadata.

2. Query with $expand

I will configure $expand for EmployeeSet?$expand=Orders,Territories,Orders/Customer

  1. Bind the Query operation for EmployeeSet.
  2. The default integration flow will have only the root entity in response mapping.
    In this case only EmployeeSet is mapped.
  3. We need a mapping for $expand with Orders, Territories, Order/Customer. For this, I will use a dummy OData Receiver. (This will be deleted later)
  4. In the Processing tab of this dummy receiver, open the query wizard by clicking Select in Resource Path.
  5. Choose Connection Source as Local EDMX File. Choose the OData sender metadata.
  6. Choose Query operation with the desired sublevel. Choose the fields. In this case, Orders,Territories,Orders/Customer. Click Finish and this will generate the required XSD. Note the name of the XSD created.
  7. Delete the dummy OData receiver that was created in previous step.
  8. Similarly, use the query wizard to generate the XSD for the OData Receiver.
  9. Use this generated XSD in response mapping step.
  10. Note that the expanded entities are mapped respectively.
  11. GET call <serviceRoot>/EmployeeSet?$expand=Orders,Territories,Orders/Customer will return expanded entities.

NOTE: If the Expanded entities are of cardinality 1:n and if not present, it is necessary to have an empty tag with the navigation property name as below.

<EmployeeSet>
	<Employee>
		...
		<Orders/>
		<Territories/>
	</Employee>
</EmployeeSet>

3. Create with DeepInsert

I will configure DeepInsert for Categories -> Products -> Supplier

  1. Bind the Create operation for Categories.
  2. The default integration flow will have only the root entity in request mapping.
    In this case, only CategorySet is mapped.
  3. Add a dummy OData receiver to generate the XSD for the sender.
  4. Open the query wizard, and choose the Sender EDMX. Select Create operation with sub level  and choose the entities.
  5. Delete the dummy OData receiver that was created.
  6. Similarly, configure the Create operation in Odata receiver with required sub levels.
  7. Select the created XSD in request mapping.
  8. Response for deepInsert can contain only the root entity. Hence, there will be no change in response mapping.
    UPDATE: Software increment 2101 allows deepinsert response to be of deep structure. You have to map the nested structure like request mapping.
  9. Deepinsert from Postman.
8 Comments
You must be Logged on to comment or reply to a post.
  • Hi Saranya,

    In the case of the On-premise TM system, what is the way to do the integration?

    I have created the OData Service in SEGW. What is the further process to achieve the same?

     

    Thanks,

    Badhusha

    • Hi Badhusha,
      I do not know about SEGW. In general, SAP cloud connector is recommended to have a secure connection between the On-premise SAP systems. You could look for blogs and other documents on this topic.

       

  • Hello Saranya

    How can I do a deep insert if the ID field in the LINK section is the ObjectID which is a field that is created automatically in the insert (when I put sublevel 2 the creation doesn't work anymore)

     

    Thanks,

     

    Julio

  • Hello Saranya ,

    I have done message mapping and calling POST url but system is not passing whole payload to oData backend . How can I solve this issue . What do you mean by mapping only root entity ?

    • By Root entity I mean only the parent entity. For Eg, IF you are doing deepinsert of Products and suppliers, then the entity that you mention in the POST url will be the root entity.

      POST https://<serviceroot>/Products  ---> root entity is Products. The request body can contain Products and the navigation entities.

       

      The system will pass the whole payload provided your mappings are done correctly.
      You can enable trace on the integration flow and monitor the trace.

  • /