SSIS – Extract single XML node using XPath from SOAP response

Introduction

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

This article assumes you have basic knowledge how to call REST API or SOAP Web service in SSIS. If you are not familiar with that then refer 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 SSIS REST API Task

Refer following video how to call REST API web service.

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

Extract REST API Response into file

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

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

Extract single XML Node using XPath (XML with Namespace)

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

Sample XML Document (Or web response from SOAP Service)

XPath Expression Example:

XPath Output:

Above expression will produce below result

Extract Attribute Value using XPath local-name() function

Now lets find out how to extract attribute value rather than element.

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

Let’s use XML Parser Task to test XPath.

Expression 1 – Get value by direct attribute name

Write 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 (Use local-name function)

Configure SSIS Rest API Task to filter XML Response using XPath

Below screenshot shows how to filter XML data using XPath expression. You can save extracted data to SSIS Variable or save to file.

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

More Examples of XPath expression