mssql-pythonでXMLデータを使う

Microsoft SQLは、サーバー側の処理機能を備えたネイティブxmlデータ型を提供します:

XMLコンテンツのクエリにはXQueryがあります。
抽出および修正のためのXMLメソッド(value()、 query()、 exist()、 nodes()、 modify())。
任意のXMLスキーマ検証。
パフォーマンスのためのXMLインデックス。

mssql-pythonドライバーはXMLデータを文字列として送受信します。すべてのXML処理(XQuery、XMLメソッド、スキーマ検証)はMicrosoft SQL側で実行されます。クライアント側のXML解析にはPythonのxml.etree.ElementTreeや類似のライブラリを使いましょう。

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() メソッド

XPath式を用いた value() メソッドを用いて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の式がドキュメント内の任意のノードと一致し、trueの場合は1、falseなら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データを修正する

XQuery DML式で使う XML.modify() を使い、XMLコンテンツをその場で更新してください。

XML modify() と挿入

XQueryのmodify()操作を使って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

XQueryのmodify()操作を用いて、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() を置き換える

XQueryのmodify()操作を使って、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()

FOR XML クエリ

クエリに FOR XML を追加して結果を単一のXML文字列として返します。

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

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を複数の行に分割し、すべての部分を1つのドキュメントに統合することがあります。

# 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データを効率的に扱いましょう。

JSONではなくXMLを選ぶ

データ構造や処理要件に応じて、Microsoft SQLのネイティブxml型を使うか、JSONをnvarcharに保存するかを決めてください。

この比較は、SQLのxml型を使うかMicrosoft JSONをnvarcharに保存するかを判断するのに役立ちます。

特徴	XML	JSON
スキーマの検証	ネイティブサポート	ネイティブサポートなし
名前空間	完全なサポート	サポートなし
属性	サポートされている	直接同等の機能はありません
混合コンテンツ	サポートされている	サポートしていません
ドキュメント処理	より良い	より構造化されていない

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