Utilisez des données XML avec mssql-python

Microsoft SQL propose un type de données natif xml avec des capacités de traitement côté serveur :

XQuery pour interroger le contenu XML.
Méthodes XML (value(), query(), exist(), nodes(), modify()) pour l’extraction et la modification.
Validation optionnelle du schéma XML.
Index XML pour améliorer les performances.

Le pilote mssql-python envoie et reçoit des données XML sous forme de chaînes de caractères. Tout le traitement XML (XQuery, méthodes XML, validation de schéma) s’exécute du côté Microsoft SQL. Utilisez des bibliothèques xml.etree.ElementTree Python ou similaires pour l'analyse XML côté client.

Quand utiliser XML vs JSON : Utilisez XML lorsque vous avez besoin de validation de schéma, de prise en charge des espaces de noms ou de contenu mixte (texte entrelacé avec des éléments). Utilisez le JSON (avec nvarchar colonnes et les fonctions JSON de Microsoft SQL) lorsque vos données sont orientées clé-valeur, consommées par des API web ou ne nécessitent pas d'application de schéma. La plupart des nouvelles applications préfèrent le JSON, sauf si les données sont intrinsèquement structurées en documents.

Insérer des données XML

Transmettez du XML sous forme de chaîne de caractères Python ; le pilote l’envoie au type de colonne natif xml de Microsoft SQL.

Insérer comme chaîne

Insérez directement du contenu XML sous forme de chaîne dans une colonne XML à l’aide de requêtes paramétrées.

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

Construire XML avec ElementTree

Construisez des documents XML de manière programmatique en utilisant la bibliothèque ElementTree de Python, puis convertissez en chaîne pour l'insertion.

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

Valider XML avant insertion

Validez la syntaxe XML sur le serveur en utilisant le TRY/CATCH et le casting de types XML de SQL pour rejeter les documents malformés avant le stockage.

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

Interroger les données XML

Utilisez des méthodes XML sur le serveur pour extraire des valeurs spécifiques sans avoir à récupérer des documents complets vers le client.

Méthode XML value()

Extraire des valeurs ou attributs scalaires uniques de XML en utilisant la value() méthode avec des expressions XPath.

Extraire les valeurs scalaires du 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})")

Méthode de requête() XML

Retournez les fragments XML (et non les valeurs scalaires) du document à l’aide d’expressions XPath.

Extraire les fragments 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)

Méthode XML exist()

Testez si une expression XPath correspond à des nœuds du document, en retournant 1 pour vrai et 0 pour faux.

Vérifiez si XPath correspond :

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

Méthode XML nodes() (conversion en lignes)

Convertir des éléments XML imbriqués en un jeu de résultats relationnel à l’aide de l’opérateur CROSS APPLY et de la méthode nodes().

Convertir XML en format relationnel :

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

Modifier les données XML

Utilisez XML.modify() avec des expressions DML XQuery pour mettre à jour directement le contenu XML.

XML modify() avec insert

Ajoutez de nouveaux éléments à un document XML en utilisant la modify() méthode avec l’opération XQuery insert .

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

Supprimez des éléments ou des nœuds d’un document XML en utilisant la modify() méthode avec l’opération 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() avec remplacement

Mettez à jour les valeurs d’attribut ou le texte des éléments dans un document XML en utilisant la modify() méthode avec l’opération XQuery replace value of .

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

Requêtes FOR XML

Ajouter FOR XML à toute requête pour retourner les résultats sous forme d’une seule chaîne XML.

FOR XML RAW

Générez du XML où chaque ligne devient un simple élément avec des colonnes comme attributs.

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>

POUR XML AUTO

Générez du XML avec une structure imbriquée qui reflète automatiquement la hiérarchie de jointure dans votre requête.

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

Construisez des structures XML personnalisées en utilisant des aliases de colonnes explicites et des sous-requêtes pour contrôler l’imbriquage et les noms d’éléments.

Le contrôle sur la structure XML est le plus important :

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

Analyser XML en Python

Récupérez XML sous forme de chaîne et analysez-le avec xml.etree.ElementTree ou une bibliothèque compatible.

Analyse des résultats des requêtes avec ElementTree

Récupérez XML depuis la base de données et analysez-le dans un arbre d’objets Python à l’aide d’ElementTree, en accédant aux éléments et attributs via le 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]}")

Convertir XML en dictionnaire

Transformez une structure en arbre XML en un dictionnaire Python imbriqué pour un accès programmatique plus facile aux données imbriquées.

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)

Gérer de gros documents XML

Microsoft SQL peut répartir de gros FOR XML résultats sur plusieurs lignes ; concaténer les parties avant l’analyse syntaxique.

Récupérer XML par blocs

Lorsque FOR XML renvoie des ensembles de résultats volumineux, Microsoft SQL peut répartir le code XML sur plusieurs lignes de résultat ; combinez toutes les parties en un seul 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)

Analyse XML en flux

Pour les documents XML très volumineux, utilisez l’analyse syntaxique itérative pour traiter les éléments un à la fois sans charger l’arbre entier en mémoire.

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

Index XML

Créez des index en Microsoft SQL pour des requêtes XML plus rapides :

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

Travail avec les espaces de noms

Déclarez les préfixes d’espace de noms en ligne dans les expressions XQuery à l’aide de la syntaxe declare namespace.

Requête XML avec des espaces de noms

Ajoutez des déclarations d’espace de noms à vos expressions XPath pour correspondre aux éléments d’un espace de noms spécifique.

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

Analyser du XML avec espaces de noms en Python

Lorsque vous analysez du XML avec des espaces de noms en Python, déclarez les correspondances d’espaces de noms dans vos appels find() et dans les autres appels d’accès aux éléments.

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

Bonnes pratiques

Appliquez ces directives pour travailler efficacement avec les données XML.

Choisissez XML contre JSON

Décidez si vous utilisez le type natif xml de Microsoft SQL ou si vous stockez du JSON nvarchar en fonction de vos besoins en structure de données et en traitement :

Cette comparaison vous aide à décider si vous utilisez le type de xml Microsoft SQL ou si vous stockez le JSON dans nvarchar:

Fonctionnalité	XML	JSON
Validation du schéma	Prise en charge native	Aucune prise en charge native
Namespaces	Prise en charge complète	Aucune prise en charge
Attributs	Soutenu	Aucun équivalent direct
Contenu mixte	Soutenu	Non pris en charge
Traitement du document	Mieux	Moins structuré

Éviter SELECT *

Ne récupérez pas des documents XML complets quand vous n’avez besoin que de valeurs spécifiques. Utilisez des méthodes XML pour extraire les données sur le serveur. Cette approche réduit le trafic réseau et évite d’analyser de gros documents XML en 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
""")

Gérer un XML nul

Lors de l’interrogation de données XML, utilisez COALESCE() pour fournir des valeurs par défaut lorsque les méthodes XML retournent NULL.

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

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-07-22