Blogs

Fetching XML API data

04 februari 2019

Fetching XML API data

I recently finished a project for which I had to fetch public data of parking lots and load it to a multiprovider to use it in reporting.  The data that I needed to fetch was from an open data API. After successfully finishing this project I thought I would share how I did this as an easy guide for others.

So to start off, you need an API from which you want to fetch data to use. The ones I came across for my project where in XML format.

The first thing you need to do before you can fetch the data is make the connection trusted. For this we need to take the certificate from the API:

Save the document somewhere you can easily find it back.

Once we have the certificate we need to implement it in transaction STRUST. Select a SSL client to which you want to implement the certificate on the left panel.

Click the left button to import the certificate that you just saved.
And add it by clicking the ‘Add to certificate list’ button.

On transaction SM59

Create a new HTTP connection to an external server.

Fill in the required fields: Target Host and Path Prefix (Proxy only required if used, my company uses it so I had to implement it).


In the logon and security tab you need to select the SSL certificate to which you added your certificate on STRUST. I used the ANONYM SLL and then you can test the connection (Left top corner). You should get following.


Once our connection is tested successful and the certificate is trusted we can start to create a function module which will fetch the data that we want.
First we need to make connection with the API in the ABAP code.
in our company we use a proxy so I used following code in order to connect to the API:
DATA: lo_http_client TYPE REF TO if_http_client,
lv_service TYPE string,
lv_url TYPE string,
lv_result TYPE string,
lo_ixml TYPE REF TO if_ixml,
lo_streamfactory TYPE REF TO if_ixml_stream_factory,
lo_istream TYPE REF TO if_ixml_istream,
lo_document TYPE REF TO if_ixml_document,
lo_parser TYPE REF TO if_ixml_parser.

cl_http_client=>create_by_url(
EXPORTING
url                = ‘URL to the API’

“Only necessary when using proxy
proxy_host         = ‘yourProxyHost’
proxy_service      = ‘yourProxyService’
ssl_id             = ‘YourSSLID’

IMPORTING
client             = lo_http_client
EXCEPTIONS
argument_not_found = 1
plugin_not_active  = 2
internal_error     = 3
OTHERS             = 4 ).

lo_http_client->send(
EXCEPTIONS
http_communication_failure = 1
http_invalid_state         = 2 ).

lo_http_client->receive(
EXCEPTIONS
http_communication_failure = 1
http_invalid_state         = 2
http_processing_failed     = 3 ).
“Prepare XML structure
CLEAR lv_result .
lv_result = lo_http_client->response->get_cdata( ).
lo_ixml = cl_ixml=>create( ).
lo_streamfactory = lo_ixml->create_stream_factory( ).
lo_istream = lo_streamfactory->create_istream_string(
lv_result ).
lo_document = lo_ixml->create_document( ).
lo_parser = lo_ixml->create_parser(
stream_factory = lo_streamfactory
istream        = lo_istream
document       = lo_document ).

“This actually makes the XML document navigable
lo_parser->parse( ).
I personally think this was the most important part of the project, because once you have connection with the API from there on it is just reading in data that the API provides. You can test if your connection to the API is working in the following manner:


Start the function module and run it with the debugger. Run through the program with F6 till lv_result is filled up with data. If this returns XML data your connection is working. If it fails lv_result will have an error statement e.g. a 404 that the URL does not exist etc.

So once this works it’s simply reading out the data from the xml file that you deem needed for your reporting. Here you can find how I did this for my project. I implement pretty simple and straightforward code here. I never used this before in ABAP and if you have better ways of doing feel free to let me know:

Here is an example of how the XML structure could look like:
<FirstNode>
<SecondNode>

<name>parkingName1</name>

<number>123</number>

</SecondNode>

<SecondNode>

<name>parkingName2</name>

<number>321</number>

</SecondNode>

</FirstNode>

 

 

ABAP:

DATA: “FirstNode
lo_firstnode_element        TYPE REF TO if_ixml_element,
lo_firstnode_nodes          TYPE REF TO if_ixml_node_list,
lv_firstnode_index          TYPE i,
lv_firstnode_length         TYPE i,

“SecondNode
lo_second_element        TYPE REF TO if_ixml_element,
lo_second_nodes          TYPE REF TO if_ixml_node_list,
lv_second_index          TYPE i,
lv_second_length         TYPE I,

lv_node_name             TYPE string.

“Firstnode: contains all FirstNode elements
LO_firstnode_ELEMENT = LO_DOCUMENT->FIND_FROM_NAME( ‘FirstNode’ ).
LO_firstnode_NODES = LO_firstnode_ELEMENT->GET_CHILDREN( ).
LV_firstnode_INDEX = 0.
“Amount of all the underlying items
LV_firstnode_LENGTH = LO_firstnode_NODES->GET_LENGTH( ).

WHILE LV_firstnode_INDEX < LV_firstnode_LENGTH.

“Get the underlying node of firstnode corresponding to the index
LO_secondnode_CURR = LO_firstnode_NODES->GET_ITEM( LV_firstnode_INDEX ).
LV_secondnode_INDEX = 0.
LV_secondnode_LENGTH = LO_secondnode_CURR->GET_CHILDREN( )->GET_LENGTH( ).

WHILE LV_secondnode_INDEX < LV_secondnode_LENGTH.
“Get the name of the node under SecondNode
LV_NODE_NAME = LO_secondnode_CURR->GET_CHILDREN( )->GET_ITEM( LV_secondnode_INDEX )->GET_NAME( ).

CASE LV_NODE_NAME.
WHEN ‘name’.

WRITE lv_secondnode_curr->GET_CHILDREN( )->GET_ITEM( LV_secondnode_INDEX )->GET_VALUE( ).

WHEN ‘number’.

WRITE lv_secondnode_curr->GET_CHILDREN( )->GET_ITEM( LV_secondnode_INDEX )->GET_VALUE( ).
ENDCASE.

ADD 1 to lv_secondnode_index.
ENDWHILE.

ADD 1 TO LV_firstnode_INDEX.

ENDWHILE.

 

 

 

What this code does is loop through the XML nodes and simply watches if it comes across one of the fields that I need. If it comes across a field that I need it will simply write it down. Here you can easily create an internal table based on a direct update DSO, fill the internal table with the data, then APPEND the internal table to a structure of the DSO and MODIFY the table to insert the data. This way you can write away data to use for reporting.

So this is how I handled my project on how to fetch data from an API. If you have remarks or questions feel free to let me know.

Sven Swennen

  • Blogs

    December 2020

    That is the deadline when you are running a BW version 7.4 or older. SAP will end the mainstream maintenance for older BW products and encourage companies to move to newer versions, preferably BW/4HANA. If you read this and find yourself in this situation, don’t panic. Politicians would say that it’s way past 5 before 12. Luckily, we are consultants and see a solution instead of a problem.

    Read more >
  • Blogs

    Making forums a better place

    Before starting my career in IT, I almost never went to online forums. However quickly after starting my career as an IT’er I came across multiple forums/communities in which I became (and still am) active.

    Read more >
  • Blogs

    so.. SAP Lumira is dead.. now What ?

    As a consultant I’m responsible for the Analytics environment for an Innovative Company in Natural Chemistry and innovative in their IT. Recently I was forced to ponder on the post-Lumira question “ Now what ? ”.

     

    Read more >