CODEWITHSUNDEEP
xmlazure-data-factory
jazzabeanie
jazzabeanie

Reputation: 445

How to read an XML element into a variable with Azure Data Factory

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>

Upvotes: 5

Views: 6691

Answers (3)

Brian MacDonald
Brian MacDonald

Reputation: 1

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

Upvotes: 0

Joseph Xu
Joseph Xu

Reputation: 6083

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

Upvotes: 6

jazzabeanie
jazzabeanie

Reputation: 445

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

Upvotes: 1

Related Questions