搭配 mssql-python 使用 XML 資料

Microsoft SQL 提供一種具備伺服器端處理能力的原生xml資料型態：

XQuery 用於查詢 XML 內容。
XML 方法（value()， query()， exist()， nodes()， modify()）用於擷取與修改。
可選的 XML 架構驗證。
XML索引以提升效能。

mssql-python 驅動程式以字串形式傳送與接收 XML 資料。所有 XML 處理（XQuery、XML 方法、結構驗證）皆在 Microsoft SQL 端執行。使用 Python xml.etree.ElementTree 或類似函式庫來進行客戶端 XML 解析。

何時使用 XML 與 JSON： 當你需要結構驗證、命名空間支援或混合內容（文字與元素交錯）時，請使用 XML。當你的資料是鍵值導向、被網頁 API 消耗，或不需要結構強制時，請使用 JSON（包含nvarchar欄位和 Microsoft SQL 的 JSON 函式）。大多數新應用偏好使用 JSON，除非資料本身是文件結構化的。

插入 XML 資料

將 XML 以 Python 字串傳遞;驅動程式會將其傳送到 Microsoft SQL 原生的 XML 欄位類型。

以字串插入

直接將 XML 內容以字串形式插入 XML 欄位，使用參數化查詢。

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

使用 ElementTree 建構 XML

使用 Python 的 ElementTree 函式庫程式化建構 XML 文件，然後轉換成字串插入。

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

插入前請驗證 XML

在伺服器上使用 SQL 的 TRY/CATCH 及 XML 類型鑄造驗證 XML 語法，以在儲存前拒絕格式不佳的文件。

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

查詢 XML 資料

在伺服器端使用 XML 方法擷取特定值，而不必將完整文件匯入用戶端。

XML value（）方法

使用 value() XPath表達式的方法，從XML中提取單一標量值或屬性。

從 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（）方法

使用XPath表達式回傳文件中的XML片段（非純量值）。

擷取 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)

XML exist（）方法

測試 XPath 表達式是否與文件中任何節點相符，回傳 1 表示 true，回傳 0 表示假。

檢查 XPath 是否符合：

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() 方法（分解成資料列）

使用 CROSS APPLY 運算子和 nodes() 方法，將巢狀 XML 元素轉換成關聯式資料列集。

將 XML 轉換為關聯格式：

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

修改 XML 資料

使用 XML.modify() XQuery DML表達式來更新現有的XML內容。

XML modify（）並插入

使用 modify() 方法，透過 XQuery 的 insert 作業，將新元素加入 XML 文件中。

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（）與 delete

利用 modify() XQuery delete 的操作，從 XML 文件中移除元素或節點。

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

XML modify（）並替換

使用 modify() 方法搭配 XQuery 的 replace value of 作業，更新 XML 文件中的屬性值或元素文字內容。

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

用於 XML 查詢

將 FOR XML 附加到任何查詢後，即可將結果以單一 XML 字串形式傳回。

用於 XML RAW

產生 XML，讓每一列都成為一個簡單元素，欄位為屬性。

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

產生帶有巢狀結構的 XML，自動反映查詢中的連接階層。

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

關於 XML 路徑

建立自訂的 XML 結構，使用明確的欄位別名與子查詢來控制巢狀與元素名稱。

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

用 Python 解析 XML

擷取 XML 字串，並用 xml.etree.ElementTree 相容函式庫解析。

使用 ElementTree 解析查詢結果

從資料庫取得 XML，並使用 ElementTree 解析成 Python 物件樹，透過 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]}")

將 XML 轉換為字典

將 XML 樹結構轉換成巢狀的 Python 字典，方便程式化存取巢狀資料。

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)

處理大型 XML 文件

Microsoft SQL 可能會將大量FOR XML結果分割到多列;在解析前先將部分串接起來。

分塊擷取 XML

當FOR XML回傳大量結果集時，Microsoft SQL 可能會將 XML 拆分到多列;將所有部分合併成一份文件。

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

串流 XML 解析

對於非常大的 XML 文件，使用迭代解析來逐一處理元素，而不必將整棵樹載入記憶體。

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 索引

在 Microsoft SQL 建立索引以加快 XML 查詢速度：

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

使用命名空間

在 XQuery 表達式中，使用 declare namespace 語法以內嵌方式宣告命名空間前綴。

查詢帶有命名空間的 XML

在你的 XPath 表達式中加入命名空間宣告，以匹配特定命名空間中的元素。

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

在 Python 中解析具有命名空間的 XML

在 Python 解析 XML 與命名空間時，請在你find()和其他元素的存取呼叫中宣告命名空間映射。

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

最佳做法

應用這些指引以更有效率地處理 XML 資料。

選擇 XML 而非 JSON

根據你的資料結構和處理需求，決定是使用 Microsoft SQL 的原生xml型別還是儲存 JSONnvarchar：

此比較有助於你決定是否使用 Microsoft SQL xml 的類型，或將 JSON 儲存在nvarchar：

Feature	XML	JSON
模式驗證	原生支援	沒有原生支援
命名空間	完整支援	不支援
屬性	Supported	沒有直接對應
混合的內容	Supported	不支援
文件處理	更好了	較不結構化

避免使用 SELECT *

當你只需要特定值時，不要去取出完整的 XML 文件。使用 XML 方法來擷取伺服器上的資料。此方法減少網路流量，並避免在 Python 中解析大型 XML 文件。

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

處理 NULL XML

查詢 XML 資料時，當 XML 方法傳回 NULL 時，請使用 COALESCE() 提供預設值。

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

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-07-22