Microsoft SQL Server 2005에서 FOR XML의 새로운 기능
Michael Rys
Microsoft Corporation
2004년 6월
수정일: 2005년 6월
적용 대상:
Microsoft SQL Server 2005
요약: Michael Rys가 작성한 시리즈의 이 첫 번째 문서에서는 SQL Server의 다음 버전에서 서버 쪽 FOR XML 절의 주요 새 기능을 설명합니다. 이러한 기능을 이용하면 응용 프로그램에서 XML 지원을 보다 강화할 수 있고 유지 관리가 쉬운 관계형 데이터와 XML 간 집계를 작성할 수 있습니다.
목차
소개
Microsoft SQL Server 2000에서는 SELECT 문에 FOR XML 절이 도입되었습니다. 이 절은 SELECT 문에서 반환한 관계형 행 집합을 XML로 집계하는 기능을 제공합니다. 서버의 FOR XML은 서로 다른 변환 의미를 제공하는 세 가지 모드(RAW, AUTO, EXPLICIT)를 지원합니다.
RAW 모드는 반환된 각 행에 대해 row라는 단일 요소를 생성합니다.
AUTO 모드는 계보 정보 및 SELECT 문의 데이터 순서에 근거하여 간단한, 수준 당 하나의 요소 이름 계층 구조를 추론하는 추론적 방법을 사용합니다.
마지막으로, EXPLICIT 모드는 단일 SQL 쿼리에 의해 공식화되는 동안 거의 모든 XML 형태에 매핑될 수 있는 특정 행 집합 형식을 필요로 합니다.
이 세 모드는 모두 대규모 문서를 효율적으로 만들 수 있도록 스트림 가능한 방식으로 XML을 생성하도록 설계되어 있습니다.
EXPLICIT 모드 형식은 그 목표를 달성하는 데 있어서는 대단히 성공적입니다. 이 모드가 수행할 수 없는 작업은 단지 몇 가지뿐입니다(임의의 재귀적 부품 목록 트리가 그 중 하나입니다). 그렇지만 행 집합 형식을 생성하기 위한 기본 설정 SQL 식은 엄청나게 방대한 “지옥의 쿼리”입니다.
아쉽게도, SQL Server 2000의 FOR XML 결과는 클라이언트 쪽에서만 소비될 수 있으며 FOR XML EXPLICIT를 사용하여 복잡한 XML 구조를 만드는 일은 복잡한 작업입니다. 행 집합 형식 설계자 중 한 사람으로서 필자는 수천 줄의 EXPLICIT 모드 쿼리를 작성한 다음 유지 관리하는 분에게 무한한 존경심을 갖습니다. 그렇지만 한편 유용성, 유지 관리성 및 복잡성 문제에 대해서도 알고 있습니다.
SQL Server 2005에서는 이제 “지옥의 쿼리”에 대한 대안을 가질 수 있습니다. 이어지는 부분에서는 XML 데이터 형식과의 통합, 식의 할당 가능성 및 중첩 및 새로운 PATH 모드와 같은 SQL Server 2005에서 FOR XML에 추가된 주요 기능을 살펴보겠습니다. 새로운 기능을 사용하여 EXPLICIT 모드 쿼리를 더 쉽게 작성할 수 있는 방법을 보여주는 Northwind 스키마를 사용한 예제가 제공되어 있습니다.
이와 같은 새로운 기능 외에도, FOR XML은 간단하지만 새로운 기능을 몇 가지 더 제공하며(이 섹션의 끝부분에 요약되어 있음), 이름 공간 인식 문서를 생성하기 위한 보다 쉬운 방법을 지원합니다.
참고 FOR XML은 여전히 SQL SELECT 문의 행 집합 집계 절이 되므로 저장 프로시저의 부작용 출력을 변환할 수 없습니다. XML로 변환된 처리 형태의 결과를 원하는 경우 사용자 정의 함수 또는 뷰를 사용하십시오.
SQL Server 2000과의 호환성
SQL Server 2005에서 FOR XML의 중요한 측면은 SQL Server 2000의 FOR XML과의 호환성입니다. 이전 버전과의 호환성 측면은 버그 수정, 호환성 모드 변경 및 FOR XML 동작(behavior) 유지의 범주로 나누어질 수 있습니다.
첫 번째 범주인 버그 수정의 경우에는 AUTO 모드에 뷰 및 하위 쿼리의 처리와 관련하여 몇 가지 버그 수정을 포함시켰습니다. 또한 공백을 보존하고 엔터티화(entitization)를 최소화기 위해 엔터티화 규칙을 변경했습니다(이 변경은 의미론적으로는 아무런 영향도 끼치지 않습니다). 이는 버그 수정으로 간주되므로 서버 호환성 플래그와 상관 없이 발생합니다.
많은 사용자들이 SQL 형식 timestamp의 인스턴스는 숫자 표시 대신 base64로 인코딩된 바이너리 값으로 보고되어야 한다고 생각했습니다. 그렇기 때문에 base64로 인코딩된 바이너리 값으로 직렬화되도록 SQL Server 2005에서 타임스탬프 값의 처리를 변경했습니다. 그렇다고 해도 여전히 SQL Server 2000 동작(behavior)은 서버의 호환성 수준을 SQL Server 2000으로 설정함으로써 요청할 수 있습니다.
마지막으로, SQL Server 2000은 성능상의 이유로 문법 적합성(well-formedness)을 확인하는 절차 없이 스트리밍 방식으로 결과를 반환했습니다. 이는 결과가 U+0007(ASCII BELL 문자)와 같은 유효하지 않은 XML 문자를 포함하거나, 혹은 (손상된 !xmltext 필드에 의해) 결과의 구조가 제대로 구성되지 않을 수 있음을 의미합니다. 이와 같은 경우는 모두 일반적으로 클라이언트 쪽 파서에 의해 탐지되지만, 사용자가 데이터를 소비하는 데 XML 파서를 사용하지 않고 그 대신에 간단한 부분 문자열 추출을 사용하는 경우가 있을 수 있습니다. 이 때문에 호환성 수준에 상관 없이 SQL Server 2005에서 이전 버전과의 호환성 방식으로 이러한 동작(behavior)을 유지시키기로 결정했습니다. 새로운 XML 데이터 형식은 이러한 유효하지 않은 XML을 받아들이지 않으므로(단, FOR XML의 조각 결과를 처리할 수는 있음), SQL Server 2005에서 정상적인 FOR XML 쿼리는 결과를 nvarchar(max) 형식의 인스턴스로 반환합니다. 더 정확하게는, 그러한 쿼리는 그 결과를 셀이 nvarchar(max) 형식의 문자열 값을 포함하는 단일 행, 단일 열의 행 집합으로 반환합니다.
XML 데이터 형식과의 통합
XML 데이터 형식의 도입과 더불어 FOR XML에 XML의 인스턴스를 직접적으로 생성할 수 있는 능력을 부여하고자 했습니다. (더 정확하게 하자면, FOR XML이 셀이 XML 데이터 형식 인스턴스를 포함하는 단일 행, 단일 열의 행 집합을 생성할 수 있는 능력을 말합니다.)
위에서 설명한 이전 버전과의 호환성 고려 사항 때문에, 결과를 XML로 생성하기 위해 새로운 TYPE 지시어를 추가했습니다. 예를 들면, 아래의 예제는 Customers 요소를 XML 데이터 형식 인스턴스로 반환하는데, TYPE 지시어가 없는 경우에는 그 대신 nvarchar(max) 인스턴스로 반환할 것입니다.
SELECT * FROM Customers FOR XML AUTO, TYPE
이 결과는 XML 데이터 형식에서 제공하는 문법 적합성(well-formedness) 제약 조건을 따르도록 보장됩니다. 결과가 XML 데이터 형식 인스턴스이므로 XQuery 식을 사용하여 결과를 쿼리하고 결과의 형태를 변경할 수도 있습니다. 예를 들어 다음 식은 Customer 연락처 이름을 새 Person 요소에 가져옵니다.
SELECT (SELECT * FROM Customers FOR XML AUTO, TYPE).query(
'<doc>{
for $c in /Customers
return
<Person name="{data($c/@ContactName)}"/>
}</doc>')
이 식은 다음 결과를 반환합니다(첫 번째 요소만 표시).
<doc>
<Person name="Maria Anders" />
<Person name="Ana Trujillo" />
<Person name="Antonio Moreno" />
...
</doc>
FOR XML 결과 할당
FOR XML 쿼리가 이제 할당 가능한 값을 반환하므로 FOR XML의 결과를 변수에 할당하거나 열로 삽입할 수 있습니다.
DECLARE @cust XML;
SET @cust = (SELECT * FROM Customers FOR XML AUTO, TYPE)
CREATE TABLE T(i int, x XML)
INSERT INTO T SELECT 1, (SELECT * FROM Customers FOR XML AUTO, TYPE)
FOR XML 식의 중첩
SQL Server 2005에서 FOR XML은 XML 데이터 형식 열을 인식하고 이 열을 하위 요소로서 직렬화(inline)합니다. 따라서 AUTO 모드의 추론적 방법에 의존하거나 EXPLICIT 모드 쿼리를 작성할 필요 없이, FOR XML 쿼리를 중첩하여 계층을 생성할 수 있습니다.
예제를 살펴봅시다. 다음의 FOR XML EXPLICIT 쿼리는 고객의 주문 및 그 주문에 대해 작업하는 직원을 포함하는 Customer 요소를 반환합니다. 간단하게 하기 위해 요소 당 하나의 속성만 반환합니다.
SELECT 1 as TAG,
NULL as Parent,
CustomerID as "Customer!1!CustomerID",
NULL as "Order!2!OrderID",
NULL as "Employee!3!LastName"
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID,
NULL
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
UNION ALL
SELECT DISTINCT 3,
1,
Customers.CustomerID,
NULL,
Employees.LastName
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY "Customer!1!CustomerID","Employee!3!LastName","Order!2!OrderID"
FOR XML EXPLICIT
이 쿼리는 다음 결과를 반환합니다(첫 번째 고객만 표시).
<Customer CustomerID="ALFKI">
<Order OrderID="10643" />
<Order OrderID="10692" />
<Order OrderID="10702" />
<Order OrderID="10835" />
<Order OrderID="10952" />
<Order OrderID="11011" />
<Employee LastName="Davolio" />
<Employee LastName="Leverling" />
<Employee LastName="Peacock" />
<Employee LastName="Suyama" />
</Customer>
...
보는 바와 같이 각 요소마다 하나의 select 문이 필요합니다. 또한 order by가 부모(parent)와 함께 자식(child)을 그룹화하도록 각 자식(child)에 대해 부모(parent)의 ID를 반복합니다. 행 집합에서 XML로의 직렬화 스트리밍은 올바른 중첩을 얻기 위해 이 그룹화에 의존합니다.
이제 FOR XML 식을 사용하여 어떻게 이 쿼리를 다시 작성할 수 있는지 살펴봅시다. XML 데이터 형식 인스턴스를 생성하기 위해 새로운 TYPE 지시어를 활용할 수 있고(그렇지 않으면, 다른 FOR XML 쿼리에 포함되는 경우 엔터티화되는 텍스트 결과를 얻게 될 것입니다), 계층을 정의하기 위해 하위 선택을 중첩할 수 있습니다.
세 개의 “엔터티” 요소 각각에 대해 별개의 FOR XML 쿼리를 사용하고 계층을 표현하기 위해 이 요소들을 중첩합니다. 이제 다음과 같이 AUTO 모드 및 중첩을 사용하여 이전의 EXPLICIT 모드 쿼리를 다시 작성합니다.
SELECT CustomerID as "CustomerID",
(SELECT OrderID as "OrderID"
FROM Orders "Order"
WHERE "Order".CustomerID = Customer.CustomerID
FOR XML AUTO, TYPE),
(SELECT DISTINCT LastName as "LastName"
FROM Employees Employee
JOIN Orders "Order" ON "Order".EmployeeID = Employee.EmployeeID
WHERE Customer.CustomerID = "Order".CustomerID
FOR XML AUTO, TYPE)
FROM Customers Customer
FOR XML AUTO, TYPE
이 쿼리는 요소 순서를 보장한다는 점을 제외하면 EXPLICIT 모드 쿼리와 동일한 결과를 반환합니다. (순서가 중요하다면 명령문별 순서를 추가할 수 있습니다.)
명백하게 이 쿼리에는 기본적으로 같은 수의 select 명령문 및 join 조건이 들어 있지만 작성, 이해 및 유지 관리가 쉽습니다.
새로운 PATH 모드
위 섹션에서는 간단한 FOR XML EXPLICIT 쿼리를 FOR XML, AUTO 모드 및 새 XML 데이터 형식의 중첩성을 활용하는 더 간단한 쿼리로 다시 작성하는 FOR XML 기능의 일부를 활용할 수 있는 방법을 살펴보았습니다.
그러나, FOR XML EXPLICIT를 사용하여 실제 시나리오를 반영하기에는 앞의 쿼리가 너무 단순하다고 여겨질 수도 있습니다.
예를 들어, EXPLICIT 모드의 장점 중 일부는 마음대로 특성과 요소를 혼합하고, 래퍼 및 복잡한 중첩 속성을 만들고, 심지어 공백으로 구분된 값 목록 및 혼합 콘텐츠를 만들 수 있다는 점입니다. 이러한 결과는 FOR XML AUTO 쿼리를 중첩하는 방법으로는 달성할 수 없습니다. 그렇다면 이와 같은 매핑 기능을 얻으려면 여전히 EXPLICIT 모드 쿼리를 작성해야 합니까?
절망하지 마십시오! 새로운 FOR XML 모드는 훨씬 더 간단한 방법으로 동일한 유연성을 제공합니다. FOR XML 식을 중첩하는 기능과 함께 새로운 PATH 모드는 복잡한 XML 문서를 생성하기 위한 가장 간단한 방법으로 사용되어 왔습니다.
PATH 모드에서는 XPath형 구문을 열 이름으로 사용하여, 이를 특성(예: "@a"), 요소(예: "e"), 하위 요소 구조("e1/e2"), 요소 콘텐츠("*"), 텍스트 노드("text()") 또는 데이터 값("data()")으로 매핑할 수 있습니다. RAW 모드에서와 마찬가지로 행 요소의 기본 이름은 row이며 NCName(접두사 없는 이름)으로 덮어쓸 수 있습니다.
몇 가지 예제를 살펴봅시다. 먼저, 위 EXPLICIT 모드 쿼리의 PATH 모드 공식화를 사용해 봅시다.
SELECT CustomerID as "@CustomerID",
(SELECT OrderID as "@OrderID"
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
FOR XML PATH('Order'), TYPE),
(SELECT DISTINCT LastName as "@LastName"
FROM Employees
JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML PATH('Employee'), TYPE)
FROM Customers
FOR XML PATH('Customer')
이 예제는 AUTO 모드 버전과 유사하며 동일한 결과를 반환합니다.
이제 PATH 모드 고유 기능 중 일부를 살펴봅시다. 다음 쿼리는 고객 정보를 가져와서 더 복잡한 path 식을 열 별칭으로 사용하여 주소와 연락처 정보를 별개의 하위 요소로 그룹화하고, 또한 (새로운 ROOT 지시어를 사용하여) 여기에 루트 노드를 추가합니다.
SELECT CustomerID as "@CustomerID",
CompanyName,
Address as "address/street",
City as "address/city",
Region as "address/region",
PostalCode as "address/zip",
Country as "address/country",
ContactName as "contact/name",
ContactTitle as "contact/title",
Phone as "contact/phone",
Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer'), ROOT('doc')
이 쿼리는 다음 문서를 반환합니다(첫 번째 고객 요소만 표시).
<doc>
<Customer CustomerID="ALFKI">
<CompanyName>Alfreds Futterkiste</CompanyName>
<address>
<street>Obere Str. 57</street>
<city>Berlin</city>
<zip>12209</zip>
<country>Germany</country>
</address>
<contact>
<name>Maria Anders</name>
<title>Sales Representative</title>
<phone>030-0074321</phone>
<fax>030-0076545</fax>
</contact>
</Customer>
...
</doc>
EXPLICIT 모드를 사용한 경우 이 쿼리는 어떤 형태일까요? 이 쿼리에는 선택 부분(select 절)이 하나만 필요한 것이 아니라, 네 개의 select 절(잎이 아닌 요소마다 하나씩)이 필요합니다.
SELECT top 1
1 as TAG,
NULL as Parent,
1 as "doc!1!dummy!hide",
NULL as "Customer!2!CustomerID",
NULL as "Customer!2!CompanyName!element",
NULL as "address!3!street!element",
NULL as "address!3!city!element",
NULL as "address!3!region!element",
NULL as "address!3!zip!element",
NULL as "address!3!country!element",
NULL as "contact!4!name!element",
NULL as "contact!4!title!element",
NULL as "contact!4!phone!element",
NULL as "contact!4!fax!element"
FROM Customers
UNION ALL
SELECT 2, 1,
1,
CustomerID, CompanyName,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 3, 2,
1,
CustomerID, NULL,
Address, City, Region, PostalCode, Country,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 4, 2,
1,
CustomerID, NULL,
NULL, NULL, NULL, NULL, NULL,
ContactName, ContactTitle, Phone, Fax
FROM Customers
ORDER BY "doc!1!dummy!hide","Customer!2!CustomerID"
FOR XML EXPLICIT, TYPE
이제 왜 EXPLICIT 모드를 종종 “지옥의 쿼리”라고 하는지 알 수 있습니다."
마지막이지만 중요한 다음 쿼리는 값 목록 생성 예제로서 텍스트 노드의 사용을 보여줍니다.
SELECT CustomerID as "@ID",
(SELECT OrderID as "data()"
FROM Orders
WHERE Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@OrderIDs",
CompanyName,
ContactTitle as "ContactName/@ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer')
이 쿼리는 다음과 같은 형태의 결과를 생성합니다(고객 한 명만 표시).
<Customer ID="HUNGC" OrderIDs="10375 10394 10415 10600 10660">
<CompanyName>Hungry Coyote Import Store</CompanyName>
<ContactName
ContactTitle="Sales Representative">Yoshi Latimer</ContactName>
<Address ZIP="97827">
<Street>City Center Plaza 516 Main St.</Street>
<City>Elgin</City>
</Address>
</Customer>
쿼리의 관련 부분을 분석해 봅시다.
OrderIDs 특성 목록을 생성하는 하위 쿼리는 OrderID 열 값을 원자 값으로서 매핑합니다(path data()를 사용하여). 그런 다음 이들 값은, 행 집합에서 바로 옆의 셀에 제공되어 있는 형제 항목 원자 값 사이에 공백을 추가하여 텍스트 노드로 직렬화됩니다. 그러고 나면 FOR XML PATH 식의 결과로서 하나의 문자열을 얻도록(TYPE 지시어가 없다는 점에 주의할 것!), PATH 모드 인수로 빈 문자열을 사용함으로써 해당 열의 이름이 생성되는 것을 피할 수 있습니다. 이 문자열은 포함하는 FOR XML 식을 통해 OrderIDs 특성에 매핑됩니다.
CompanyName은 동일한 이름의 하위 요소에 매핑됩니다.
ContactName 열 값이 동일한 요소의 텍스트 노드에 매핑되는 동안 ContactTitle은 ContactName 요소의 ContactTitle 특성을 생성합니다. 이 경우에, ContactName을 ContactName 요소에 직접 매핑하여 동일한 결과가 달성되었다는 사실에 주의하십시오.
마지막으로, Address 요소 부분의 속성이 한데 결합됩니다.
XML 이름 공간 추가
XML 이름 공간은 정보 교환을 위해 XML 문서 제작의 점점 더 중요한 측면이 되고 있습니다. XML 이름 공간은 서로 다른 어휘를 명확하게 하고, 어휘의 소유권을 식별하고, XML 스키마 정보(및 잠재적으로 그 외 다른 정보)를 XML 요소 또는 특성에 연결하는 데 사용됩니다.
SQL Server 2000에서 FOR XML은 XML 이름 공간 생성 및 유지 관리의 부담을 쿼리 작성기에 부과합니다. 열 값이 되는 이름 공간 URI를 사용하여, 모든 다른 특성과 마찬가지로 XML 이름 공간 선언 특성을 만들어야 합니다. 생성된 XML이 특성 중심 형태가 아니라면, 이는 EXPLICIT 모드를 사용하여 쿼리를 작성해야 됨을 의미합니다. 예를 들어, 다음 쿼리는 결과 Customer 요소와 그 속성 요소를 이름 공간 urn:example.com/customer에 넣습니다.
SELECT 1 as tag, NULL as parent,
'urn:example.com/customer' as "cust:Customer!1!xmlns:cust",
CustomerID as "cust:Customer!1!cust:CustomerID!element",
ContactName as "cust:Customer!1!cust:ContactName!element"
FROM Customers
FOR XML EXPLICIT
쿼리 결과는 다음과 같습니다(처음의 두 요소만 표시).
<cust:Customer xmlns:cust="urn:example.com/customer">
<cust:CustomerID>ALFKI</cust:CustomerID>
<cust:ContactName>Maria Anders</cust:ContactName>
</cust:Customer>
<cust:Customer xmlns:cust="urn:example.com/customer">
<cust:CustomerID>ANATR</cust:CustomerID>
<cust:ContactName>Ana Trujillo</cust:ContactName>
</cust:Customer>
이름 공간 선언은 실제로는 XML 데이터 모델의 특성이 아닙니다. 따라서 PATH 모드에서 이름 공간 선언이 특성으로 지정될 수 없습니다.
FOR XML에서 XML 이름 공간의 사용을 단순화하기 위해, SQL Server 2005의 April CTP 버전부터 WITH XMLNAMESPACES 절 지원이 추가되었습니다. WITH XMLNAMESPACES 절은 일반적인 테이블 식을 정의하는 데 주로 사용되는 일반 WITH 절의 확장으로 SQL:2003 표준에 정의되어 있습니다. WITH 절은 SELECT, INSERT, UPDATE 문과 같은 상위 수준 SQL 문에 놓여질 수 있고 CREATE VIEW 문 내에서 사용될 수 있습니다. WITH XMLNAMESPACES 절은 RAW, AUTO, PATH 모드와 함께 사용될 수 있지만 XMLSCHEMA 및 XMLDATA 지시어 또는 EXPLICIT 모드와는 함께 사용될 수 없습니다.
앞의 이름 공간 생성 방법은 여전히 SQL Server 2000 모드에서 지원되지만 WITH XMLNAMESPACES 절과 혼합될 수 없습니다. WITH 절을 WITH의 다른 구문적 사용에서 명확하게 하기 위해 WITH 절을 선행하는 T-SQL 문은 세미콜론(;)과 함께 종료해야 합니다. 다음 쿼리는 고객 및 주문 데이터를 서로 다른 이름 공간에 놓고 기본 이름 공간의 루트 노드를 추가합니다.
WITH XMLNAMESPACES (
DEFAULT 'urn:example.com/doc'
, 'urn:example.com/customer' as "c"
, 'urn:example.com/order' as "o"
)
SELECT CustomerID as "@ID",
(SELECT OrderID as "@OrderID"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('o:Order'), TYPE
) as "c:Orders",
CompanyName as "c:CompanyName",
ContactTitle as "c:ContactName/@ContactTitle",
ContactName as "c:ContactName/text()",
PostalCode as "c:Address/@ZIP",
Address as "c:Address/c:Street",
City as "c:Address/c:City"
FROM Customers
FOR XML PATH('c:Customer'), ROOT('doc')
다음의 부분적인 결과가 보여주는 대로 XML 이름 공간 선언은 현재 각 FOR XML 선택 부분의 상위 수준 요소에 추가되어 있습니다.
<doc xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer"
xmlns="urn:example.com/doc">
<c:Customer ID="ALFKI">
<c:Orders>
<o:Order xmlns:o="urn:example.com/order"
xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc"
OrderID="10643" />
<o:Order xmlns:o="urn:example.com/order"
xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc"
OrderID="10692" />
<o:Order xmlns:o="urn:example.com/order"
xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc"
OrderID="10702" />
<o:Order xmlns:o="urn:example.com/order"
xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc"
OrderID="10835" />
<o:Order xmlns:o="urn:example.com/order"
xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc"
OrderID="10952" />
<o:Order xmlns:o="urn:example.com/order"
xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc"
OrderID="11011" />
</c:Orders>
<c:CompanyName>Alfreds Futterkiste</c:CompanyName>
<c:ContactName
ContactTitle="Sales Representative">Maria Anders</c:ContactName>
<c:Address ZIP="12209">
<c:Street>Obere Str. 57</c:Street>
<c:City>Berlin</c:City>
</c:Address>
</c:Customer>
...
위 쿼리는 기본 이름 공간을 추가하기 위해 DEFAULT 절을 사용했습니다. 결과에 포함된 기본 이름 공간이 없는 중첩 XML 문서가 있는 경우, 기본 이름 공간의 부재를 유지하도록 하기 위해 약간의 성능의 손실이 따른다는 점에 주의하십시오.
마지막으로, WITH XMLNAMESPACES 절은 XML 데이터 형식에서 XQuery 및 XML DML 메서드에 대한 이름 공간 바인딩을 제공하기 위해 사용될 수도 있습니다.
재귀 및 FOR XML
XML 형식의 장점 중 하나는 계층(부품 목록과 같은 구조적으로 재귀적인 계층 포함)을 쉽게 표시할 수 있다는 점입니다. SQL Server 2000에서는 쿼리 공식화 시에 최대 깊이를 모르는 상태에서는 이러한 구조를 생성할 수 없었습니다. 이제 FOR XML 식을 중첩할 수 있으므로 사용자 정의 함수를 사용하여 재귀 계층을 쉽게 생성할 수 있습니다.
예를 들어 다음 사용자 정의 함수는 특정 부품에 대한 부품 목록을 상술하는 중첩 XML 문서를 만듭니다. 먼저, 몇 가지 예제 데이터를 정의해 봅시다.
CREATE TABLE PARTS(id int, parent int, name nvarchar(500))
GO
INSERT INTO PARTS
SELECT 1, NULL, N'car'
UNION
SELECT 2, 1, N'engine'
UNION
SELECT 3, 1, N'body'
UNION
SELECT 4, 3, N'door'
UNION
SELECT 5, 3, N'fender'
UNION
SELECT 6, 4, N'window'
UNION
SELECT 7, 2, N'piston'
다음으로, 제공된 부품 번호에 대해 XML 형식으로 하위 부품을 반환하는 함수를 정의합니다.
CREATE FUNCTION PartsList(@PartsNo int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT id as "@id", name as "@name",
CASE WHEN parent=@PartsNo
THEN dbo.PartsList(id)
END
FROM dbo.PARTS WHERE parent=@PartsNo
FOR XML PATH('Parts'), TYPE)
END
최적화 프로그램이 선택 부분 실행 후 필터를 적용하도록 결정하는 경우에 쿼리 실행이 재귀적으로 함수를 계산하지 않도록 하기 위해 CASE 문을 사용합니다. 다음 식을 실행하면 부품 3을 하위 부품과 함께 반환합니다.
select id as "@id", name as "@name",
CASE WHEN id=3
THEN dbo.PartsList(id)
END
FROM PARTS
WHERE id=3
FOR XML PATH('Parts'), TYPE
반환 결과:
<Parts id="3" name="body">
<Parts id="4" name="door">
<Parts id="6" name="window" />
</Parts>
<Parts id="5" name="fender" />
</Parts>
SQL Server 2005에서 재귀적으로 중첩된 함수 호출의 최대 제한은 32회입니다. 부품 계층이 이 제한을 초과할 경우 XML을 플랫 형식으로 가져와서 XSLT 스타일 시트를 적용하여 계층을 생성하는 이전의 방식을 사용해야 할 것입니다.
추가 FOR XML 확장
위에서 언급한 새 기능 외에도 SQL Server 2005는 다음과 같은 새로운 기능을 제공합니다.
- RAW 모드는 ELEMENTS 지시어와 결합할 수 있고 매개 변수를 사용하여 행 요소 이름을 덮어쓸 수 있습니다. 예를 들면 다음과 같습니다.
SELECT *
FROM Customers
FOR XML RAW('Customer'), ELEMENTS
이 쿼리는 다음 결과를 반환합니다(첫 번째 고객만 표시).
<Customer>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</Customer>
- ELEMENTS 지시어는 NULL 값을 xsi:nil="true" 특성을 가진 요소에 매핑하는 XSINIL 옵션을 제공합니다. 예를 들면 다음과 같습니다.
SELECT *
FROM Customers
WHERE Region is null
FOR XML PATH('Customer'), ELEMENTS XSINIL
이 쿼리는 다음 결과를 반환합니다(첫 번째 고객만 표시).
<Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<Region xsi:nil="true" />
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</Customer>
- 선택적 인수로 대상 이름 공간 URI를 사용하는 새로운 인라인 스키마 추론 지시어 XMLSCHEMA가 RAW 및 AUTO 모드에 추가되었습니다. 예를 들면 다음과 같습니다.
SELECT *
FROM Customers
FOR XML RAW('Customer'), XMLSCHEMA('urn:example.com')
이 쿼리는 다음 결과를 반환합니다(스키마 및 데이터 부분만 표시).
<xsd:schema targetNamespace="urn:example.com"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes"
elementFormDefault="qualified">
<xsd:import namespace=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes" />
<xsd:element name="Customer">
...
</xsd:element>
</xsd:schema>
<Customer xmlns="urn:example.com" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" />
...
EXPLICIT 및 PATH 모드는 RAW 및 AUTO 모드와 달리 일반적으로 이전에 제공한 스키마에 따라 XML을 생성하는 경우에 사용된다는 점에 주의하십시오. 그렇기 때문에 EXPLICIT 및 PATH 모드에서는 스키마 추론 지시어를 제공하지 않습니다.
결론
이 문서에서는 SQL Server 2005에서의 확장된 FOR XML 지원에 대해 살펴보았습니다. 대부분 새로운 XML 데이터 형식에서 사용 가능한 추가 기능으로 인해 FOR XML은 관계형 데이터에서 XML을 생성하기 위한 매우 강력하고 사용하기 쉬운 도구가 될 수 있습니다. 새로운 PATH 모드는 FOR XML 쿼리의 중첩 및 WITH XMLNAMESPACES 절과 함께 더 간단하고 유지 관리하기 쉬운 방식으로 대부분의 EXPLICIT 모드 쿼리를 대체할 만큼의 강력한 기능을 제공합니다. 또한 FOR XML 쿼리를 중첩할 수 있는 기능은 재귀 계층을 생성하는 메커니즘을 제공합니다.
여전히 EXPLICIT 모드를 사용해야 하는 경우가 간혹 있을 수 있지만(예: CDATA 섹션을 생성하거나 !xmltext 지시어를 사용하기 위해), 새로운 기능 덕분에 이제는 “지옥의 쿼리”를 자주 겪지 않게 될 것입니다.