Använd XML-data med mssql-python

Microsoft SQL tillhandahåller en inbyggd xml datatyp med serverbaserad bearbetning:

• XQuery för att söka XML-innehåll.
• XML-metoder (value(), query(), exist(), , nodes()) modify()för extraktion och modifiering.
• Valfri validering av XML-schema.
• XML-index för prestanda.

mssql-python-drivrutinen skickar och tar emot XML-data som strängar. All XML-bearbetning (XQuery, XML-metoder, schemavalidering) körs på Microsoft SQL-sidan. Använd Python xml.etree.ElementTree eller liknande bibliotek för klient-sida XML-parsing.

När man ska använda XML vs JSON: Använd XML när du behöver schemavalidering, namnrymdsstöd eller blandat innehåll (text blandat med element). Använd JSON (med nvarchar kolumner och Microsoft SQL:s JSON-funktioner) när din data är nyckelvärdesorienterad, konsumeras av webb-API:er eller inte behöver schema-övervakning. De flesta nya applikationer föredrar JSON om inte datan är i grunden dokumentstrukturerad.

Infoga XML-data

Skicka XML som en Python-sträng; drivrutinen skickar det till Microsoft SQL:s inbyggda xml-kolumntyp.

Infoga som sträng

Infoga XML-innehåll direkt som en sträng i en xml-kolumn med hjälp av parameteriserade frågor.

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

Bygg XML med ElementTree

Konstruera XML-dokument programmatiskt med Python:s ElementTree-bibliotek och konvertera sedan till en sträng för insättning.

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

Validera XML innan insättning

Validera XML-syntaxen på servern med SQL:s TRY/CATCH och XML-typcasting för att avvisa felaktigt formade dokument innan lagring.

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

Sök XML-data

Använd XML-metoder på servern för att extrahera specifika värden utan att hämta hela dokument till klienten.

XML-värde()-metoden

Extrahera enskilda skalärvärden eller attribut från XML med metoden value() med XPath-uttryck.

Extrahera skalära värden från 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})")

XML-query()-metoden

Returnera XML-fragment (inte skalärvärden) från dokumentet med XPath-uttryck.

Extrahera XML-fragment:

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

Testa om ett XPath-uttryck matchar några noder i dokumentet, och returnera 1 för sant och 0 för falskt.

Kolla om XPath matchar:

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 nodes() metod (strimla till rader)

Konvertera nästlade XML-element till en relationell raduppsättning med operatorn CROSS APPLY och nodes() metoden.

Konvertera XML till relationsformat:

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

Ändra XML-data

Använd XML.modify() med XQuery DML-uttryck för att uppdatera XML-innehåll som finns på plats.

XML modify() med hjälp av insert

Lägg till nya element i ett XML-dokument med metoden modify() med XQuerys insert operation.

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() med hjälp av delete

Ta bort element eller noder från ett XML-dokument med metoden modify() med 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() med ersätta

Uppdatera attributvärden eller elementtext i ett XML-dokument med metoden modify() med 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-frågor

Lägg till FOR XML på vilken fråga som helst för att returnera resultat som en enda XML-sträng.

FÖR XML RAW

Generera XML där varje rad blir ett enkelt element med kolumner som attribut.

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

Generera XML med nästlad struktur som automatiskt speglar join-hierarkin i din fråga.

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

FÖR XML-SÖKVÄG

Skapa anpassade XML-strukturer med explicita kolumnalias och underfrågor för att styra kapsling och elementnamn.

Mest kontroll över XML-strukturen:

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 i Python

Hämta XML som en sträng och parsa den med xml.etree.ElementTree ett kompatibelt bibliotek.

Parsa frågeresultat med ElementTree

Hämta XML från databasen och parsa det till ett Python-objektträd med ElementTree, och åtkomst till element och attribut via DOM:en.

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

Konvertera XML till ordbok

Omvandla en XML-trädstruktur till en nästlad Python-ordbok för enklare programmatisk åtkomst till nästlad data.

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)

Hantera stora XML-dokument

Microsoft SQL kan dela upp stora FOR XML resultat över flera rader; koppla ihop delarna innan parsning.

Hämta XML i delar

När FOR XML stora resultatuppsättningar returneras kan Microsoft SQL dela upp XML:en över flera rader; kombinera alla delar i ett enda dokument.

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

Ström-XML-parsning

För mycket stora XML-dokument, använd iterativ parsning för att bearbeta elementen ett i taget utan att ladda hela trädet i minnet.

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

Skapa index i Microsoft SQL för snabbare XML-frågor:

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

Arbete med namnrymder

Deklarera namnrymdsprefix direkt i XQuery-uttryck med syntaxen declare namespace.

Sök XML med namnrymder

Lägg till namnrymsdeklarationer i dina XPath-uttryck för att matcha element i ett specifikt namnrymd.

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

Tolka XML med namnrymder i Python

När du parsar XML med namnrymder i Python, deklarera namnrymdsmappningarna i dina find() och andra elementåtkomstanrop.

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

Metodtips

Tillämpa dessa riktlinjer för att arbeta effektivt med XML-data.

Välj XML istället för JSON

Bestäm om du vill använda Microsoft SQL:s inbyggda xml typ eller lagra JSON baserat nvarchar på dina datastruktur- och bearbetningsbehov:

Denna jämförelse hjälper dig att avgöra om du ska använda Microsoft SQL:s xml typ eller lagra JSON invarchar:

Feature	XML	JSON
Schemavalidering	Internt stöd	Inget internt stöd
Namnområden	Fullständigt stöd	Inget stöd
Attributes	Stöds	Ingen direkt motsvarighet
Blandat innehåll	Stöds	Stöds ej
Dokumentbearbetning	Bättre	Mindre strukturerat

Undvik SELECT *

Hämta inte hela XML-dokument när du bara behöver specifika värden. Använd XML-metoder för att extrahera data på servern. Denna metod minskar nätverkstrafiken och undviker att tolka stora XML-dokument i 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
""")

Hantera NULL-XML

När du frågar XML-data, använd COALESCE() för att ange standardvärden när XML-metoder returnerar NULL.

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

Relaterat innehåll

• Datatypsmappningar
• JSON-data
• Microsoft SQL XML-dokumentation