Используйте XML-данные с mssql-python

Microsoft SQL предоставляет нативный xml тип данных с возможностями обработки на стороне сервера:

• XQuery для запросов к XML-контенту.
• XML-методы (value(), query(), exist()nodes(), modify(), ) для извлечения и модификации.
• Необязательная проверка схемы XML.
• XML-индексы производительности.

Драйвер mssql-python отправляет и принимает XML-данные в виде строк. Вся обработка XML (XQuery, методы XML, проверка схемы) выполняется на стороне Microsoft SQL. Используйте библиотеки xml.etree.ElementTree Python или похожие для разбора XML на стороне клиента.

Когда использовать XML или JSON: Используйте XML, когда нужна проверка схемы, поддержка пространства имён или смешанный контент (текст, перемежающийся с элементами). Используйте JSON (с nvarchar столбцами и JSON-функциями Microsoft SQL), когда ваши данные ориентированы на значение ключа, потребляются веб-API или не требуют контроля схемы. Большинство новых приложений предпочитают JSON, если данные сами по себе не структурированы по документам.

Вставить XML-данные

Передайте XML как строку Python; драйвер отправляет его в собственный тип столбца Microsoft SQL xml.

Вставка в виде строки

Напрямую вставлять XML-содержимое в виде строки в xml-столбец с помощью параметризованных запросов.

import mssql_python
from xml.etree import ElementTree as ET

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes"
)
cursor = conn.cursor()

# Create temp table for XML storage
cursor.execute("CREATE TABLE #XMLOrders (OrderID INT IDENTITY(1,1) PRIMARY KEY, OrderXML XML)")

# Insert XML as string
xml_data = """
<Order OrderID="1001">
    <Customer Name="John Doe" Email="john@example.com"/>
    <Items>
        <Item ProductID="A1" Quantity="2" Price="29.99"/>
        <Item ProductID="B2" Quantity="1" Price="49.99"/>
    </Items>
</Order>
"""

cursor.execute("""
    INSERT INTO #XMLOrders (OrderXML) VALUES (%(xml)s)
""", {"xml": xml_data})
conn.commit()

Постройте XML с помощью ElementTree

Создайте XML-документы программно с помощью библиотеки ElementTree Python, затем конвертируйте в строку для вставки.

from xml.etree import ElementTree as ET

# Build XML document
order = ET.Element("Order", OrderID="1002")
customer = ET.SubElement(order, "Customer", Name="Jane Smith", Email="jane@example.com")
items = ET.SubElement(order, "Items")
ET.SubElement(items, "Item", ProductID="C3", Quantity="3", Price="19.99")
ET.SubElement(items, "Item", ProductID="D4", Quantity="2", Price="39.99")

# Convert to string
xml_string = ET.tostring(order, encoding="unicode")

cursor.execute("""
    INSERT INTO #XMLOrders (OrderXML) VALUES (%(xml)s)
""", {"xml": xml_string})
conn.commit()

Проверьте XML перед вставкой

Проверяйте синтаксис XML на сервере, используя TRY/CATCH SQL и приведение к типу XML, чтобы отклонять некорректно сформированные документы перед сохранением.

# Validate XML with TRY/CATCH
cursor.execute("""
    BEGIN TRY
        DECLARE @xml XML = CAST(%(xml)s AS XML);
        SELECT @xml.value('(/Order/@OrderID)[1]', 'INT') AS Validated;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
""", {"xml": xml_data})

Запрос XML-данных

Используйте XML-методы на сервере для извлечения определённых значений без загрузки полных документов клиенту.

Метод value() типа XML

Извлекать одиночные скалярные значения или атрибуты из XML с помощью value() метода с выражениями XPath.

Извлечение скалярных значений из XML:

cursor.execute("""
    SELECT 
        OrderID,
        OrderXML.value('(/Order/@OrderID)[1]', 'INT') AS XmlOrderID,
        OrderXML.value('(/Order/Customer/@Name)[1]', 'NVARCHAR(100)') AS CustomerName,
        OrderXML.value('(/Order/Customer/@Email)[1]', 'NVARCHAR(100)') AS Email
    FROM #XMLOrders
""")

for row in cursor:
    print(f"Order {row.XmlOrderID}: {row.CustomerName} ({row.Email})")

Метод XML-запроса()

Возвращайте фрагменты XML (не скалярные значения) из документа с помощью выражений XPath.

