Gunakan data XML dengan mssql-python

Microsoft SQL menyediakan tipe data asli xml dengan kemampuan pemrosesan sisi server:

• XQuery untuk mengkueri konten XML.
• Metode XML (value(), query(), , exist()nodes(), ) modify()untuk ekstraksi dan modifikasi.
• Validasi skema XML opsional.
• Indeks XML untuk performa.

Driver mssql-python mengirim dan menerima data XML sebagai string. Semua pemrosesan XML (XQuery, metode XML, validasi skema) dijalankan di sisi Microsoft SQL. Gunakan Python xml.etree.ElementTree atau pustaka serupa untuk penguraian XML sisi klien.

Kapan menggunakan XML vs JSON: Gunakan XML saat Anda memerlukan validasi skema, dukungan namespace, atau konten campuran (teks diselingi dengan elemen). Gunakan JSON (dengan nvarchar kolom dan fungsi JSON Microsoft SQL) saat data Anda berorientasi pada nilai kunci, digunakan oleh API web, atau tidak memerlukan penegakan skema. Sebagian besar aplikasi baru lebih memilih menggunakan JSON, kecuali jika datanya pada dasarnya memang terstruktur sebagai dokumen.

Menyisipkan data XML

Berikan XML sebagai string Python; driver mengirimkannya ke tipe kolom xml bawaan Microsoft SQL.

Sisipkan sebagai string

Langsung sisipkan konten XML sebagai string ke dalam kolom xml menggunakan kueri berparameter.

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

Bangun XML dengan ElementTree

Buat dokumen XML secara terprogram menggunakan pustaka ElementTree Python, lalu konversi ke string untuk disisipkan.

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

Validasi XML sebelum menyisipkan

Validasi sintaks XML di server menggunakan TRY/CATCH SQL dan pengecoran jenis XML untuk menolak dokumen yang salah bentuk sebelum penyimpanan.

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

Membuat kueri data XML

Gunakan metode XML di server untuk mengekstrak nilai tertentu tanpa mengambil dokumen lengkap ke klien.

XML value() metode

Ekstrak nilai atau atribut skalar tunggal dari XML menggunakan value() metode dengan ekspresi XPath.

Ekstrak nilai skalar dari 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() metode

Mengembalikan fragmen XML (bukan nilai skalar) dari dokumen menggunakan ekspresi XPath.

Ekstrak fragmen 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)

Metode exist() XML

Uji apakah ekspresi XPath cocok dengan simpul apa pun dalam dokumen, mengembalikan 1 untuk true dan 0 untuk false.

Periksa apakah XPath cocok:

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

Metode XML nodes() (mengurai menjadi baris)

Konversi elemen XML berlapis menjadi kumpulan baris relasional menggunakan CROSS APPLY operator dan nodes() metode.

Konversi XML ke format relasional:

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

Mengubah data XML

Gunakan XML.modify() dengan ekspresi DML XQuery untuk memperbarui konten XML secara langsung.

XML modify() dengan penyisipan

Tambahkan elemen baru ke dokumen XML menggunakan metode modify() dengan operasi insert milik 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() dengan delete

Hapus elemen atau simpul dari dokumen XML menggunakan metode modify() dengan operasi delete milik XQuery.

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

XML modify() dengan penggantian

Perbarui nilai atribut atau teks elemen dalam dokumen XML menggunakan modify() metode dengan operasi XQuery replace value of .

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

UNTUK kueri XML

Tambahkan FOR XML ke kueri apa pun untuk mengembalikan hasil sebagai string XML tunggal.

UNTUK XML RAW

Hasilkan XML di mana setiap baris menjadi elemen sederhana dengan kolom sebagai atribut.

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>

UNTUK XML AUTO

Hasilkan XML dengan struktur berlapis yang secara otomatis mencerminkan hierarki gabungan dalam kueri Anda.

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

UNTUK JALUR XML

Buat struktur XML kustom menggunakan alias kolom eksplisit dan subkueri untuk mengatur penyarangan dan nama elemen.

Sebagian besar kontrol atas struktur 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()

Mengurai XML di Python

Ambil XML sebagai string dan uraikan dengan xml.etree.ElementTree atau pustaka yang kompatibel.

Mengurai hasil kueri dengan ElementTree

Ambil XML dari database dan uraikan menjadi pohon objek Python menggunakan ElementTree, mengakses elemen dan atribut melalui 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]}")

Mengonversi XML ke kamus

Ubah struktur hierarki XML menjadi kamus Python berlapis untuk akses terprogram yang lebih mudah ke data bertingkat.

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)

Menangani dokumen XML besar

Microsoft SQL mungkin membagi hasil besar FOR XML di beberapa baris; menggabungkan bagian-bagian sebelum menguraikan.

Ambil XML dalam potongan

Saat FOR XML mengembalikan kumpulan hasil yang besar, Microsoft SQL mungkin membagi XML ke beberapa baris; gabungkan semua bagian menjadi satu dokumen.

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

Penguraian aliran XML

Untuk dokumen XML yang sangat besar, gunakan penguraian berulang untuk memproses elemen satu per satu tanpa memuat seluruh pohon ke dalam memori.

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

Indeks XML

Buat indeks di Microsoft SQL untuk kueri XML yang lebih cepat:

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

Bekerja dengan namespace

Deklarasikan prefiks namespace langsung di dalam ekspresi XQuery menggunakan sintaks declare namespace.

Mengkueri XML dengan namespace

Tambahkan deklarasi namespace ke ekspresi XPath Anda untuk mencocokkan elemen di namespace tertentu.

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

Mengurai XML dengan namespace di Python

Saat mengurai XML dengan namespace di Python, tentukan pemetaan namespace dalam find() dan pemanggilan akses elemen lainnya.

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

Praktik terbaik

Terapkan panduan ini untuk bekerja dengan data XML secara efisien.

Pilih XML versus JSON

Putuskan apakah akan menggunakan jenis asli xml Microsoft SQL atau menyimpan JSON berdasarkan nvarchar struktur data dan persyaratan pemrosesan Anda:

Perbandingan ini membantu Anda memutuskan apakah akan menggunakan jenis Microsoft SQL xml atau menyimpan JSON di:nvarchar

Feature	XML	JSON
Skema validasi	Dukungan asli	Tidak ada dukungan bawaan
Namespaces	Dukungan penuh	Tidak ada dukungan
Atribut	Dukungan	Tidak ada yang setara langsung
Konten campuran	Dukungan	Tidak didukung
Pemrosesan dokumen	Lebih baik	Kurang terstruktur

Hindari PILIH *

Jangan mengambil dokumen XML lengkap jika Anda hanya memerlukan nilai tertentu. Gunakan metode XML untuk mengekstrak data di server. Pendekatan ini mengurangi lalu lintas jaringan dan menghindari penguraian dokumen XML besar di 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
""")

Tangani XML NULL

Saat mengkueri data XML, gunakan COALESCE() untuk memberikan nilai default saat metode XML mengembalikan NULL.

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

Konten terkait

• Pemetaan jenis data
• Data JSON
• Dokumentasi Microsoft SQL XML