Použijte XML data s mssql-python

Microsoft SQL poskytuje nativní xml datový typ s možnostmi zpracování na straně serveru:

• XQuery pro dotazování XML obsahu.
• XML metody (value(), query(), exist(), , nodes()) modify()pro extrakci a úpravu.
• Volitelná validace XML schématu.
• XML indexy pro výkon.

Ovladač mssql-python odesílá a přijímá XML data ve formě řetězců. Veškeré XML zpracování (XQuery, XML metody, validace schémat) probíhá na straně Microsoft SQL. Používejte Python xml.etree.ElementTree nebo podobné knihovny pro klientskou XML analýzu.

Kdy používat XML vs JSON: Používejte XML, když potřebujete validaci schématu, podporu jmenných prostorů nebo smíšený obsah (text prokládaný s prvky). Používejte JSON (se sloupci nvarchar a funkcemi JSON v Microsoft SQL Serveru), když jsou vaše data typu klíč–hodnota, využívají je webová API nebo nevyžadují vynucování schématu. Většina nových aplikací preferuje JSON, pokud data nejsou inherentně dokumentovaná.

Vložit data XML

Předejte XML jako Python string; ovladač jej odešle do nativního XML sloupce Microsoft SQL.

Vložit jako řetězec

Přímo vložte XML obsah jako řetězec do sloupce XML pomocí parametrizovaných dotazů.

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

Vytvoření XML pomocí ElementTree

Programově sestavujte XML dokumenty pomocí knihovny ElementTree v Python, poté je převádějte do řetězce pro vložení.

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

Validujte XML před vložením

Ověřte XML syntaxi na serveru pomocí SQL TRY/CATCH a XML typového castingu, abyste před uložením odmítli deformované dokumenty.

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

Dotazování dat XML

Použijte XML metody na serveru k extrakci konkrétních hodnot bez načítání celých dokumentů pro klienta.

Metoda XML value()

Extrahujte jednotlivé skalární hodnoty nebo atributy z XML pomocí value() metody s výrazy XPath.

Extrahujte skalární hodnoty 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 XML dotazu()

Vraťte XML fragmenty (nikoli skalární hodnoty) z dokumentu pomocí výrazů XPath.

Extrahujte XML fragmenty:

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

Otestuje, zda výraz XPath odpovídá nějakým uzlům v dokumentu, a vrací 1 pro pravdu a 0 pro nepravdu.

Zkontrolujte, jestli XPath sedí:

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 XML nodes() (rozložení do řádků)

Převeďte vnořené prvky XML do relační řádkové sady pomocí operátoru CROSS APPLY a metody nodes().

Převod XML do relačního formátu:

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

Upravit XML data

Použijte XML.modify() s výrazy XQuery DML k místní aktualizaci obsahu XML.

XML modify() s insertem

Přidání nových prvků do dokumentu XML pomocí metody modify() s operací insert jazyka 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() s použitím delete

Odstraňte prvky nebo uzly z XML dokumentu pomocí modify() metody s operací 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() s nahrazením

Aktualizujte hodnoty atributů nebo text prvků v dokumentu XML pomocí metody modify() s operací replace value of jazyka 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()

Dotazy pro XML

Přidejte FOR XML k libovolnému dotazu, aby se výsledky vrátily jako jediný řetězec XML.

FOR XML RAW

Generujte XML, kde se každý řádek stává jednoduchým prvkem s atributy ve sloupcích.

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

Generujte XML s vnořenou strukturou, která automaticky odráží hierarchii spojů ve vašem dotazu.

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

Vytvářet vlastní XML struktury pomocí explicitních aliasů sloupců a poddotazů pro řízení vnoření a názvů prvků.

Největší kontrola nad XML strukturou:

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

Načtěte XML jako řetězec a analyzujte ho pomocí xml.etree.ElementTree nebo kompatibilní knihovny.

Výsledky dotazů parsujte pomocí ElementTree

Načíst XML z databáze a parsovat jej do stromu objektů Pythonu pomocí ElementTree s přístupem k prvkům a atributům přes 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]}")

Převod XML do slovníku

Transformujte strukturu XML stromu na vnořený Python slovník pro snadnější programový přístup k vnořeným datům.

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)

Zpracování velkých XML dokumentů

Microsoft SQL může rozdělit velké FOR XML výsledky do více řádků; spojit části před analýzou.

Načítat XML po částech

Když FOR XML vrátí velké sady výsledků, Microsoft SQL může XML rozdělit do více řádků; spojit všechny části do jednoho dokumentu.

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

Průběžné parsování XML

U velmi velkých XML dokumentů použijte iterativní parsování k zpracování prvků jeden po druhém, aniž byste museli načítat celý strom do paměti.

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

Indexy XML

Vytvářejte indexy v Microsoft SQL pro rychlejší XML dotazy:

-- 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;

Práce s jmennými prostory

Deklarujte prefixy jmenného prostoru přímo v XQuery výrazech pomocí syntaxe declare namespace .

Dotaz XML pomocí jmenných prostorů

Přidejte deklarace jmenného prostoru do svých XPath výrazů, aby odpovídaly prvkům v konkrétním jmenném prostoru.

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

Analyzovat XML s obory názvů v Pythonu

Při parsování XML se jmennými prostory v jazyce Python deklarujte mapování jmenných prostorů ve voláních find() a dalších voláních pro přístup k prvkům.

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

Osvědčené postupy

Aplikujte tyto pokyny k efektivní práci s XML daty.

Vyberte XML versus JSON

Rozhodněte se, zda použít nativní xml typ Microsoft SQL, nebo uložit JSON nvarchar podle vašich datových struktur a požadavků na zpracování:

Toto srovnání vám pomůže rozhodnout se, zda použít typ Microsoft SQL xml nebo uložit JSON vnvarchar:

funkce	jazyk XML	JSON
Ověřování schématu	Nativní podpora	Žádná nativní podpora
Namespaces	Úplná podpora	Žádná podpora
Attributes	Podporováno	Žádný přímý ekvivalent
Smíšený obsah:	Podporováno	Nepodporováno
Zpracovávání dokumentu	Lepší	Méně strukturované

Vyhněte se SELECT *

Nezískávejte kompletní XML dokumenty, když potřebujete jen konkrétní hodnoty. Použijte XML metody pro extrakci dat ze serveru. Tento přístup snižuje síťový provoz a vyhýbá se parsování velkých XML dokumentů v Pythonu.

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

Zvládání NULL XML

Při dotazování na data XML použijte COALESCE() k poskytnutí výchozích hodnot, když metody XML vrátí hodnotu NULL.

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

Související obsah

• Mapování datových typů
• JSON data
• Dokumentace Microsoft SQL XML