MSSQL-python ile XML verisi kullanın

Microsoft SQL, sunucu tarafı işleme yeteneklerine sahip yerel xml bir veri tipi sunar:

• XML içeriği sorgulamak için XQuery.
• Çıkarma ve modifikasyon için XML yöntemleri (value(), query(), exist(), nodes()modify(), ) bulunmaktadır.
• İsteğe bağlı XML şema doğrulaması.
• Performans için XML indeksleri.

mssql-python sürücüsü, XML verilerini dizi olarak gönderir ve alır. Tüm XML işleme (XQuery, XML yöntemleri, şema doğrulama) Microsoft SQL tarafında çalışır. İstemci tarafı XML ayrıştırma için Python xml.etree.ElementTree veya benzeri kütüphaneleri kullanın.

XML ve JSON ne zaman kullanılır: Şema doğrulaması, isim alanı desteği veya karışık içerik (metin ile öğelerle karıştırılmış) ihtiyacınız olduğunda XML kullanın. Verileriniz anahtar-değer yapısına sahipse, web API’leri tarafından kullanılıyorsa veya şema zorlaması gerektirmiyorsa JSON’u (nvarchar sütunları ve Microsoft SQL’in JSON işlevleriyle) kullanın. Çoğu yeni uygulama, veriler doğası gereği belge yapılandırılmadığı sürece JSON'u tercih eder.

XML verisi ekle

XML'i bir Python dizisi olarak geçirin; sürücü bunu Microsoft SQL'nin yerel xml sütun tipine gönderir.

Dize olarak ekle

XML içeriğini parametreli sorgular kullanılarak doğrudan bir xml sütununa bir dizi olarak ekleyin.

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 ile XML Oluşturma

XML belgelerini Python'un ElementTree kütüphanesini kullanarak programatik olarak oluşturun, ardından eklemek için bir diziye dönüştürün.

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'i eklemeden önce doğrulama

Depolamadan önce hatalı biçimlendirilmiş belgeleri reddetmek için sunucuda XML sözdizimini, SQL'in TRY/CATCH yapısını ve XML türüne dönüştürmeyi kullanarak doğrulayın.

# 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 verisini sorgulama

Sunucuda XML yöntemlerini kullanarak, tam belgeleri istemciye getirmeden belirli değerleri ayıklayın.

XML value() yöntemi

XPath ifadeleriyle value() yöntemini kullanarak XML'den tekil skaler değerler veya öznitelikler ayıklayın.

XML'den skaler değerler çıkarın:

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() yöntemi

XPath ifadeleri kullanılarak belgeden XML parçalarını (skaler değerler değil) döndürün.

XML parçalarını çıkarın:

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() yöntemi

XPath ifadesinin belgedeki herhangi bir düğümle eşleşip eşleşmediğini test edin; doğru için 1, yanlış için 0 döner.

XPath'ın eşleşip eşleşmediğine bakın:

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() yöntemi (sıralara parçalama)

İç içe XML öğelerini, CROSS APPLY operatörünü ve nodes() yöntemini kullanarak ilişkisel bir satır kümesine dönüştürün.

XML'i ilişkisel formata dönüştürün:

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 verisini değiştir

XML içeriğini yerinde güncellemek için XQuery DML ifadeleriyle kullanın XML.modify() .

insert kullanarak XML modify() ile ekleme

XQuery modify() işlemiyle bir insert yöntemi kullanarak bir XML belgesine yeni öğeler ekleyin.

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() içinde delete kullanımı

XQuery'nin delete işlemini kullanarak, bir XML belgesinden modify() yöntemiyle öğeleri veya düğümleri kaldırın.

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

XML modifiye() ile yerine

XQuery modify() işlemiyle bir XML belgesinde replace value of öznitelik değerlerini veya öğe metnini güncelleyin.

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 sorguları için

Sonuçları tek bir XML dizisi olarak döndürmek için herhangi bir sorguya ekleyin FOR XML .

