Extracting data with JSONPath and XPath in Web Connect
On this page
This guide explains how to extract data from a Web Connect response using JSONPath and XPath. For the rest of the Web Connect settings, see the Web Connect setup guide.
Advanced data extraction
Using JSONPath
The JSONPath enables you to specify where a particular parameter or its value is located. This means that you can filter for one specific value in a JSON response or filter for specific parameters and their values. This is a general feature available for any text formatted in JSON with correct syntax. See the example below to help you understand this concept:
-
Visit JSONPath.com.
-
Paste the following code snippet into the left window displayed on the JSONPath.com website:
{ "store": { "book": [ { "category": "reference", "author": "Nigel Rees", "title": "Sayings of the Century", "price": 8.95 }, { "category": "fiction", "author": "Evelyn Waugh", "title": "Sword of Honour", "price": 12.99 }, { "category": "fiction", "author": "Herman Melville", "title": "Moby Dick", "isbn": "0-553-21311-3", "price": 8.99 }, { "category": "fiction", "author": "J. R. R. Tolkien", "title": "The Lord of the Rings", "isbn": "0-395-19395-8", "price": 22.99 } ], "bicycle": { "color": "red", "price": 19.95 } } } -
To only filter for all book titles in the response, enter the following code to the JSONPath Syntax field at the top of the page:
$.store.book[*]
As a result, you used the JSONPath to filter for a specific part of the response. For more information, see JSON with JSONPath from REST API Tutorials.
Using XPath
XPath (XML Path Language) enables you to specify where a particular element, attribute, or value is located in an XML response. This means that you can filter for one specific value in an XML response or filter for specific elements and their values. This is a general feature available for any text formatted in XML with correct syntax. See the examples below to help you understand this concept.
When configuring XPath in Web Connect, the XPath expression in the Data key field identifies which elements in the XML represent individual rows of data. Adverity processes each matching element as a separate row.
The XPath expressions in the Column mapping field are evaluated relative to each row element identified by the Data key. This means that each XPath expression in your column mapping is executed in the context of a single row element.
Example 1: Simple structure
For a simple XML structure where each row element contains all the data you need:
<PriceList>
<Item>
<Date>2023-01-15</Date>
<Price>165.83</Price>
</Item>
<Item>
<Date>2023-01-16</Date>
<Price>166.97</Price>
</Item>
<Item>
<Date>2023-01-17</Date>
<Price>167.43</Price>
</Item>
</PriceList>
Configuration:
-
Data key:
//ItemThis XPath expression finds all
Itemelements in the XML document. EachItemelement represents one row of data. -
Column mapping:
{ "Date": "Date/text()", "Price": "Price/text()" }This creates a data extract with two columns: Date and Price, containing the text content from the respective child elements of each
Item.You can also use
./Date/text()and./Price/text()instead ofDate/text()andPrice/text(). Both forms work correctly.
Example 2: Accessing parent-level attributes
For XML where row elements are nested within a parent container that contains descriptive attributes or metadata about the data series. This pattern is useful when each parent element represents a distinct data series with its own characteristics.
<ExchangeRates>
<CurrencySeries>
<Currency>US Dollar</Currency>
<Code>USD</Code>
<Rates>
<Rate>
<Number>001</Number>
<EffectiveDate>2023-01-02</EffectiveDate>
<Value>3.5963</Value>
</Rate>
<Rate>
<Number>002</Number>
<EffectiveDate>2023-01-03</EffectiveDate>
<Value>3.6045</Value>
</Rate>
</Rates>
</CurrencySeries>
</ExchangeRates>
Configuration:
-
Data key:
//RateThis XPath expression finds all
Rateelements. EachRateelement represents one row of data. -
Column mapping:
{ "Currency": "../../Currency/text()", "Code": "../../Code/text()", "Rate_Number": "./Number/text()", "EffectiveDate": "./EffectiveDate/text()", "Value": "./Value/text()" }This creates a data extract with five columns:
-
Currency and Code are extracted from the parent
CurrencySerieselement using../../to navigate up two levels. These values describe the series and are the same for all rates within that series. -
Rate_Number, EffectiveDate, and Value are extracted from the current
Rateelement using./or direct element names.
Use this pattern when your XML structure groups related rows under a parent element that contains descriptive information about that group.
-
Example 3: Accessing table-level metadata
For XML where row elements are nested within a table or dataset container that contains metadata fields applicable to all rows. This pattern is useful when you need to include table identifiers, effective dates, or other metadata that applies to the entire dataset.
<DataSeries>
<Series>
<Table>A</Table>
<TableNumber>001</TableNumber>
<EffectiveDate>2023-01-02</EffectiveDate>
<Items>
<Item>
<Name>Product A</Name>
<Code>PRD001</Code>
<Price>19.99</Price>
</Item>
<Item>
<Name>Product B</Name>
<Code>PRD002</Code>
<Price>24.99</Price>
</Item>
</Items>
</Series>
</DataSeries>
Configuration:
-
Data key:
//ItemThis XPath expression finds all
Itemelements. EachItemelement represents one row of data. -
Column mapping:
{ "Table": "../../Table/text()", "TableNumber": "../../TableNumber/text()", "EffectiveDate": "../../EffectiveDate/text()", "Name": "./Name/text()", "Code": "./Code/text()", "Price": "./Price/text()" }This creates a data extract with six columns:
-
Table, TableNumber, and EffectiveDate are extracted from the parent
Serieselement using../../to navigate up two levels. These are table-level metadata fields that identify the dataset and apply to all items within that series. -
Name, Code, and Price are extracted from the current
Itemelement.
Use this pattern when your XML structure includes dataset-level metadata (such as table identifiers, publication dates, or version numbers) that you want to include in every row of your data extract.
-
Example 4: Attributes and text content
For XML that uses both element text content and attributes:
<Products>
<Product id="1" category="Electronics">
<Name>Laptop</Name>
<Price currency="USD">999.99</Price>
</Product>
<Product id="2" category="Books">
<Name>Novel</Name>
<Price currency="EUR">19.99</Price>
</Product>
</Products>
Configuration:
-
Data key:
//ProductThis XPath expression finds all
Productelements. EachProductelement represents one row of data. -
Column mapping:
{ "Product_ID": "./@id", "Category": "./@category", "Product_Name": "./Name/text()", "Price": "./Price/text()", "Currency": "./Price/@currency" }This creates a data extract with five columns:
-
Product_ID and Category are extracted from attributes of the
Productelement using./@attributeName -
Product_Name and Price are extracted from text content using
./ElementName/text() -
Currency is extracted from an attribute of a child element using
./Price/@currency
-
XPath validator tools
When developing XPath expressions, you can use online tools to test and validate your XPath syntax before configuring them in Web Connect.
Visit XPath Validator.
Paste your XML response into the input field and test each XPath expression individually to verify it returns the expected results.
Troubleshooting XPath expressions
If your XPath expressions aren’t returning the expected data, check the following:
- No results returned
If your XPath expressions return no data, verify the following:
- Context issues
Make sure you’re using the correct path relative to the row element. If data is in a parent element, use
../or../../to navigate up. If data is in a child element, use./or the element name directly.- Attribute vs text
Verify whether you need an attribute or text content. Use
./@attributeNameor./ElementName/@attributeNamefor attributes, and./ElementName/text()orElementName/text()for text content.
- Common mistakes to avoid
Using absolute paths (
//) in column mapping when you need relative pathsUsing
@valuewhen you need/text()or vice versaNot using
../or../../when accessing parent elementsForgetting to include
/text()when extracting text content
- Testing your XPath
To verify your XPath expressions work correctly:
Use an online XPath validator to test each expression individually
Verify the XML structure matches your expectations
Test in Web Connect with a small date range first
Check the data extract preview to verify column values
As a result, you used XPath to filter for specific parts of the XML response and extract the data you need.