Извлечение фрагментов XML:

cursor.execute("""
    SELECT 
        OrderID,
        OrderXML.query('/Order/Items') AS ItemsXML
    FROM #XMLOrders
    WHERE OrderID = %(id)s
""", {"id": 1})

row = cursor.fetchone()
# row.ItemsXML is an XML string
items_xml = row.ItemsXML
print(items_xml)

Метод XML exist()

Проверьте, совпадает ли выражение XPath с какими-либо узлами документа, возвращая 1 для истины и 0 для ложного.

Проверьте, совпадает ли XPath:

cursor.execute("""
    SELECT OrderID, OrderXML
    FROM #XMLOrders
    WHERE OrderXML.exist('/Order/Items/Item[@ProductID="A1"]') = 1
""")

for row in cursor:
    print(f"Order {row.OrderID} contains product A1")

Метод XML nodes() (разбиение на строки)

Преобразуйте вложенные XML-элементы в реляционный набор строк с помощью CROSS APPLY оператора и nodes() метода.

Преобразование XML в реляционный формат:

cursor.execute("""
    SELECT 
        o.OrderID,
        Items.Item.value('@ProductID', 'VARCHAR(10)') AS ProductID,
        Items.Item.value('@Quantity', 'INT') AS Quantity,
        Items.Item.value('@Price', 'DECIMAL(10,2)') AS Price
    FROM #XMLOrders o
    CROSS APPLY o.OrderXML.nodes('/Order/Items/Item') AS Items(Item)
    WHERE o.OrderID = %(id)s
""", {"id": 1})

for row in cursor:
    print(f"Product {row.ProductID}: {row.Quantity} x ${row.Price}")

Изменение XML-данных

Используйте XML.modify() в выражениях XQuery DML для обновления XML-содержимого на месте.

XML modify() со вставкой

Добавляйте новые элементы в XML-документ с помощью метода modify() и операции insert в XQuery.

cursor.execute("""
    UPDATE #XMLOrders
    SET OrderXML.modify('
        insert <Item ProductID="E5" Quantity="1" Price="59.99"/>
        into (/Order/Items)[1]
    ')
    WHERE OrderID = %(id)s
""", {"id": 1})
conn.commit()

XML modify() с удалением

Удалить элементы или узлы из документа XML с помощью метода modify() с использованием операции delete в XQuery.

cursor.execute("""
    UPDATE #XMLOrders
    SET OrderXML.modify('
        delete /Order/Items/Item[@ProductID="A1"]
    ')
    WHERE OrderID = %(id)s
""", {"id": 1})
conn.commit()

XML modify() с заменой

Обновление значений атрибутов или текста элементов в XML-документе с помощью метода modify() и операции replace value of в XQuery.

cursor.execute("""
    UPDATE #XMLOrders
    SET OrderXML.modify('
        replace value of (/Order/Items/Item[@ProductID="B2"]/@Price)[1]
        with 54.99
    ')
    WHERE OrderID = %(id)s
""", {"id": 1})
conn.commit()

ДЛЯ XML-запросов

Добавьте FOR XML к любому запросу для возврата результатов в виде одной XML-строки.

ДЛЯ XML RAW

Генерируйте XML, где каждая строка становится простым элементом со столбцами в качестве атрибутов.

cursor.execute("""
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = %(cat)s
    FOR XML RAW('Product'), ROOT('Products')
""", {"cat": 1})

xml_result = cursor.fetchval()
print(xml_result)
# <Products><Product ProductID="1" Name="..." ListPrice="..."/></Products>

ДЛЯ XML AUTO

Генерируйте XML с вложенной структурой, которая автоматически отражает иерархию join в вашем запросе.

cursor.execute("""
    SELECT sc.Name AS SubcategoryName, p.Name AS ProductName, p.ListPrice
    FROM Production.ProductSubcategory sc
    JOIN Production.Product p ON sc.ProductSubcategoryID = p.ProductSubcategoryID
    WHERE sc.ProductSubcategoryID = %(cat)s
    FOR XML AUTO, ROOT('Catalog')
""", {"cat": 1})

xml_result = cursor.fetchval()
# Nested XML structure based on join hierarchy

FOR XML PATH

Создавайте пользовательские XML-структуры с помощью явных псевдонимов столбцов и подзапросов, чтобы управлять вложенностью и именами элементов.