XML RAW için

Her satırın basit bir öğeye dönüştüğü ve sütunların öznitelikler olarak kullanıldığı XML oluşturun.

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 IÇIN

Sorgunuzdaki birleştirme hiyerarşisini otomatik olarak yansıtan iç içe XML oluşturun.

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 YOLU IÇIN

İç içe alma ve eleman adlarını kontrol etmek için açık sütun aliasları ve alt sorgular kullanarak özel XML yapıları oluşturun.

XML yapısı üzerindeki en fazla kontrol:

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

XML'i Python'da ayrıştırın

XML’yi bir dize olarak alın ve xml.etree.ElementTree veya uyumlu bir kitaplıkla ayrıştırın.

ElementTree ile sorgu sonuçlarını ayrıştır

Veritabanından XML alın ve ElementTree kullanarak Python nesne ağacına ayrıştırın, DOM üzerinden öğelere ve özniteliklere erişin.

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'i sözlüğe dönüştürün

İçiçe alınmış verilere daha kolay programatik erişim için bir XML ağacı yapısını iç içe bir Python sözlüğüne dönüştürün.

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)

Büyük XML belgelerini yönetin

Microsoft SQL büyük FOR XML sonuçları birden fazla satıra bölebilir; ayrıştırmadan önce parçaları birleştirebilir.

XML'i parçalar halinde getir

Büyük FOR XML sonuç setleri döndürdüğünde, Microsoft SQL XML'i birden fazla satıra bölebilir; tüm parçaları tek bir belgede birleştirebilir.

# 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 akış ayrıştırma

Çok büyük XML belgeleri için, tüm ağacı belleğe yüklemeden öğeleri tek tek işlemek için yinelemeli ayrıştırma kullanın.

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 dizinleri

Daha hızlı XML sorguları için Microsoft SQL'de indeksler oluşturun:

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

Isim alanlarıyla çalışma

XQuery ifadelerinde isim alanı öneklerini declare namespace sözdizimini kullanarak satır içi bildirin.

Namespace'lerle XML sorgu

XPath ifadelerinize belirli bir isim alanındaki öğeleri eşleştirmek için isim alanı bildirmeleri ekleyin.

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'da isim aralığı içindeki XML'i ayrıştırın

XML'i Python'da namespace'lerle ayrıştırırken, kendi find() ve diğer eleman erişim çağrılarında namespace eşlemelerini bildirin.

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

En iyi uygulamalar

Bu yönergeleri XML verileriyle verimli çalışmak için uygulayın.

XML yerine JSON seçin

Veri yapınıza ve işleme gereksinimlerinize bağlı olarak, Microsoft SQL’in yerel xml türünü mü kullanacağınıza yoksa JSON’u nvarchar içinde mi depolayacağınıza karar verin:

Bu karşılaştırma, Microsoft SQL'in xml tipini mi kullanacağınızı veya JSON'u şu nvarcharadreste mı saklayacağınızı belirlemenize yardımcı olur:

Özellik	XML	JSON
Şema doğrulaması	Yerel destek	Yerel destek yok
Namespaces	Tam destek	Destek yok
Öznitelikler	Destekleniyor	Doğrudan eşdeğeri yok
Karma içerik	Destekleniyor	Desteklenmiyor
Belge işleme	Daha iyi	Daha az yapılandırılmış

SELECT'ten kaçının *

Sadece belirli değerlere ihtiyacınız olduğunda tam XML belgeleri almayın. Sunucudaki verileri çıkarmak için XML yöntemleri kullanın. Bu yaklaşım, ağ trafiğini azaltır ve Python'da büyük XML belgelerinin ayrıştırılmasından kaçınır.

# 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'i İşle

XML verisi sorguladığında, XML yöntemleri NULL döndürdüğünde varsayılan değerleri sağlamak için kullanın COALESCE() .

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

İlgili içerik

• Veri türü eşlemeleri
• JSON verileri
• Microsoft SQL XML dokümantasyonu