Używaj danych XML z mssql-python

Microsoft SQL oferuje natywny xml typ danych z możliwością przetwarzania po stronie serwera:

• XQuery do zapytań o treści XML.
• Metody XML (value(), query(), exist(), , nodes()) modify()do ekstrakcji i modyfikacji.
• Opcjonalna walidacja schematu XML.
• Indeksy XML dotyczące wydajności.

Sterownik mssql-python wysyła i odbiera dane XML w formie ciągów znaków. Całe przetwarzanie XML (XQuery, metody XML, walidacja schematów) odbywa się po stronie Microsoft SQL. Używaj bibliotek Python xml.etree.ElementTree lub podobnych do parsowania XML po stronie klienta.

Kiedy używać XML a kiedy JSON: Używaj XML, gdy potrzebujesz walidacji schematów, wsparcia dla przestrzeni nazw lub mieszanej zawartości (tekst przeplatany elementami). Używaj formatu JSON (z kolumnami nvarchar i funkcjami JSON w Microsoft SQL), gdy Twoje dane mają charakter klucz-wartość, są używane przez internetowe interfejsy API lub nie wymagają wymuszania schematu. Większość nowych aplikacji preferuje JSON, chyba że dane są z natury strukturyzowane dokumentami.

Wstaw dane XML

Przekaż kod XML jako ciąg znaków języka Python; sterownik wysyła go do natywnego typu kolumny xml w Microsoft SQL.

Wstaw jako ciąg znaków

Bezpośrednio wstawiaj zawartość XML jako ciąg znaków do kolumny xml za pomocą parametryzowanych zapytań.

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()

Buduj XML za pomocą ElementTree

Skonstruuj dokumenty XML programatycznie, korzystając z biblioteki ElementTree w Python, a następnie konwertuj na ciąg do wstawienia.

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()

Zweryfikowaj XML przed wstawieniem

Weryfikuj składnię XML na serwerze za pomocą mechanizmu TRY/CATCH i rzutowania na typ XML w SQL, aby odrzucać niepoprawnie sformowane dokumenty przed zapisaniem.

# 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})

Zapytanie o dane XML

Używaj metod XML na serwerze, aby wyodrębnić konkretne wartości bez pobierania pełnych dokumentów do klienta.

Metoda value() języka XML

Wyodrębniaj pojedyncze wartości skalarne lub atrybuty z XML za pomocą value() metody z wyrażeniami XPath.

Wyodrębniaj wartości skalarne z 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})")

Metoda zapytania XML ()

Zwracaj fragmenty XML (nie wartości skalarne) z dokumentu za pomocą wyrażeń XPath.

Wyodrębniaj fragmenty 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)

Metoda XML exist()

Sprawdź, czy wyrażenie XPath pasuje do jakiegokolwiek węzła w dokumencie, zwracając 1 dla prawdziwego i 0 dla false.

Sprawdź, czy XPath pasuje:

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")

Metoda nodes() dla węzłów XML (podział do wierszy)

Przekonwertuj zagnieżdżone elementy XML na relacyjny zestaw wierszy za pomocą operatora CROSS APPLY i metody nodes().

Przekonwertowanie XML do formatu relacyjnego:

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}")

Modyfikacja danych XML

Używaj XML.modify() z wyrażeniami XQuery DML do aktualizacji treści XML na miejscu.

XML modify() z insertem

Dodaj nowe elementy do dokumentu XML za pomocą metody modify() przy użyciu operacji insert języka 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() przy użyciu delete

Usuń elementy lub węzły z dokumentu XML za pomocą modify() metody z operacją XQuery delete .

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

XML modify() z operacją zastępowania

Zaktualizuj wartości atrybutów lub tekst elementów w dokumencie XML za pomocą metody modify() z użyciem operacji replace value of języka 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()

zapytania FOR XML

Dodaj FOR XML do dowolnego zapytania, aby zwrócić wyniki jako pojedynczy ciąg XML.

FOR XML RAW