Большинство контроля над структурой XML:

cursor.execute("""
    SELECT 
        sc.ProductSubcategoryID AS '@ID',
        sc.Name AS 'Name',
        (
            SELECT p.ProductID AS '@ID',
                   p.Name AS 'Name',
                   p.ListPrice AS 'Price'
            FROM Production.Product p
            WHERE p.ProductSubcategoryID = sc.ProductSubcategoryID
            FOR XML PATH('Product'), TYPE
        ) AS 'Products'
    FROM Production.ProductSubcategory sc
    WHERE sc.ProductSubcategoryID = %(cat)s
    FOR XML PATH('Subcategory'), ROOT('Catalog')
""", {"cat": 1})

xml_result = cursor.fetchval()

Разбор XML в Python

Получите XML в виде строки и проанализируйте его с помощью xml.etree.ElementTree или совместимой библиотеки.

Разбор результатов запроса с помощью ElementTree

Получите XML из базы данных и разберите его в объектное дерево Python с помощью ElementTree, получая доступ к элементам и атрибутам через DOM.

from xml.etree import ElementTree as ET

cursor.execute("""
    SELECT CatalogDescription FROM Production.ProductModel
    WHERE CatalogDescription IS NOT NULL AND ProductModelID = 19
""")

row = cursor.fetchone()
root = ET.fromstring(row.CatalogDescription)

