Microsoft SQL Server 2005에서 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는 다음과 같은 새로운 기능을 제공합니다.

1. 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>

2. 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>

3. 선택적 인수로 대상 이름 공간 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 지시어를 사용하기 위해), 새로운 기능 덕분에 이제는 “지옥의 쿼리”를 자주 겪지 않게 될 것입니다.

제공 : DB포탈사이트 DBguide.net

출처명 : 한국 마이크로소프트