Usa dati XML con mssql-python

Microsoft SQL fornisce un tipo di dato nativo xml con capacità di elaborazione lato server:

  • XQuery per interrogare contenuti XML.
  • Metodi XML (value(), query(), exist(), nodes(), modify()) per l'estrazione e la modifica.
  • Validazione opzionale dello schema XML.
  • Indici XML per le prestazioni.

Il driver mssql-python invia e riceve dati XML come stringhe. Tutta l'elaborazione XML (XQuery, metodi XML, validazione dello schema) viene eseguita sul lato Microsoft SQL. Usa Python xml.etree.ElementTree o librerie simili per l'analisi XML lato client.

Quando usare XML vs JSON: Usa XML quando hai bisogno di validazione dello schema, supporto per namespace o contenuti misti (testo intercalato con elementi). Usa JSON (con nvarchar colonne e funzioni JSON di Microsoft SQL) quando i tuoi dati sono orientati a key-value, consumati da API web o non richiedono l'applicazione dello schema. La maggior parte delle nuove applicazioni preferisce JSON, a meno che i dati non siano intrinsecamente strutturati in documenti.

Inserisci dati XML

Passa XML come una stringa Python; il driver lo invia al tipo di colonna xml nativo di Microsoft SQL.

Inserisci come stringa

Inserisci direttamente contenuti XML come stringa in una colonna XML usando query parametrizzate.

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

Compila XML con ElementTree

Costruire documenti XML in modo programmativo usando la libreria ElementTree di Python, poi convertire in una stringa per l'inserimento.

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

Valida XML prima di inserire

Validare la sintassi XML sul server utilizzando TRY/CATCH e XML type casting di SQL per rifiutare documenti malformati prima dell'archiviazione.

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

Consulta dati XML

Usa metodi XML sul server per estrarre valori specifici senza dover scaricare documenti completi al client.

Metodo XML value()

Estrai valori o attributi scalari singoli da XML usando il value() metodo con espressioni XPath.

Estrarre i valori scalari da 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})")

Metodo XML query()

Restituisci frammenti XML (non valori scalari) dal documento usando espressioni XPath.

Estrae frammenti 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)

Metodo XML exist()

Verifica se un'espressione XPath corrisponde a nodi nel documento, restituendo 1 per vero e 0 per falso.

Controlla se XPath corrisponde:

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

Metodo XML nodes() (suddivisione in righe)

Converti gli elementi XML annidati in un set di righe relazionale usando l'operatore CROSS APPLY e il metodo nodes().

Converti XML in formato relazionale:

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

Modifica dati XML

Usa XML.modify() con le espressioni DML di XQuery per aggiornare il contenuto XML in loco.

XML modify() con inserimento

Aggiungi nuovi elementi a un documento XML usando il modify() metodo con l'operazione di 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 modifica() con eliminazione

Rimuovere elementi o nodi da un documento XML utilizzando il modify() metodo con l'operazione di 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() con sostituire

Aggiornare i valori degli attributi o il testo degli elementi in un documento XML utilizzando il modify() metodo con l'operazione di 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()

FOR query XML

Aggiungi FOR XML a qualsiasi query per restituire i risultati come una singola stringa XML.

FOR XML RAW

Genera XML in cui ogni riga diventa un elemento semplice con colonne come attributi.

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

Genera XML con una struttura annidata che rifletta automaticamente la gerarchia di join nella tua query.

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

PER IL PERCORSO XML

Costruire strutture XML personalizzate utilizzando alias espliciti di colonne e sottoquery per controllare il nesting e i nomi degli elementi.

Il maggior controllo sulla struttura 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

Recupera XML come stringa e analizzalo con xml.etree.ElementTree o una libreria compatibile.

Analizza i risultati delle query con ElementTree

Recupera XML dal database e analizzalo in un albero degli oggetti Python usando ElementTree, accedendo a elementi e attributi tramite il 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]}")

Converti XML in dizionario

Trasforma una struttura ad albero XML in un dizionario Python annidato per un accesso programmatico più semplice ai dati annidati.

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)

Gestione di grandi documenti XML

Microsoft SQL potrebbe suddividere i risultati grandi FOR XML su più righe; concatenare le parti prima dell'analisi parsica.

Recupera XML in blocchi

Quando FOR XML restituisce grandi set di risultati, Microsoft SQL può suddividere l'XML su più righe; combinare tutte le parti in un unico documento.

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

Parsing XML in streaming

Per documenti XML molto grandi, si utilizza l'analisi iteratrica per elaborare gli elementi uno alla volta senza caricare l'intero albero in memoria.

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

Indici XML

Crea indici in Microsoft SQL per query XML più rapide:

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

Lavoro con namespace

Dichiarare i prefissi di namespace in linea nelle espressioni XQuery usando la sintassi declare namespace.

Consulta XML con namespace

Aggiungi dichiarazioni di namespace alle tue espressioni XPath per abbinare elementi in uno specifico namespace.

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

Analizzare XML con spazi dei nomi in Python

Quando esegui il parsing di XML con namespace in Python, dichiara le mappature dei namespace nelle chiamate a find() e nelle altre chiamate di accesso agli elementi.

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

Procedure consigliate

Applica queste linee guida per lavorare con dati XML in modo efficiente.

Scegli XML invece che JSON

Decidi se usare il tipo nativo xml di Microsoft SQL o memorizzare JSON nvarchar in base alla struttura dei dati e alle esigenze di elaborazione:

Questo confronto ti aiuta a decidere se usare il tipo di xml Microsoft SQL o memorizzare JSON in nvarchar:

Feature XML JSON
Convalida dello schema Supporto nativo Nessun supporto nativo
Namespaces Supporto completo Nessun supporto
Attributes Supportato Nessun equivalente diretto
Contenuto misto Supportato Non supportato
Documento in fase di elaborazione Meglio Meno strutturato

Evita SELECT *

Non recuperare documenti XML completi quando ti servono solo valori specifici. Usa metodi XML per estrarre i dati dal server. Questo approccio riduce il traffico di rete ed evita di analizzare grandi documenti XML 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
""")

Gestione NULL XML

Quando si interrogano dati XML, si usa COALESCE() per fornire valori predefiniti quando i metodi XML restituiscono NULL.

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

Contenuti correlati

  • Last updated on