# Navigate XML structure using namespace
ns = {'pd': 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'}
summary = root.find('.//pd:Summary', ns)
if summary is not None:
    # Get all text content
    text = ''.join(summary.itertext()).strip()
    print(f"Summary: {text[:100]}")

Преобразование XML в словарь

Преобразовать структуру XML-дерева в вложенный словарь Python для облегчения программного доступа к вложенным данным.

def xml_to_dict(element):
    """Convert XML element to dictionary."""
    result = {}
    
    # Add attributes
    if element.attrib:
        result['@attributes'] = element.attrib
    
    # Add children
    children = list(element)
    if children:
        child_dict = {}
        for child in children:
            child_result = xml_to_dict(child)
            if child.tag in child_dict:
                # Convert to list if multiple same-named children
                if not isinstance(child_dict[child.tag], list):
                    child_dict[child.tag] = [child_dict[child.tag]]
                child_dict[child.tag].append(child_result)
            else:
                child_dict[child.tag] = child_result
        result.update(child_dict)
    
    # Add text content
    if element.text and element.text.strip():
        result['#text'] = element.text.strip()
    
    return result

# Usage
cursor.execute("""
    SELECT CatalogDescription FROM Production.ProductModel
    WHERE CatalogDescription IS NOT NULL AND ProductModelID = 19
""")
xml_string = cursor.fetchval()
root = ET.fromstring(xml_string)
data = xml_to_dict(root)

Обработка крупных XML-документов

Microsoft SQL может делить большие FOR XML результаты на несколько строк; объединять части перед парсингом.

Получение XML по частям

Когда FOR XML возвращает большие наборы результатов, Microsoft SQL может разбить XML на несколько строк; объединить все части в один документ.

# FOR XML might split large results across rows
cursor.execute("""
    SELECT * FROM LargeTable FOR XML RAW
""")

xml_parts = []
for row in cursor:
    xml_parts.append(row[0])

full_xml = "".join(xml_parts)

Потоковый XML-разбор

Для очень больших XML-документов используйте итеративный разбор для обработки элементов по одному без загрузки всего дерева в память.

from xml.etree import ElementTree as ET
import io

cursor.execute("""
    SELECT Instructions FROM Production.ProductModel
    WHERE Instructions IS NOT NULL AND ProductModelID = 7
""")
xml_string = cursor.fetchval()

# Use iterparse for memory-efficient parsing
xml_stream = io.StringIO(xml_string)
for event, elem in ET.iterparse(xml_stream, events=['end']):
    if elem.tag.endswith('step'):
        # Process step element
        text = ''.join(elem.itertext()).strip()
        if text:
            print(f"Step: {text[:60]}")
        # Free memory
        elem.clear()

XML-индексы

Создавайте индексы в Microsoft SQL для более быстрых XML-запросов:

-- Primary XML index (assumes a table with an XML column)
CREATE PRIMARY XML INDEX PIX_XMLOrders_OrderXML
ON #XMLOrders(OrderXML);

-- Secondary indexes for specific access patterns
CREATE XML INDEX SIX_XMLOrders_Path
ON #XMLOrders(OrderXML)
USING XML INDEX PIX_XMLOrders_OrderXML
FOR PATH;

CREATE XML INDEX SIX_XMLOrders_Value
ON #XMLOrders(OrderXML)
USING XML INDEX PIX_XMLOrders_OrderXML
FOR VALUE;

Работа с пространствами имён

Объявлять префиксы пространства имён в строке в выражениях XQuery с использованием синтаксиса declare namespace .

Запрос в XML с пространствами имён

Добавьте объявления пространств имён в выражения XPath, чтобы сопоставлять элементы в определённом пространстве имён.

xml_with_ns = """
<Order xmlns="http://example.com/orders" 
       xmlns:c="http://example.com/customer">
    <c:Customer Name="John Doe"/>
    <Items>
        <Item ProductID="A1"/>
    </Items>
</Order>
"""

cursor.execute("""
    SELECT OrderXML.value('
        declare namespace o="http://example.com/orders";
        declare namespace c="http://example.com/customer";
        (/o:Order/c:Customer/@Name)[1]
    ', 'NVARCHAR(100)') AS CustomerName
    FROM #XMLOrders
    WHERE OrderID = %(id)s
""", {"id": 1})

Разбор XML с пространственными именами в Python

При разборе XML с пространствами имён в Python объявляйте сопоставления пространств имён в вызове find() и других вызовах обращения к элементам.

from xml.etree import ElementTree as ET

cursor.execute("""
    SELECT TOP 1 
        (SELECT SalesOrderID AS [@OrderID],
                TotalDue AS [@Total],
                CustomerID AS [Customer/@ID]
         FROM Sales.SalesOrderHeader
         WHERE SalesOrderID = 43659
         FOR XML PATH('Order'))
""")
xml_string = cursor.fetchval()
root = ET.fromstring(xml_string)
order_id = root.get('OrderID')
total = root.get('Total')
print(f"Order {order_id}: ${total}")

Лучшие практики

Применяйте эти рекомендации для эффективной работы с XML-данными.

Выберите XML вместо JSON

Решите, использовать ли нативный xml тип Microsoft SQL или хранить JSON, nvarchar исходя из вашей структуры данных и требований к обработке:

Это сравнение помогает решить, использовать ли тип Microsoft SQL xml или хранить JSON в nvarchar:

Функция	XML	JSON
Проверка схемы	Встроенная поддержка	Нет собственной поддержки
Namespaces	Полная поддержка	Поддержка не поддерживается
Атрибуты	Поддерживается	Нет прямого эквивалента
Смешанное содержимое.	Поддерживается	Не поддерживаются
Обработка документов	Лучше	Менее структурированная

Избегайте SELECT *

Не получайте полные XML-документы, если нужны только конкретные значения. Используйте методы XML для извлечения данных на сервере. Такой подход снижает сетевой трафик и позволяет избежать парсинга больших XML-документов на Python.

# Create sample XML table for performance demos
cursor.execute("""
    CREATE TABLE #XMLPerf (
        OrderID INT,
        OrderXML XML
    )
""")
cursor.execute("""
    INSERT INTO #XMLPerf (OrderID, OrderXML) VALUES
    (1, '<Order><Customer Name="Alice"/><Item ProductID="1" Qty="2" Price="29.99"/></Order>'),
    (2, '<Order><Customer Name="Bob"/><Item ProductID="3" Qty="1" Price="49.99"/></Order>')
""")

# Anti-pattern: Downloads entire XML per row
cursor.execute("SELECT * FROM #XMLPerf")

# Better: Extract only the values you need on the server
cursor.execute("""
    SELECT OrderID,
           OrderXML.value('(/Order/Customer/@Name)[1]', 'NVARCHAR(100)') AS Customer
    FROM #XMLPerf
""")

Обрабатывать XML со значением NULL

При запросе XML-данных используйте COALESCE() для указания значений по умолчанию, когда методы XML возвращают NULL.

cursor.execute("""
    SELECT OrderID,
           COALESCE(OrderXML.value('(/Order/@Total)[1]', 'DECIMAL(10,2)'), 0) AS Total
    FROM #XMLPerf
""")

Связанные материалы

• Сопоставления типов данных
• Данные JSON
• Документация по Microsoft SQL XML