5

I am trying to read in data from an API with Azure Data Factory. First I need to call a log in method, which provides an XML response. I need to take an element from that XML and put it into my next API call to get the data that I need.

Currently I am using the Copy data tool to call the log in method and save the XML to blob storage. How do I now read an element of that XML into a variable?

If there is a better way of doing this then please advise, but I would still like to know how to read an XML element into a variable.

Edit: here is the XML being returned. I need to capture the SessionID.

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <DoLoginResponse xmlns="http://foo.bar">
            <DoLoginResult>
                <OperationStatus>true</OperationStatus>
                <StatusMessage />
                <SecurityProfile>
                    <User></User>
                    <Session>
                        <SessionId>d2cf6ea6-120f-4bff-a5d1-adad9063d9d2</SessionId>
                    </Session>
                    <IsFirstLogon>true</IsFirstLogon>
                    <IsSystemOwner>false</IsSystemOwner>
                </SecurityProfile>
            </DoLoginResult>
        </DoLoginResponse>
    </soap:Body>
</soap:Envelope>
Improve this question
3
  • 1
    Do you want to extract a part of the xml file into a string variable? Commented Apr 27, 2021 at 7:29
  • Can you show us some example data and which part you want to extract? Commented Apr 27, 2021 at 7:30
  • 1
    I don't think ADF supports processing XML inline like this. One idea would be to pass the XML payload to an Azure Function that parses it and returns it as JSON, which would be easily readable into a variable. Commented Apr 27, 2021 at 18:24

3 Answers 3

Reset to default
6

Solution1:

I ended up getting it done by using the Lookup activity sourcing an XML data set connected an HTTP linked service. The returned XML is output from the activity as a json object which is normally accessable with activity('GetSessionID').output.etc. However, some of the element names contain a colon (soap:Envelope and soap:Body) and Azure Data Factory gave me a "BadRequest" error when I put these in as dynamic content. To get around this I converted it to XML, to string, stripped out the colons, converted back to xml, then to json. From there I could access the property like normal. This is the dynamic content that gave me the session id:

@json(xml(replace(string(xml(activity('GetSessionID').output.firstRow)), ':', ''))).Envelope.Body.DoLoginResponse.DoLoginResult.SecurityProfile.Session.SessionId

Solution2:

I think it is ok to extract a part of the xml file into a string variable. My idea is to convert the xml file into a string, and dynamically extract the SessionId part according to the expression.

I created a simple test here:

  1. I'm using Lookup activity to get the xml file, you should replace with your web activity. I declared 2 String variables XMLString and SessionId: enter image description here

  2. In Set variable1 activity, add dynamic content @string(activity('Lookup1').output.value[0]) to assign value to variable XMLString. If you are using Web activity, the content should be @string(activity('<Web_Actvity_Name>').output). enter image description here

  3. In Set variable2 activity, add dynamic content @substring(variables('XMLString'),add(indexof(variables('XMLString'),'SessionId'),12),sub(indexof(variables('XMLString'),'}'),add(lastindexof(variables('XMLString'),'SessionId'),13))) to assign value to variable SessionId. enter image description here The value of SessionId is as follows:
    enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I ended up getting it done by using the Lookup activity sourcing an XML data set connected an HTTP linked service. The returned XML is output from the activity as a json object which is normally accessable with activity('GetSessionID').output.etc. However, some of the element names contain a colon (soap:Envelope and soap:Body) and Azure Data Factory gave me a "BadRequest" error when I put these in as dynamic content. To get around this I converted it to XML, to string, stripped out the colons, converted back to xml, then to json. From there I could access the property like normal. This is the dynamic content that gave me the session id: @json(xml(replace(string(xml(activity('GetSessionID').output.firstRow)), ':', ''))).Envelope.Body.DoLoginResponse.DoLoginResult.SecurityProfile.Session.SessionId

Improve this answer

1 Comment

Hi @jazzabeanie Your solution is great. I added it to my answer. This can be beneficial to other community members.
0

I generally try and force the API to return results in JSON format so it's easier to work with in different situations. This can be done generally by adding a header to the api call. Key = Accept, Value = application/json;odata=verbose

Improve this answer

1 Comment

This should be a comment; rather than an actual answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.