XML adatok használata mssql-python-nal

A Microsoft SQL natív xml adattípust kínál, szerveroldali feldolgozási képességekkel:

• XQuery XML tartalom lekérdezéséhez.
• XML módszerek (value(), query(), exist(), nodes()modify(), ) a kinyeréshez és módosításhoz.
• Opcionális XML séma ellenőrzés.
• XML indexek a teljesítményhez.

Az mssql-python illezőprogram XML adatokat küld és fogad stringként. Minden XML feldolgozás (XQuery, XML módszerek, séma ellenőrzés) a Microsoft SQL oldalán fut. Használj Python xml.etree.ElementTree vagy hasonló könyvtárakat kliensoldali XML elemzéshez.

Mikor kell használni XML-t és JSON-t: Használj XML-t, ha séma ellenőrzésre, névtér támogatásra vagy vegyes tartalomra (szöveg elemekkel való átvont) szükséged van. Használd a JSON-t (nvarcharoszlopokkal és a Microsoft SQL JSON függvényeivel), ha az adataid kulcsérték-orientáltak, webes API-k használják őket, vagy nem igényel séma betarttatást. A legtöbb új alkalmazás a JSON-t részesíti előnyben, hacsak az adatok nem alapvetően dokumentum-szerkezetűek.

XML adatok beszúrása

Passzold az XML-t Python stringként; a meghajtó elküldi azt a Microsoft SQL natív xml oszloptípusára.

Beszúrás karakterláncként

Közvetlenül illeszts be XML tartalmat stringként egy xml oszlopba paraméterezett lekérdezésekkel.

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

XML építése ElementTree-vel

Készítsd programozott XML dokumentumokat a Python ElementTree könyvtárával, majd konvertálj szöveggránnyá a beillesztéshez.

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 érvényesítése beszúrás előtt

A kiszolgálón ellenőrizze az XML-szintaxist az SQL TRY/CATCH és az XML-típusra alakítás használatával, hogy a rosszul formázott dokumentumokat még tárolás előtt elutasítsa.

# 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 adatok lekérdezése

XML módszereket használj a szerveren, hogy konkrét értékeket nyerj ki anélkül, hogy teljes dokumentumokat kellene lehoznák az ügyfélnek.

XML value() metódus

Egyetlen skaláris értékeket vagy attribútumokat vonjunk ki XML-ből XPath kifejezésekkel rendelkező value() módszerrel.

Skalárértékek kinyerése XML-ből:

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() módszer

XML fragmentumokat (nem skaláris értékeket) térítsünk vissza a dokumentumból XPath kifejezésekkel.

XML fragmentek kinyerése:

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() módszer

Ellenőrzi, hogy egy XPath-kifejezés megfelel-e a dokumentum bármely csomópontjának; igaz esetén 1-et, hamis esetén 0-t ad vissza.

Nézd meg, hogy az XPath egyezik-e:

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() módszer (sorokra bontás)

Átalakítsuk az egymásba ágyazott XML elemeket relációs sorhalmazsá az CROSS APPLY operátor és nodes() a metódus segítségével.

XML átalakítása relációs formátumra:

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 adatok módosítása

Használja a XML.modify() elemet az XML-tartalom helyben történő frissítéséhez XQuery DML-kifejezésekkel.

XML módosítás() beszúrással

Új elemek hozzáadása egy XML-dokumentumhoz a modify() módszerrel, az XQuery insert műveletének használatával.

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 módosítás() törléssel

Elemek vagy csomópontok eltávolítása XML-dokumentumból a modify() metódussal, az XQuery delete műveletének használatával.

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

XML módosítás() helyettesítéssel

Frissítse az attribútumértékeket vagy elemszöveget egy XML dokumentumban az modify() XQuery replace value of műveletével rendelkező módszerrel.

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-lekérdezésekhez

Bármely FOR XML lekérdezéshez csatolhatjuk, hogy egyetlen XML stringként térjen vissza az eredményeket.

