SSIS – Extract single XML node using XPath from SOAP response

Introduction

In this post, you will learn how to extract a single XML node value from your XML web response (SOAP Web service call) using an XPath expression in the SSIS REST API Web Service Task and the XML Parser Task.

This article assumes you have basic knowledge of how to call a REST API or a SOAP Web service in SSIS. If you are not familiar with that, then refer to the following articles.

Calling SOAP Web Service in SSIS (XML Source)

Call REST API using SSIS Web Service Task / JSON / XML Source

Calling REST / SOAP Web service using the SSIS REST API Task

Refer to the following video on how to call a REST API web service.

Calling REST API in SSIS using REST API Task, Pass headers, Body, Url Parameters

Extract the REST API Response into a file

Here is how you can save the response into a variable or a file. Go to the Response settings tab and select the check Response content option, as shown below. You can also Filter Response content (e.g., Extract only a single value from a node).

SSIS Rest API Task – Extract data from a single XML node using XPath expression (Namespace used)

Extract a single XML Node using XPath (XML with Namespace)

To extract a single value from the Response XML, you can define an XPath expression on the REST API Response tab as follows. If your response XML document contains namespaces (e.g., the node looks like this: <ns1:Body xmlns:ns1=”http://abc.com”><ns1:Data>abcdefg</ns1:Data></ns1:Body>), then you have to write XPath in a particular way, as shown below. Notice we have not specified the Namespace prefix ns1

Sample XML Document (Or web response from SOAP Service)

XPath Expression Example:

XPath Output:

The above expression will produce the following result

Extract Attribute Value using XPath local-name() function

Now, let’s find out how to extract the attribute value rather than the element.

Consider the following XML. Let’s say we want to extract the id from the data node. In such a case, the previous XPath expression won’t work, so we need to use a slightly different XPath.

Let’s use the XML Parser Task to test XPath.

Expression 1 – Get value by direct attribute name

Write the following expression as below and click Test

Extract XML Attribute Value using XPath expression (Use local-name function)

Expression 2 – Get specific element attribute value

If you want to be specific and read the value of the id attribute from a particular data element, you can use an expression like this:

Extract XML Attribute Value using XPath expression 2

Configure the SSIS Rest API Task to filter XML Response using XPath

The screenshot below shows how to filter XML data using an XPath expression. You can save extracted data to an SSIS Variable or save it to a file.

SSIS Rest API Task – Extract data from a single XML node using XPath expression (Namespace used)

More Examples of XPath expressions