How to use Worksheet.XmlMapQuery in the xlwings API way

The XmlMapQuery member of the Worksheet object in the Excel object model provides a powerful interface for querying and retrieving data from XML maps that have been added to a workbook. In xlwings, this functionality is accessible through the api property, allowing Python scripts to interact directly with Excel’s underlying COM objects. This is particularly useful for extracting structured data from XML sources mapped into Excel worksheets, enabling automation of data retrieval and integration tasks.

Functionality:
The XmlMapQuery method executes a query against an XML map associated with the worksheet, returning a Range object that represents the cells containing the queried data. It allows you to specify XPath expressions to filter or select specific nodes from the XML data, making it possible to import only relevant subsets into Excel. This is essential for handling large or complex XML datasets where selective data extraction is needed for analysis or reporting.

Syntax in xlwings:
In xlwings, you call this member via the api property of a Worksheet object. The general syntax is:

worksheet.api.XmlMapQuery(XPath, SelectionNamespaces, Map)

• XPath: A string specifying the XPath expression to query the XML map. This determines which data nodes are retrieved. For example, "/root/element" selects all element nodes under the root.
• SelectionNamespaces: A string containing the namespace declarations required for the XPath query, if the XML uses namespaces. It should be formatted as a space-separated list of xmlns:prefix="URI" declarations. For instance, 'xmlns:ns="http://example.com"'.
• Map: An optional parameter that specifies the XmlMap object to query. If omitted, Excel uses the first XML map in the workbook. You can pass an XmlMap object retrieved via the workbook’s XmlMaps collection.

Example Usage:
Suppose you have an XML map in an Excel workbook linked to a worksheet, and you want to query data from it using xlwings. Below is a code example that demonstrates how to use XmlMapQuery to retrieve specific data:

import xlwings as xw

# Connect to the open Excel workbook and worksheet
app = xw.apps.active
wb = app.books.active
ws = wb.sheets['Sheet1']

# Define the XPath query and namespaces (if needed)
xpath_expression = "/Orders/Order[Status='Shipped']"
namespaces = 'xmlns:ord="http://www.example.com/orders"'

# Execute the XML map query
# Assuming the first XML map in the workbook is used
result_range = ws.api.XmlMapQuery(xpath_expression, namespaces)

# Check if data was returned and output it
if result_range:
    # Convert the range to a list of lists for easy processing in Python
    data = result_range.value
    print("Queried Data:", data)
    # You can now analyze or visualize this data using Python libraries
else:
    print("No data found for the query.")

# Alternatively, specify a particular XML map by name
xml_map = wb.api.XmlMaps("MyXmlMap") # Replace with your map name
result_range_specific = ws.api.XmlMapQuery(xpath_expression, namespaces, xml_map)