XML RAW esetén

Generáljunk XML-t, ahol minden sor egyszerű elemmé válik, oszlopokkal attribútumként.

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 ESETÉN

Generálj XML-t beágyazott struktúrával, amely automatikusan tükrözi a lekérdezésed csatlakozási hierarchiáját.

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

FOR XML PATH

Egyedi XML struktúrákat építs explicit oszlop-aliasokkal és allekérdezésekkel a fészkelés és elemnevek vezérlésére.

A legtöbb XML struktúra feletti kontroll:

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

Az XML elemzése Python-ban

Lekérd az XML-t stringként, és parzáld kompatibilis xml.etree.ElementTree könyvtárral.

Lekérdezési eredmények feldolgozása az ElementTree használatával

Szerezz XML-t az adatbázisból, és az ElementTree segítségével egy Python objektumfára szegezzük, így elemeket és attribútumokat a DOM-on keresztül érsz el.

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 átalakítása szótárrá

Alakítsd át az XML fa struktúrát egy beágyazott Python szótárrá, hogy könnyebb programozott hozzáférés legyen az ágyazott adatokhoz.

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)

Nagy XML dokumentumok kezelése

A Microsoft SQL nagy FOR XML eredményeket több sorra oszthat; összefűzheti az alkatrészeket az elemzés előtt.

XML-t csomagokban szerezz

Amikor FOR XML nagy eredményhalmazokat ad vissza, a Microsoft SQL megoszthatja az XML-t több sorra; egyesítheti az összes részt egyetlen dokumentumba.

# 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 áramlat elemzés

Nagyon nagy méretű XML-dokumentumok esetén használjon iteratív elemzést az elemek egyenkénti feldolgozásához anélkül, hogy a teljes fát betöltené a memóriába.

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

Hozz létre indexeket a Microsoft SQL-ben gyorsabb XML lekérdezésekhez:

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

Munka névterekkel

Adja meg a névtér-előtagokat közvetlenül az XQuery-kifejezésekben a declare namespace szintaxis használatával.

XML lekérdezés névterekkel

Névtér kijelentéseket adj hozzá az XPath kifejezésekhez, hogy illeszkedjenek egy adott névtér elemeihez.

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

A névtér alapú XML elemzése Python-ban

Amikor névterekkel rendelkező XML-t dolgozol fel Pythonban, add meg a névtérleképezéseket a(z) find() és más elemelérési hívásokban.

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

Bevált gyakorlatok

Alkalmazd ezeket az irányelveket, hogy hatékonyan dolgozzon az XML adatokkal.

Válassz XML vagy JSON

Döntsd el, hogy a Microsoft SQL natív xml típusát használod-e vagy tárolod a JSON-t nvarchar az adatszerkezeted és a feldolgozási igények alapján:

Ez az összehasonlítás segít eldönteni, hogy a Microsoft SQL xml típusát használja, vagy JSON-t tárolja-e a nvarcharkövetkező helyen:

Feature	XML	JSON
Sémaellenőrzés	Natív támogatás	Nincs natív támogatás
Namespaces	Teljes körű támogatás	Nincs támogatás
Attributes	Támogatott	Nincs közvetlen egyenértékű
Vegyes tartalom:	Támogatott	Nem támogatott
Dokumentumfeldolgozás	Jobb	Kevésbé strukturált

Kerüld a SELECT * használatát

Ne szerezz teljes XML dokumentumokat, ha csak konkrét értékekre van szükséged. XML módszereket használj az adatok kinyerésére a szerveren. Ez a megközelítés csökkenti a hálózati forgalmat, és elkerüli a nagy XML dokumentumok Python-os elemzését.

# 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 értékű XML kezelése

XML adatlekérdezéskor használd COALESCE() az alapértelmezett értékek megadását, amikor XML metódusok NULL-t adnak vissza.

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

Kapcsolódó tartalom

• Adattípus-leképezések
• JSON adatok
• Microsoft SQL XML dokumentáció