Skip to content
Book a demo

Connect documentation

All documentation
Documentation

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:

  1. Visit JSONPath.com.

  2. 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
          }
      }
    }
    
  3. 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: //Item

    This XPath expression finds all Item elements in the XML document. Each Item element 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 of Date/text() and Price/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: //Rate

    This XPath expression finds all Rate elements. Each Rate element 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 CurrencySeries element 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 Rate element 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: //Item

    This XPath expression finds all Item elements. Each Item element 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 Series element 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 Item element.

    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: //Product

    This XPath expression finds all Product elements. Each Product element 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 Product element 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 ./@attributeName or ./ElementName/@attributeName for attributes, and ./ElementName/text() or ElementName/text() for text content.

Common mistakes to avoid
  • Using absolute paths (//) in column mapping when you need relative paths

  • Using @value when you need /text() or vice versa

  • Not using ../ or ../../ when accessing parent elements

  • Forgetting to include /text() when extracting text content

Testing your XPath

To verify your XPath expressions work correctly:

  1. Use an online XPath validator to test each expression individually

  2. Verify the XML structure matches your expectations

  3. Test in Web Connect with a small date range first

  4. 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.