Wygeneruj XML, gdzie każdy wiersz staje się prostym elementem z kolumnami jako atrybutami.

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>

FOR XML AUTO

Wygeneruj XML o strukturze zagnieżdżonej, która automatycznie odzwierciedla hierarchię złączeń w zapytaniu.

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

DLA ŚCIEŻKI XML

Konstruuj niestandardowe struktury XML z wykorzystaniem jawnych aliasów kolumn i podzapytań do kontroli zagnieżdżania i nazw elementów.

Największa kontrola nad strukturą 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()

Parse XML in Python

Pobierz XML jako ciąg znaków i parsuj go za pomocą xml.etree.ElementTree lub zgodnej biblioteki.

Parsuj wyniki zapytań za pomocą ElementTree

Pobierz XML z bazy danych i przeanalizuj go do drzewa obiektowego Python za pomocą ElementTree, uzyskując dostęp do elementów i atrybutów przez 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]}")

Przekształtaj XML do słownika

Przekształc strukturę drzewa XML w zagnieżdżony słownik Python, aby ułatwić programowanie dostępu do zagnieżdżonych danych.

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)

Obsługa dużych dokumentów XML

Microsoft SQL może dzielić duże FOR XML wyniki na wiele wierszy; łączyć części przed parsowaniem.

Pobieranie XML w kawałkach

Gdy FOR XML zwraca duże zbiory wyników, Microsoft SQL może podzielić XML na wiele wierszy; połączyć wszystkie części w jeden dokument.

# 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)

Strumieniowe parsowanie XML

W przypadku bardzo dużych dokumentów XML stosuj iteracyjne parsowanie, aby przetwarzać elementy pojedynczo bez ładowania całego drzewa do pamięci.

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()

Indeksy XML

Tworz indeksy w Microsoft SQL dla szybszych zapytań 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;

Praca z przestrzeniami nazw

Deklaruj prefiksy przestrzeni nazw bezpośrednio w wyrażeniach XQuery, używając składni declare namespace.

Wykonywanie zapytań do XML z użyciem przestrzeni nazw

Dodaj deklaracje przestrzeni nazw do wyrażeń XPath, aby dopasować elementy w konkretnej przestrzeni nazw.

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})

Analizowanie XML z przestrzeniami nazw w Pythonie

Podczas analizowania XML z użyciem przestrzeni nazw w Pythonie zadeklaruj mapowania przestrzeni nazw w wywołaniach find() oraz innych wywołaniach służących do uzyskiwania dostępu do elementów.

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}")

Najlepsze rozwiązania

Stosuj te wytyczne, aby efektywnie pracować z danymi XML.

Wybierz XML zamiast JSON

Zdecyduj, czy użyć natywnego xml typu Microsoft SQL, czy zapisać JSON, nvarchar w zależności od swojej struktury danych i wymagań dotyczących przetwarzania:

To porównanie pomaga zdecydować, czy użyć typu Microsoft SQLxml, czy przechowywać JSON wnvarchar:

Funkcja	XML	JSON
Weryfikacja schematu	Natywna obsługa	Brak natywnej obsługi
Namespaces	Pełna obsługa	Brak obsługi
Attributes	Wsparte	Brak bezpośredniego odpowiednika
Zawartość mieszana	Wsparte	Niewspierane
Przetwarzanie dokumentów	Lepsze	Mniej uporządkowane

Unikaj SELECT *

Nie pobieraj pełnych dokumentów XML, gdy potrzebujesz tylko konkretnych wartości. Używaj metod XML do wyodrębniania danych z serwera. Takie podejście zmniejsza ruch sieciowy i unika parsowania dużych dokumentów XML w Pythonie.

# 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
""")

Obsługa wartości NULL w XML

Podczas wykonywania zapytań na danych XML używaj elementu COALESCE(), aby podać wartości domyślne, gdy metody XML zwracają NULL.

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

Treści powiązane

• Mapowania typów danych
• Dane JSON
• Dokumentacja Microsoft SQL XML