Verwendung von XML-Daten mit mssql-python

Microsoft SQL bietet einen nativen xml Datentyp mit serverseitigen Verarbeitungsfähigkeiten:

• XQuery zum Abfragen von XML-Inhalten.
• XML-Methoden (value(), query(), exist(), nodes(), ) modify()für Extraktion und Modifikation.
• Optionale XML-Schema-Validierung.
• XML-Indizes für die Performance.

Der mssql-python-Treiber sendet und empfängt XML-Daten als Strings. Alle XML-Verarbeitung (XQuery, XML-Methoden, Schema-Validierung) läuft auf der Microsoft-SQL-Seite. Verwenden Sie Python oder ähnliche Bibliotheken xml.etree.ElementTree für clientseitiges XML-Parsing.

Wann XML vs. JSON verwendet werden sollte: Verwenden Sie XML, wenn Sie eine Schema-Validierung, Unterstützung für Namensräume oder gemischte Inhalte (Text mit Elementen durchsetzt) benötigen. Verwenden Sie JSON (mit nvarchar Spalten und den JSON-Funktionen von Microsoft SQL), wenn Ihre Daten schlüsselwertorientiert sind, von Web-APIs genutzt werden oder keine Schema-Durchsetzung benötigen. Die meisten neuen Anwendungen bevorzugen JSON, es sei denn, die Daten sind von Natur aus dokumentenstrukturiert.

XML-Daten einfügen

XML als Python-String übergeben; der Treiber sendet es an den nativen XML-Spaltentyp von Microsoft SQL.

Als Zeichenfolge einfügen

Fügen Sie XML-Inhalte direkt als Zeichenkette in eine XML-Spalte mit parametrisierten Abfragen ein.

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 mit ElementTree bauen

Erstellen Sie XML-Dokumente programmatisch mit der ElementTree-Bibliothek von Python und konvertieren Sie dann in eine Zeichenkette zur Einfügung.

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 vor dem Einfügen validieren

Validiere die XML-Syntax auf dem Server mit SQLs TRY/CATCH und XML-Typcasting, um fehlgeformte Dokumente vor der Speicherung abzulehnen.

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

Abfrage von XML-Daten

Verwenden Sie XML-Methoden auf dem Server, um bestimmte Werte zu extrahieren, ohne vollständige Dokumente zum Client abzurufen.

XML value()-Methode

Einzelne skalare Werte oder Attribute aus XML mit der value() Methode mit XPath-Ausdrücken extrahieren.

Skalare Werte aus XML extrahieren:

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

Geben Sie XML-Fragmente (keine skalaren Werte) aus dem Dokument mit XPath-Ausdrücken zurück.

XML-Fragmente extrahieren:

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

Testen Sie, ob ein XPath-Ausdruck mit irgendwelchen Knoten im Dokument übereinstimmt, wobei 1 als wahr und 0 als falsch zurückgegeben wird.

Prüfe, ob XPath passt:

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() (in Zeilen aufteilen)

Konvertieren Sie verschachtelte XML-Elemente mithilfe des Operators CROSS APPLY und der Methode nodes() in einen relationalen Rowset.

Konvertiere XML in ein relationales Format:

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-Daten bearbeiten

Verwenden Sie XML.modify() mit XQuery-DML-Ausdrücken, um XML-Inhalte direkt zu aktualisieren.

XML modify() mit insert

Fügen Sie einem XML-Dokument mit der modify() Methode mit XQuerys insert Operation neue Elemente hinzu.

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

Entfernen Sie Elemente oder Knoten aus einem XML-Dokument mit der modify() Methode mit XQuerys delete Operation.

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

XML modify() mit replace

Aktualisieren Sie Attributwerte oder Elementtexte in einem XML-Dokument mit der modify() Methode mit XQuerys replace value of Operation.

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

Fügen Sie FOR XML an jede Abfrage an, um die Ergebnisse als einzelne XML-Zeichenfolge zurückzugeben.

FÜR XML RAW

Generiere XML, bei dem jede Zeile zu einem einfachen Element mit Spalten als Attributen wird.

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>

FÜR XML AUTO

Generiere XML mit verschachtelter Struktur, die automatisch die Join-Hierarchie in deiner Abfrage widerspiegelt.

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

Erstellen Sie benutzerdefinierte XML-Strukturen mit expliziten Spaltenaliasen und Unterabfragen, um Verschachtelung und Elementnamen zu steuern.

Die meiste Kontrolle über die XML-Struktur:

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

XML als String abrufen und mit xml.etree.ElementTree einer kompatiblen Bibliothek parsen.

Abfrageergebnisse mit ElementTree analysieren

Rufe XML aus der Datenbank ab und wandle es mit ElementTree in einen Python-Objektbaum um, wobei über das DOM auf Elemente und Attribute zugegriffen wird.

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 in ein Wörterbuch umwandeln

Transformieren Sie eine XML-Baumstruktur in ein verschachteltes Python-Wörterbuch, um programmatischen Zugriff auf verschachtelte Daten zu erleichtern.

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)

Handhabung großer XML-Dokumente

Microsoft SQL könnte große FOR XML Ergebnisse auf mehrere Zeilen aufteilen; die Teile vor dem Parsen verketten.

XML in Blöcken abrufen

Wenn FOR XML große Ergebnissätze zurückgegeben werden, könnte Microsoft SQL das XML auf mehrere Zeilen aufteilen; alle Teile zu einem einzigen Dokument zusammenfassen.

# 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

Für sehr große XML-Dokumente verwenden Sie iteratives Parsing, um Elemente einzeln zu verarbeiten, ohne den gesamten Baum in den Speicher zu 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-Indizes

Erstellen Sie Indizes in Microsoft SQL für schnellere XML-Abfragen:

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

Arbeit mit Namensräumen

Deklarieren Sie Namensraumpräfixe direkt in XQuery-Ausdrücken mit der Syntax declare namespace.

Abfrage von XML mit Namensräumen

Füge Namespace-Deklarationen zu deinen XPath-Ausdrücken hinzu, um Elemente in einem bestimmten Namespace abzustimmen.

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 mit Namespaces in Python analysieren

Beim Parsen von XML mit Namespaces in Python deklarieren Sie die Namespace-Mappings in Ihren find() und anderen Element-Zugriffsaufrufen.

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

Bewährte Methoden

Wenden Sie diese Richtlinien an, um effizient mit XML-Daten zu arbeiten.

Wähle XML statt JSON

Entscheiden Sie abhängig von Ihren Anforderungen an Datenstruktur und -verarbeitung, ob Sie den nativen xml-Typ von Microsoft SQL verwenden oder JSON in nvarchar speichern:

Dieser Vergleich hilft Ihnen bei der Entscheidung, ob Sie den xml-Typ von Microsoft SQL verwenden oder JSON in nvarchar speichern:

Vermeiden Sie SELECT *

Holen Sie keine vollständigen XML-Dokumente ab, wenn Sie nur bestimmte Werte benötigen. Verwenden Sie XML-Methoden, um Daten auf dem Server zu extrahieren. Dieser Ansatz reduziert den Netzwerkverkehr und vermeidet das Parsen großer XML-Dokumente 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 verarbeiten

Bei der Abfrage von XML-Daten wird verwendet COALESCE() , um Standardwerte anzugeben, wenn XML-Methoden NULL zurückgeben.

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

Verwandte Inhalte

• Datentypzuordnungen
• JSON-Daten
• Microsoft SQL XML-Dokumentation