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(컬럼과 Microsoft SQL JSON 함수 포함nvarchar)을 사용하세요. 대부분의 새로운 애플리케이션은 데이터가 본질적으로 문서 구조화되지 않는 한 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 검증
서버에서 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 값() 메서드
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 표현식이 문서 내 노드와 일치하는지 테스트하여 참을 1, 거짓일 경우 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 콘텐츠를 제자리에서 업데이트합니다.
insert를 사용한 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()
delete를 사용한 XML modify()
XQuery의 delete 작업에서 modify() 메서드를 사용하여 XML 문서에서 요소 또는 노드를 제거합니다.
cursor.execute("""
UPDATE #XMLOrders
SET OrderXML.modify('
delete /Order/Items/Item[@ProductID="A1"]
')
WHERE OrderID = %(id)s
""", {"id": 1})
conn.commit()
XML modify()를 사용한 replace
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 문자열로 반환할 수 있습니다.
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>
FOR 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을 여러 행에 나누어 분할할 수 있습니다; 모든 부분을 하나의 문서로 결합할 수 있습니다.
# 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 인덱스
더 빠른 XML 쿼리를 위해 Microsoft SQL로 인덱스를 생성하세요:
-- 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;
네임스페이스 작업
declare namespace 구문을 사용하여 XQuery 표현식에서 네임스페이스 접두사를 인라인으로 선언합니다.
네임스페이스가 있는 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 을 저장할지 결정하세요:
이 비교는 Microsoft SQL xml 타입을 사용할지 아니면 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
""")