Gebruik XML-data met mssql-python

Microsoft SQL biedt een native xml datatype met server-side verwerkingsmogelijkheden:

• XQuery voor het opvragen van XML-inhoud.
• XML-methoden (value(), query(), exist(), , nodes(), ) modify()voor extractie en wijziging.
• Optionele XML-schemavalidatie.
• XML-indexen voor prestaties.

De mssql-python-driver verzendt en ontvangt XML-gegevens als strings. Alle XML-verwerking (XQuery, XML-methoden, schemavalidatie) wordt uitgevoerd aan de Microsoft SQL-zijde. Gebruik Python's xml.etree.ElementTree of vergelijkbare bibliotheken voor client-side XML-parsing.

Wanneer XML gebruiken versus JSON: Gebruik XML wanneer je schemavalidatie, namespace-ondersteuning of gemengde inhoud nodig hebt (tekst afgewisseld met elementen). Gebruik JSON (met nvarchar kolommen en de JSON-functies van Microsoft SQL) wanneer je data key-value-gericht is, wordt gebruikt door web-API's, of geen schema-handhaving nodig heeft. De meeste nieuwe applicaties geven de voorkeur aan JSON, tenzij de data van nature documentgestructureerd is.

XML-gegevens invoegen

Geef XML door als een Python-string; de driver stuurt het naar het systeemeigen kolomtype xml van Microsoft SQL.

Voeg in als tekenreeks

Voeg XML-inhoud direct in als string in een xml-kolom met behulp van geparametriseerde queries.

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

Bouw XML met ElementTree

Bouw XML-documenten programmatisch met behulp van Python's ElementTree-bibliotheek en converteer vervolgens naar een string voor invoeging.

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

Valideer XML vóór invoeging

Valideer de XML-syntaxis op de server met behulp van SQL's TRY/CATCH en XML-type casting om misvormde documenten te weigeren vóór opslag.

# 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-gegevens opvragen

Gebruik XML-methoden op de server om specifieke waarden te extraheren zonder volledige documenten naar de client te halen.

XML value()-methode

Extraheer enkele scalaire waarden of attributen uit XML met behulp van de value() methode met XPath-expressies.

Haal scalaire waarden uit 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 query()-methode

Geef XML-fragmenten (geen scalaire waarden) terug uit het document met XPath-expressies.

XML-fragmenten extraheren:

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

Test of een XPath-expressie overeenkomt met nodes in het document, waarbij 1 als waar en 0 als onwaar wordt teruggegeven.

Controleer of XPath overeenkomt:

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-methode nodes() (omzetten naar rijen)

Converteer geneste XML-elementen naar een relationele rijset met behulp van de CROSS APPLY operator en nodes() methode.

Converteer XML naar relationeel formaat:

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-gegevens wijzigen

Gebruik XML.modify() met XQuery DML-expressies om XML-inhoud ter plekke bij te werken.

XML modify() met de opdracht insert

Voeg nieuwe elementen toe aan een XML-document met de modify() methode met XQuery's insert operatie.

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() met behulp van delete

Verwijder elementen of knooppunten uit een XML-document met de modify() methode met XQuery's delete operatie.

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

XML modify() en replace

Werk attribuutwaarden of elementtekst bij in een XML-document met de modify() methode met XQuery's replace value of operatie.

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

FOR XML-queries

FOR XML Voeg aan elke query toe om resultaten als één enkele XML-string terug te geven.

VOOR XML RAW

Genereer XML waarbij elke rij een eenvoudig element wordt met kolommen als attributen.

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>

VOOR XML AUTO

Genereer XML met een geneste structuur die automatisch de join-hiërarchie in je query weerspiegelt.

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

VOOR XML-PAD

Bouw aangepaste XML-structuren met expliciete kolomaliasen en subqueries om nesting en elementnamen te beheersen.

Meeste controle over de XML-structuur:

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

Haal XML op als een string en parse deze met xml.etree.ElementTree of een compatibele bibliotheek.

Parse queryresultaten met ElementTree

Haal XML uit de database en parsen het in een Python-objectboom met behulp van ElementTree, waarbij je elementen en attributen via de DOM benadert.

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 omzetten naar woordenboek

Transformeer een XML-boomstructuur naar een genest Python-woordenboek voor eenvoudigere programmatische toegang tot geneste data.

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)

Omgaan met grote XML-documenten

Microsoft SQL kan grote FOR XML resultaten over meerdere rijen splitsen; de onderdelen aanvoegen voordat je pars.

XML in delen ophalen

Wanneer FOR XML grote resultatensets worden teruggegeven, kan Microsoft SQL de XML over meerdere rijen splitsen; alle onderdelen worden gecombineerd tot één document.

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

Stream XML-parsing

Voor zeer grote XML-documenten gebruik je iteratieve parsing om elementen één voor één te verwerken zonder de hele boom in het geheugen te laden.

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

Maak indexen aan in Microsoft SQL voor snellere XML-query's:

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

Werk met naamruimtes

Declareer naamruimte-prefixen inline in XQuery-expressies met behulp van de syntaxis declare namespace .

XML opvragen met naamruimtes

Voeg namespace-declaraties toe aan je XPath-expressies om elementen in een specifieke namespace te matchen.

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 met naamruimten parseren in Python

Bij het parsen van XML met naamruimten in Python definieer je de naamruimtetoewijzingen in je find() en andere aanroepen voor elementtoegang.

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

Beste praktijken

Pas deze richtlijnen toe om efficiënt met XML-data te werken.

Kies XML versus JSON

Bepaal of je het native xml type van Microsoft SQL gebruikt of JSON opslaat op nvarchar basis van je datastructuur en verwerkingsbehoeften:

Deze vergelijking helpt je beslissen of je het xml type van Microsoft SQL gebruikt of JSON opslaat in nvarchar:

Vermijd SELECT *

Haal geen volledige XML-documenten op als je alleen specifieke waarden nodig hebt. Gebruik XML-methoden om data op de server te extraheren. Deze aanpak vermindert het netwerkverkeer en voorkomt het parsen van grote XML-documenten in 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
""")

NULL XML verwerken

Bij het opvragen van XML-gegevens wordt COALESCE() gebruik om standaardwaarden te geven wanneer XML-methoden NULL teruggeven.

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

Verwante onderwerpen

• Gegevenstypetoewijzingen
• JSON-gegevens
• Microsoft SQL XML-documentatie