Utilize dados XML com mssql-python

O Microsoft SQL fornece um tipo de dado nativo xml com capacidades de processamento do lado do servidor:

• XQuery para consultar conteúdo XML.
• Métodos XML (value(), query(), exist(), nodes(), modify()) para extração e modificação.
• Validação opcional do esquema XML.
• Índices XML para desempenho.

O driver mssql-python envia e recebe dados XML como strings. Todo o processamento XML (XQuery, métodos XML, validação de esquema) é executado do lado Microsoft SQL. Use Python xml.etree.ElementTree ou bibliotecas semelhantes para análise sintática XML do lado do cliente.

Quando usar XML vs JSON: Use XML quando precisar de validação de esquema, suporte a namespace ou conteúdo misto (texto entrelaçado com elementos). Utilize JSON (com colunas nvarchar e as funções JSON do Microsoft SQL) quando os seus dados estiverem estruturados em pares chave-valor, forem utilizados por APIs Web ou não necessitarem de imposição de esquema. A maioria das novas aplicações prefere JSON, a menos que os dados estejam inerentemente estruturados em documentos.

Inserir dados XML

Passe XML como uma string Python; o driver envia-o para o tipo de coluna XML nativa do Microsoft SQL.

Inserir como cadeia de caracteres

Insira diretamente conteúdo XML como uma string numa coluna 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()

Construir XML com ElementTree

Constrói documentos XML programaticamente usando a biblioteca ElementTree do Python, depois converte para uma string para inserção.

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 inserir

Valide a sintaxe XML no servidor usando o TRY/CATCH e o casting de tipos XML do SQL para rejeitar documentos malformados antes do armazenamento.

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

Consultar dados XML

Use métodos XML no servidor para extrair valores específicos sem ter de buscar documentos completos para o cliente.

Método value() de XML

Extrair valores ou atributos escalares únicos do XML usando o value() método com expressões XPath.

Extrair valores escalares do 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 XML query()

Devolve fragmentos XML (e não valores escalares) do documento utilizando expressões XPath.

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

Teste se uma expressão XPath corresponde a algum nó no documento, retornando 1 para verdadeiro e 0 para falso.

Verifica se o XPath corresponde:

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 XML nodes() (fragmentação em linhas)

Converter elementos XML aninhados num conjunto relacional de linhas utilizando o operador CROSS APPLY e o método nodes().

Converter XML para 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 dados XML

Utilize XML.modify() com expressões XQuery DML para atualizar o conteúdo XML diretamente.

XML modify() com inserção

Adicione novos elementos a um documento XML utilizando o método modify() com a operação insert do 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() com eliminação

Remova elementos ou nós de um documento XML utilizando o método modify() com a operação delete do XQuery.

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

XML modify() com substituição

Atualizar valores de atributos ou o texto de um elemento num documento XML utilizando o método modify() com a operação replace value of do 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

Adicione FOR XML a qualquer consulta para devolver resultados como uma única cadeia XML.

FOR XML RAW

Gerar XML onde cada linha se torne um elemento simples com colunas 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

Gera XML com estrutura aninhada que reflita automaticamente a hierarquia de junção na tua 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 O CAMINHO XML

Construir estruturas XML personalizadas usando aliases de colunas explícitos e subconsultas para controlar o aninhamento e os nomes dos elementos.

Maior controlo sobre a estrutura 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()

Análise XML em Python

Recupere XML como uma string e analise-a com xml.etree.ElementTree ou com uma biblioteca compatível.

Análise dos resultados das consultas com o ElementTree

Buscar XML da base de dados e analisá-lo numa árvore de objetos Python usando ElementTree, acedendo a elementos e atributos através do 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]}")

Converter XML em dicionário

Transforme uma estrutura de árvore XML num dicionário Python aninhado para facilitar o acesso programático a dados aninhados.

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)

Lidar com documentos XML de grande porte

O Microsoft SQL pode dividir resultados grandes FOR XML em várias linhas; concatene as partes antes da análise sintética.

Buscar XML em blocos

Quando FOR XML retorna grandes conjuntos de resultados, o Microsoft SQL pode dividir o XML em várias linhas; juntar todas as partes num único 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álise sintática XML em fluxo

Para documentos XML muito grandes, use análise iterativa para processar os elementos um de cada vez, sem carregar toda a árvore na memória.

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

Crie índices no Microsoft SQL para consultas XML mais 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;

Trabalho com namespaces

Declare os prefixos do espaço de nomes diretamente nas expressões XQuery utilizando a sintaxe declare namespace.

Consultar XML com espaços de nomes

Adicione declarações de namespace às suas expressões XPath para corresponder a elementos num namespace 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})

Análise de XML com namespaces em Python

Ao analisar XML com namespaces em Python, declare os mapeamentos de namespace nas suas find() e outras chamadas de acesso 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}")

Melhores práticas

Aplique estas diretrizes para trabalhar com dados XML de forma eficiente.

Escolha XML versus JSON

Decida se deve usar o tipo nativo xml do Microsoft SQL ou armazenar JSON nvarchar com base na sua estrutura de dados e necessidades de processamento:

Esta comparação ajuda-o a decidir se deve usar o tipo de xml Microsoft SQL ou armazenar JSON emnvarchar:

Evitar SELECT *

Não recupere documentos XML completos quando só precisa de valores específicos. Use métodos XML para extrair dados no servidor. Esta abordagem reduz o tráfego de rede e evita analisar documentos XML grandes em 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
""")

Processar XML nulo

Ao consultar dados XML, use COALESCE() para fornecer valores predefinidos quando os métodos XML retornam NULL.

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

Conteúdo relacionado

• Mapeamentos de tipo de dados
• Dados JSON
• Documentação Microsoft SQL XML