Utiliza datos XML con mssql-python

Microsoft SQL proporciona un tipo de dato nativo xml con capacidades de procesamiento en el lado del servidor:

• XQuery para consultar contenido XML.
• Métodos XML (value(), query(), exist(), nodes(), modify()) para extracción y modificación.
• Validación opcional del esquema XML.
• Índices XML para rendimiento.

El controlador mssql-python envía y recibe datos XML como cadenas. Todo el procesamiento XML (XQuery, métodos XML, validación de esquemas) se ejecuta en el lado de Microsoft SQL. Utiliza librerías de Python xml.etree.ElementTree o similares para análisis XML en el lado del cliente.

Cuándo usar XML vs JSON: Usa XML cuando necesites validación de esquemas, soporte para espacios de nombres o contenido mixto (texto entrelazado con elementos). Usa JSON (con nvarchar columnas y las funciones JSON de Microsoft SQL) cuando tus datos estén orientados a clave-valor, estén consumidos por APIs web o no necesiten aplicación de esquemas. La mayoría de las aplicaciones nuevas prefieren JSON a menos que los datos estén inherentemente estructurados en documentos.

Insertar datos XML

Pase el XML como una cadena de Python; el controlador lo envía al tipo de columna xml nativo de Microsoft SQL.

Insertar como cadena

Inserta directamente contenido XML como una cadena en una columna XML usando consultas parametrizadas.

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

Compilar XML con ElementTree

Construye documentos XML de forma programática usando la biblioteca ElementTree de Python, y luego convierte a una cadena para insertarla.

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

Validar XML antes de insertar

Valida la sintaxis XML en el servidor usando el TRY/CATCH y el casting de tipos XML de SQL para rechazar documentos mal formados antes de almacenarlos.

# 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 de datos XML

Utiliza métodos XML en el servidor para extraer valores específicos sin tener que transferir documentos completos al cliente.

Método XML value()

Extrae valores o atributos escalares únicos de XML usando el value() método con expresiones XPath.

Extraer valores escalares de 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étodo de consulta XML

Devuelve fragmentos XML (no valores escalares) del documento usando expresiones XPath.

Extraer fragmentos 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étodo XML exist()

Comprobar si una expresión XPath coincide con algún nodo del documento, devolviendo 1 para verdadero y 0 para falso.

Comprueba si XPath coincide:

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étodo nodes() de XML (descomposición en filas)

Convierte elementos XML anidados en un conjunto relacional de filas mediante el operador CROSS APPLY y el método nodes().

Convertir XML a formato relacional:

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

Modificar datos XML

Use XML.modify() con expresiones DML de XQuery para actualizar directamente el contenido XML.

XML modify() mediante insert

Añadir nuevos elementos a un documento XML usando el método modify() con la operación insert de 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() con eliminación

Elimine elementos o nodos de un documento XML usando el método modify() con la operación delete de 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 reemplazar

Actualizar valores de atributos o el texto de los elementos en un documento XML usando el método modify() con la operación replace value of de 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()

Consultas FOR XML

Añade FOR XML a cualquier consulta para devolver los resultados como una sola cadena XML.

FOR XML RAW

Genera XML donde cada fila se convierta en un elemento simple con columnas como atributos.

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>

PARA XML AUTO

Genera XML con estructura anidada que refleje automáticamente la jerarquía de unión en tu consulta.

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

PARA LA RUTA XML

Construir estructuras XML personalizadas usando alias de columna explícitos y subconsultas para controlar el anidamiento y los nombres de los elementos.

Mayor control sobre la estructura 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()

Analizar XML en Python

Recupera XML como cadena y analiza con xml.etree.ElementTree o con una biblioteca compatible.

Analizar resultados de consultas con ElementTree

Obtén XML de la base de datos y analiza el árbol de objetos en Python usando ElementTree, accediendo a elementos y atributos mediante el 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 a diccionario

Transforma una estructura de árbol XML en un diccionario de Python anidado para facilitar el acceso programático a datos anidados.

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)

Manejar documentos XML grandes

Microsoft SQL puede dividir los resultados grandes FOR XML en varias filas; concatenar las partes antes de analizar.

Recuperar XML por fragmentos

Cuando FOR XML devuelve grandes conjuntos de resultados, Microsoft SQL puede dividir el XML en varias filas; combinar todas las partes en un solo 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)

Análisis de XML en flujo

Para documentos XML muy grandes, utiliza análisis iterativo para procesar los elementos uno a uno sin cargar todo el árbol en 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()

índices XML

Crea índices en Microsoft SQL para consultas XML más rápidas:

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

Trabajar con espacios de nombres

Declara los prefijos de espacio de nombres en línea en las expresiones XQuery usando la declare namespace sintaxis.

Consulta XML con espacios de nombres

Añade declaraciones de espacio de nombres a tus expresiones de XPath para que coincidan con los elementos de un espacio de nombres específico.

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

Analizar XML con espacios de nombres en Python

Al analizar XML con espacios de nombres en Python, declara las asignaciones de espacios de nombres en tus llamadas find() de acceso a elementos y en otras llamadas de acceso a elementos.

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

procedimientos recomendados

Aplica estas directrices para trabajar con datos XML de forma eficiente.

Elige XML frente a JSON

Decide si usar el tipo nativo xml de Microsoft SQL o almacenar JSON nvarchar en función de tus requisitos de estructura de datos y procesamiento:

Esta comparación te ayuda a decidir si usar el tipo de xml Microsoft SQL o almacenar JSON ennvarchar:

Evitar SELECT *

No recuperes documentos XML completos cuando solo necesitas valores específicos. Utiliza métodos XML para extraer datos del servidor. Este enfoque reduce el tráfico de red y evita analizar documentos XML grandes 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
""")

Manejo de NULL XML

Al consultar datos XML, utiliza COALESCE() para proporcionar valores predeterminados cuando los métodos XML devuelven NULL.

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

Contenido relacionado

• Asignaciones de tipos de datos
• Datos JSON
• Documentación de Microsoft SQL XML