Kommentar
Åtkomst till den här sidan kräver auktorisering. Du kan prova att logga in eller ändra kataloger.
Åtkomst till den här sidan kräver auktorisering. Du kan prova att ändra kataloger.
Microsoft SQL tillhandahåller en inbyggd xml datatyp med serverbaserad bearbetning:
- XQuery för att söka XML-innehåll.
- XML-metoder (
value(),query(),exist(), ,nodes())modify()för extraktion och modifiering. - Valfri validering av XML-schema.
- XML-index för prestanda.
mssql-python-drivrutinen skickar och tar emot XML-data som strängar. All XML-bearbetning (XQuery, XML-metoder, schemavalidering) körs på Microsoft SQL-sidan. Använd Python xml.etree.ElementTree eller liknande bibliotek för klient-sida XML-parsing.
När man ska använda XML vs JSON: Använd XML när du behöver schemavalidering, namnrymdsstöd eller blandat innehåll (text blandat med element). Använd JSON (med nvarchar kolumner och Microsoft SQL:s JSON-funktioner) när din data är nyckelvärdesorienterad, konsumeras av webb-API:er eller inte behöver schema-övervakning. De flesta nya applikationer föredrar JSON om inte datan är i grunden dokumentstrukturerad.
Infoga XML-data
Skicka XML som en Python-sträng; drivrutinen skickar det till Microsoft SQL:s inbyggda xml-kolumntyp.
Infoga som sträng
Infoga XML-innehåll direkt som en sträng i en xml-kolumn med hjälp av parameteriserade frågor.
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()
Bygg XML med ElementTree
Konstruera XML-dokument programmatiskt med Python:s ElementTree-bibliotek och konvertera sedan till en sträng för insättning.
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()
Validera XML innan insättning
Validera XML-syntaxen på servern med SQL:s TRY/CATCH och XML-typcasting för att avvisa felaktigt formade dokument innan lagring.
# 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})
Sök XML-data
Använd XML-metoder på servern för att extrahera specifika värden utan att hämta hela dokument till klienten.
XML-värde()-metoden
Extrahera enskilda skalärvärden eller attribut från XML med metoden value() med XPath-uttryck.
Extrahera skalära värden från 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()-metoden
Returnera XML-fragment (inte skalärvärden) från dokumentet med XPath-uttryck.
Extrahera XML-fragment:
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()-metoden
Testa om ett XPath-uttryck matchar några noder i dokumentet, och returnera 1 för sant och 0 för falskt.
Kolla om XPath matchar:
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() metod (strimla till rader)
Konvertera nästlade XML-element till en relationell raduppsättning med operatorn CROSS APPLY och nodes() metoden.
Konvertera XML till relationsformat:
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}")
Ändra XML-data
Använd XML.modify() med XQuery DML-uttryck för att uppdatera XML-innehåll som finns på plats.
XML modify() med hjälp av insert
Lägg till nya element i ett XML-dokument med metoden modify() med XQuerys insert operation.
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() med hjälp av delete
Ta bort element eller noder från ett XML-dokument med metoden modify() med XQuerys delete operation.
cursor.execute("""
UPDATE #XMLOrders
SET OrderXML.modify('
delete /Order/Items/Item[@ProductID="A1"]
')
WHERE OrderID = %(id)s
""", {"id": 1})
conn.commit()
XML modify() med ersätta
Uppdatera attributvärden eller elementtext i ett XML-dokument med metoden modify() med XQuerys replace value of operation.
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-frågor
Lägg till FOR XML på vilken fråga som helst för att returnera resultat som en enda XML-sträng.
FÖR XML RAW
Generera XML där varje rad blir ett enkelt element med kolumner som attribut.
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>
FÖR XML AUTO
Generera XML med nästlad struktur som automatiskt speglar join-hierarkin i din fråga.
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
FÖR XML-SÖKVÄG
Skapa anpassade XML-strukturer med explicita kolumnalias och underfrågor för att styra kapsling och elementnamn.
Mest kontroll över XML-strukturen:
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()
Parse XML i Python
Hämta XML som en sträng och parsa den med xml.etree.ElementTree ett kompatibelt bibliotek.
Parsa frågeresultat med ElementTree
Hämta XML från databasen och parsa det till ett Python-objektträd med ElementTree, och åtkomst till element och attribut via DOM:en.
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]}")
Konvertera XML till ordbok
Omvandla en XML-trädstruktur till en nästlad Python-ordbok för enklare programmatisk åtkomst till nästlad data.
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)
Hantera stora XML-dokument
Microsoft SQL kan dela upp stora FOR XML resultat över flera rader; koppla ihop delarna innan parsning.
Hämta XML i delar
När FOR XML stora resultatuppsättningar returneras kan Microsoft SQL dela upp XML:en över flera rader; kombinera alla delar i ett enda dokument.
# 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)
Ström-XML-parsning
För mycket stora XML-dokument, använd iterativ parsning för att bearbeta elementen ett i taget utan att ladda hela trädet i minnet.
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-index
Skapa index i Microsoft SQL för snabbare XML-frågor:
-- 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;
Arbete med namnrymder
Deklarera namnrymdsprefix direkt i XQuery-uttryck med syntaxen declare namespace.
Sök XML med namnrymder
Lägg till namnrymsdeklarationer i dina XPath-uttryck för att matcha element i ett specifikt namnrymd.
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})
Tolka XML med namnrymder i Python
När du parsar XML med namnrymder i Python, deklarera namnrymdsmappningarna i dina find() och andra elementåtkomstanrop.
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}")
Metodtips
Tillämpa dessa riktlinjer för att arbeta effektivt med XML-data.
Välj XML istället för JSON
Bestäm om du vill använda Microsoft SQL:s inbyggda xml typ eller lagra JSON baserat nvarchar på dina datastruktur- och bearbetningsbehov:
Denna jämförelse hjälper dig att avgöra om du ska använda Microsoft SQL:s xml typ eller lagra JSON invarchar:
| Feature | XML | JSON |
|---|---|---|
| Schemavalidering | Internt stöd | Inget internt stöd |
| Namnområden | Fullständigt stöd | Inget stöd |
| Attributes | Stöds | Ingen direkt motsvarighet |
| Blandat innehåll | Stöds | Stöds ej |
| Dokumentbearbetning | Bättre | Mindre strukturerat |
Undvik SELECT *
Hämta inte hela XML-dokument när du bara behöver specifika värden. Använd XML-metoder för att extrahera data på servern. Denna metod minskar nätverkstrafiken och undviker att tolka stora XML-dokument i 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
""")
Hantera NULL-XML
När du frågar XML-data, använd COALESCE() för att ange standardvärden när XML-metoder returnerar NULL.
cursor.execute("""
SELECT OrderID,
COALESCE(OrderXML.value('(/Order/@Total)[1]', 'DECIMAL(10,2)'), 0) AS Total
FROM #XMLPerf
""")