Reading data from sap xml file in Logic Apps along with null values

amitlalazure503 26 Reputation points
2021-02-08T16:01:11.37+00:00

Hi members,
I'm looking to extract XML content/values from the sap application table structure generated XML file in the attachment. I tried to parse using JSON but could not handle null values as well.

Tried below but didn't worked for me.
@{xpath(xml(base64ToBinary(triggerBody()?['RFC_READ_TABLEResponse'])),'string(WA)')}

XML with null value when no data fetched, Logic app compose should display NULL or something! If you note <WA> field is not available below

<RFC_READ_TABLEResponse xmlns="http://Microsoft.LobServices.Sap/2007/03/Rfc/"><DATA></DATA><FIELDS><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>MANDT</FIELDNAME><OFFSET>000000</OFFSET><LENGTH>000003</LENGTH><TYPE>C</TYPE><FIELDTEXT>Client</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>BNAME</FIELDNAME><OFFSET>000003</OFFSET><LENGTH>000012</LENGTH><TYPE>C</TYPE><FIELDTEXT>User Name in User Master Record</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>TERMID</FIELDNAME><OFFSET>000015</OFFSET><LENGTH>000010</LENGTH><TYPE>I</TYPE><FIELDTEXT>Terminal ID</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>SERVER</FIELDNAME><OFFSET>000025</OFFSET><LENGTH>000040</LENGTH><TYPE>C</TYPE><FIELDTEXT>Server</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>TERMINAL</FIELDNAME><OFFSET>000065</OFFSET><LENGTH>000036</LENGTH><TYPE>C</TYPE><FIELDTEXT>Terminal</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>SPRACHE</FIELDNAME><OFFSET>000101</OFFSET><LENGTH>000001</LENGTH><TYPE>C</TYPE><FIELDTEXT>Logon Language</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>LOGON_DATE</FIELDNAME><OFFSET>000102</OFFSET><LENGTH>000008</LENGTH><TYPE>D</TYPE><FIELDTEXT>Last Logon Date</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>LOGON_TIME</FIELDNAME><OFFSET>000110</OFFSET><LENGTH>000006</LENGTH><TYPE>T</TYPE><FIELDTEXT>Last Logon Time</FIELDTEXT></RFC_DB_FLD></FIELDS><OPTIONS></OPTIONS></RFC_READ_TABLEResponse>

XML with WA value when data fetched, Logic app compose should display data under <WA> . If you note <WA> field is available below

<RFC_READ_TABLEResponse xmlns="http://Microsoft.LobServices.Sap/2007/03/Rfc/"><DATA><TAB512 xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><WA>001AMIT 16 vhcalnplci_NPL_00 73.111.216.71-serverbook E20210208155333</WA></TAB512></DATA><FIELDS><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>MANDT</FIELDNAME><OFFSET>000000</OFFSET><LENGTH>000003</LENGTH><TYPE>C</TYPE><FIELDTEXT>Client</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>BNAME</FIELDNAME><OFFSET>000003</OFFSET><LENGTH>000012</LENGTH><TYPE>C</TYPE><FIELDTEXT>User Name in User Master Record</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>TERMID</FIELDNAME><OFFSET>000015</OFFSET><LENGTH>000010</LENGTH><TYPE>I</TYPE><FIELDTEXT>Terminal ID</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>SERVER</FIELDNAME><OFFSET>000025</OFFSET><LENGTH>000040</LENGTH><TYPE>C</TYPE><FIELDTEXT>Server</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>TERMINAL</FIELDNAME><OFFSET>000065</OFFSET><LENGTH>000036</LENGTH><TYPE>C</TYPE><FIELDTEXT>Terminal</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>SPRACHE</FIELDNAME><OFFSET>000101</OFFSET><LENGTH>000001</LENGTH><TYPE>C</TYPE><FIELDTEXT>Logon Language</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>LOGON_DATE</FIELDNAME><OFFSET>000102</OFFSET><LENGTH>000008</LENGTH><TYPE>D</TYPE><FIELDTEXT>Last Logon Date</FIELDTEXT></RFC_DB_FLD><RFC_DB_FLD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/"><FIELDNAME>LOGON_TIME</FIELDNAME><OFFSET>000110</OFFSET><LENGTH>000006</LENGTH><TYPE>T</TYPE><FIELDTEXT>Last Logon Time</FIELDTEXT></RFC_DB_FLD></FIELDS><OPTIONS></OPTIONS></RFC_READ_TABLEResponse>

65339-image.png

Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,573 questions
SAP HANA on Azure Large Instances
SAP HANA on Azure Large Instances
Microsoft branding terminology for an Azure offer to run HANA instances on SAP HANA hardware deployed in Large Instance stamps in different Azure regions.
120 questions
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,965 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MayankBargali-MSFT 69,946 Reputation points
    2021-02-10T09:55:16.257+00:00

    Hi @amitlalazure503

    I have tested below expression with both the XML's. Inputxml is the parameter that I have defined in my logic app. 

    xpath(xml(parameters('Inputxml')), '/*[name()="RFC_READ_TABLEResponse"]/*[name()="DATA"]/*[name()="TAB512"]/*[name()="WA"]/text()')

    If there are matches across the document, it returns an array else returns the empty array
    ["001AMIT 16 vhcalnplci_NPL_00 73.111.216.71-serverbook E20210208155333"]

    I will also update the docs with the correct syntax on the xpath examples.

    Reference: https://video2.skills-academy.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#xpath