How to use Worksheet.XmlDataQuery in the xlwings API way

The XmlDataQuery member of the Worksheet object in Excel’s object model is a method that returns a Range object representing the cell or cells mapped to a specific XML map data node. This is particularly useful when working with XML data mapped into an Excel worksheet, allowing you to programmatically locate and manipulate data based on its XML structure. In xlwings, you can access this functionality through the api property, which provides direct access to the underlying Excel object model.

Functionality:
The primary purpose of XmlDataQuery is to query a worksheet for a range that is associated with a given XML map element or attribute. It helps in dynamically finding cells that are bound to XML data, enabling automated data processing, validation, or extraction within workbooks that use XML maps.

Syntax in xlwings:
The method is called via the worksheet’s API object. The general syntax is:

range_object = worksheet.api.XmlDataQuery(XPath, SelectionNamespaces, Map)

• XPath (required, string): The XPath expression that specifies the XML map node. This can be an absolute path to an element or attribute.
• SelectionNamespaces (optional, string): A space-delimited string of namespace declarations used in the XPath. It is required if the XPath contains namespaces. For example: "xmlns:ns='http://example.com/namespace'".
• Map (optional, Variant): An XmlMap object representing the specific XML map to query. If omitted, Excel uses all maps in the workbook. You can pass an XmlMap object obtained via workbook.api.XmlMaps(index_or_name).

The method returns a Range object (or None if no matching range is found), which you can then use with xlwings for further operations.

Example:
Suppose you have an XML map in your workbook with data mapped to a worksheet, and you want to find the cell containing the “Price” element. Here’s how you might use XmlDataQuery with xlwings:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
ws = wb.sheets['Sheet1']

# Define the XPath for the XML node (e.g., an element named 'Price')
xpath = "/Invoice/Items/Item/Price"

# Optionally, define namespaces if needed (e.g., for a namespace 'ns')
namespaces = "xmlns:ns='http://schemas.example.com/invoice'"

# Query for the range
try:
    # Use the api to call XmlDataQuery
    target_range = ws.api.XmlDataQuery(XPath=xpath, SelectionNamespaces=namespaces)
    if target_range is not None:
        # Convert to xlwings range for easier manipulation
        xl_range = xw.Range(target_range)
        print(f"Found data at cell: {xl_range.address}")
        print(f"Cell value: {xl_range.value}")
    else:
        print("No matching range found.")
except Exception as e:
    print(f"Error querying XML data: {e}")