AUTO 모드 사용
FOR XML을 사용하여 XML 문서 검색 항목에 설명된 바와 같이 AUTO 모드는 쿼리 결과를 중첩된 XML 요소로 반환합니다. 이 모드에서는 쿼리 결과로 생성되는 XML의 모양을 상세하게 조정할 수 없습니다. AUTO 모드 쿼리는 간단한 계층을 생성하려는 경우에 유용합니다. 하지만 EXPLICIT 모드 사용 및 PATH 모드 사용 시에는 쿼리 결과로 생성되는 XML의 모양을 좀 더 상세하게 조정할 수 있습니다.
최소한 한 개의 해당 열이 SELECT 절에 나열되는 FROM 절의 각 테이블은 XML 요소로 표시됩니다. 선택 항목인 ELEMENTS 옵션이 FOR XML 절에 지정된 경우 SELECT 절에 나열되는 열은 특성이나 하위 요소로 매핑됩니다.
결과 XML에서 요소가 중첩된 XML 계층은 SELECT 절에 지정된 열에 의해 식별되는 테이블의 순서를 기반으로 합니다. 따라서 SELECT 절에 열 이름이 지정되는 순서가 중요합니다. 맨 앞에서 가장 왼쪽에 있는 것으로 식별되는 테이블은 결과 XML 문서의 최상위 요소를 만듭니다. 그 다음으로 SELECT 문의 열로 식별되는 왼쪽에서 두 번째에 있는 테이블은 최상위 요소 안에서 하위 요소를 만듭니다.
SELECT 절에 나열되는 열 이름이 SELECT 절에서 이전에 지정한 열에 의해 이미 식별된 테이블로부터 비롯된 경우 새로운 계층 수준을 여는 대신 이미 생성된 요소의 특성으로 열이 추가됩니다. ELEMENTS 옵션이 지정된 경우 열이 특성으로 추가됩니다.
예를 들어 다음 쿼리를 실행하십시오.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO
다음은 결과의 일부입니다.
<Cust CustomerID="1" CustomerType="S">
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...
SELECT 절에서 다음에 유의하십시오.
- CustomerID는 Cust 테이블을 참조합니다. 따라서 <
Cust> 요소가 생성되고 CustomerID가 그 특성으로 추가됩니다. - 그런 다음 OrderHeader.CustomerID, OrderHeader.SaleOrderID 및 OrderHeader.Status의 3개 열은 OrderHeader 테이블을 참조합니다. 따라서 <
OrderHeader> 요소는 <Cust> 요소의 하위 요소로 추가되고 이 3개의 열이 <OrderHeader>의 특성으로 추가됩니다. - 그런 다음 Cust.CustomerType 열은 Cust.CustomerID 열로 이미 식별된 Cust 테이블을 다시 참조합니다. 따라서 새 요소는 생성되지 않습니다. 대신 이전에 생성된 <
Cust> 요소에 CustomerType 특성이 추가됩니다. - 이 쿼리는 테이블 이름에 대한 별칭을 지정합니다. 이러한 별칭은 해당 요소 이름으로 나타납니다.
- ORDER BY는 하나의 부모 아래에 있는 모든 자식을 그룹화하는 데 필요합니다.
이 쿼리는 이전 쿼리와 비슷하지만 SELECT 절에서 Cust 테이블의 열 앞에 OrderHeader 테이블의 열을 지정합니다. 따라서 첫 번째 <OrderHeader> 요소가 생성된 다음 <Cust> 자식 요소가 여기에 추가됩니다.
select OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerID,
Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto
다음은 결과의 일부입니다.
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
<Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...
ELEMENTS 옵션이 FOR XML 절에 추가된 경우 요소 중심 XML이 반환됩니다.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS
다음은 결과의 일부입니다.
<Cust>
<CustomerID>1</CustomerID>
<CustomerType>S</CustomerType>
<OrderHeader>
<CustomerID>1</CustomerID>
<SalesOrderID>43860</SalesOrderID>
<Status>5</Status>
</OrderHeader>
...
</Cust>
...
이 쿼리에서 CustomerID 값은 CustomerID가 테이블의 기본 키이기 때문에 <Cust> 요소를 만들 때 한 행씩 순서대로 비교됩니다. CustomerID가 테이블의 기본 키로 식별되지 않는 경우 모든 열 값(이 쿼리의 CustomerType인 CustomerID)이 한 행씩 순서대로 비교됩니다. 값이 다르면 새로운 <Cust> 요소가 XML에 추가됩니다.
이러한 열 값을 비교할 때 비교되는 임의의 열 유형이 text, ntext, image 또는 xml인 경우 값이 같더라도 FOR XML은 값이 다르고 비교되지 않는 것으로 가정합니다. 이러한 이유는 큰 개체에 대한 비교가 지원되지 않기 때문입니다. 선택한 각 행에 대한 결과에 요소가 추가됩니다. (n)varchar(max) 및 **varbinary(max)**의 열이 비교되는지 확인합니다.
집계 열 또는 계산 열의 경우와 같이 SELECT 절의 열이 FROM 절에서 식별된 어떤 테이블과도 연결될 수 없는 경우 그 열은 목록에서 나타날 때 XML 문서의 가장 깊은 중첩 수준에 추가됩니다. 그러한 열이 SELECT 절에서 첫 번째 열로 나타나는 경우에는 최상위 요소에 추가됩니다.
별표(*) 와일드카드 문자를 SELECT 절에 지정하는 경우 위에 설명한 방법과 동일한 방식으로 행이 쿼리 엔진에 의해 반환되는 순서에 따라 중첩이 결정됩니다.
쿼리에서 BINARY BASE64 옵션을 지정하면 이진 데이터가 base64 인코딩 형식으로 반환됩니다. 기본적으로 BINARY BASE64 옵션이 지정되지 않은 경우 AUTO 모드는 이진 데이터의 URL 인코딩을 지원합니다. 즉, 이진 데이터 대신 쿼리가 실행된 데이터베이스의 가상 루트의 상대 URL에 대한 참조가 반환됩니다. 이 참조를 사용하면 후속 작업에서 SQLXML ISAPI dbobject 쿼리를 사용하여 실제 이진 데이터를 액세스할 수 있습니다. 쿼리는 이미지를 식별하기 위해 기본 키 열과 같은 정보를 충분히 제공해야 합니다.
쿼리를 지정할 때 뷰의 이진 열에 대해 별칭이 사용되는 경우 별칭은 이진 데이터의 URL 인코딩으로 반환됩니다. 후속 작업에서 별칭은 아무 의미도 없으며 URL 인코딩은 이미지를 검색하는 데 사용할 수 없습니다. 따라서 FOR XML AUTO 모드를 사용하여 뷰를 쿼리할 때는 별칭을 사용하지 않아야 합니다.
반환된 XML 모양 지정에서 AUTO 모드 추론 이해
AUTO 모드는 쿼리를 기반으로 반환된 XML의 모양을 결정합니다. 요소 중첩 방법을 결정할 때 AUTO 모드 추론은 인접한 행의 열 값을 비교합니다. ntext, text, image 및 xml을 제외한 모든 유형의 열이 비교됩니다. (n)varchar(max) 및 varbinary(max) 유형의 열이 비교됩니다.
다음 예에서는 결과 XML의 모양을 결정하는 AUTO 모드 추론에 대해 설명합니다.
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
테이블 T1의 키가 지정되지 않은 경우 새로운 <T1> 요소가 시작되는 위치를 확인하기 위해 ntext, text, image 및 xml을 제외한 T1의 모든 열 값이 비교됩니다. 그런 다음 Name 열이 **nvarchar(40)**이고 SELECT 문이 이 행 집합을 반환한다고 가정합니다.
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
AUTO 모드 추론은 테이블 T1, Id 및 Name 열의 모든 값을 비교합니다. 처음 두 행은 Id 및 Name 열에 대한 값이 같기 때문에 두 개의 <T2> 자식 요소가 포함된 하나의 <T1> 요소가 결과에 추가됩니다.
다음은 반환되는 XML입니다.
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
이제 Name 열이 text 유형이라고 가정해 보십시오. AUTO 모드 추론은 이 유형의 값을 비교하지 않습니다. 그 대신 값이 같지 않다고 가정합니다. 그 결과 다음과 같이 XML이 생성됩니다.
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
예
다음 예에서는 AUTO 모드를 사용하는 방법을 보여 줍니다. 이러한 쿼리는 대부분 ProductModel 테이블의 Instructions 열에 저장된 자전거 제조 지침 XML 문서에 대해 지정됩니다. XML 지침에 대한 자세한 내용은 AdventureWorks 데이터베이스의 xml 데이터 형식 표시를 참조하십시오.
1. 고객, 주문 및 주문 세부 정보 검색
이 쿼리는 특정 고객에 대한 고객, 주문 및 주문 세부 정보를 검색합니다.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID,
Product.Name,
Detail.OrderQty
FROM Sales.Customer Cust,
Sales.SalesOrderHeader OrderHeader,
Sales.SalesOrderDetail Detail,
Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND OrderHeader.SalesOrderID = Detail.SalesOrderID
AND Detail.ProductID = Product.ProductID
AND (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
OrderHeader.SalesOrderID
FOR XML AUTO
이 쿼리는 Cust, OrderHeader, Detail 및 Product 테이블 별칭을 식별하기 때문에 해당 요소가 AUTO 모드에 의해 생성됩니다. 다시 말해서 SELECT 절에 지정된 테이블이 열에 의해 식별되는 순서에 따라 이러한 요소들의 계층이 결정됩니다.
다음은 결과의 일부입니다.
<Cust CustomerID="117">
<OrderHeader CustomerID="117" SalesOrderID="43660">
<Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
<Product Name="Road-450 Red, 52" />
</Detail>
<Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
<Product Name="Road-650 Red, 44" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="47660">
<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
<Product Name="Road-650 Black, 58" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="49857">
<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
<Product Name="Women's Tights, S" />
</Detail>
</OrderHeader>
...
</Cust>
2. GROUP BY 및 집계 함수 지정
다음 쿼리는 개별 고객 ID와 고객이 요청한 주문 번호를 반환합니다.
SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO
다음은 결과의 일부입니다.
<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...
3. AUTO 모드에서 계산 열 지정
이 쿼리는 연결된 개별 고객 이름 및 주문 정보를 반환합니다. 계산 열은 해당 시점에 발생하는 가장 안쪽 수준으로 할당됩니다(이 예에서는 <SOH> 요소). 연결된 고객 이름은 결과에서 <SOH> 요소의 특성으로 추가됩니다.
select C.FirstName + ' ' + C.LastName as Name,
SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND I.CustomerID = SOH.CustomerID
FOR XML AUTO
다음은 결과의 일부입니다.
<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />
각 판매 주문 헤더 정보가 하위 요소로 들어 있는 Name 특성이 포함된 <IndividualCustomer> 요소를 검색하기 위해 하위 SELECT를 사용하여 쿼리가 다시 작성되었습니다. 내부 SELECT는 개별 고객의 이름이 포함된 계산 열이 있는 임시 IndividualCustomer 테이블을 만듭니다. 그런 다음 이 테이블이 SalesOrderHeader 테이블에 조인되어 결과를 가져옵니다.
Sales.Individual 테이블은 해당 고객에 대한 ContactID 값을 포함하여 개별 고객 정보를 저장합니다. 그런 다음 이 ContactID는 Person.Contact 테이블에서 담당자 이름을 찾는 데 사용됩니다.
SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
FROM Sales.Individual I, Person.Contact C
WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO
다음은 결과의 일부입니다.
<IndividualCustomer Name="Jon Yang">
<SOH SalesOrderID="43793" />
<SOH SalesOrderID="51522" />
<SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...
4. 이진 데이터 반환
이 쿼리는 Employees 테이블로부터 직원 사진을 반환합니다. Photo는 Employees 테이블에 있는 image 열입니다. 기본적으로 AUTO 모드는 이진 데이터에 대해 쿼리가 실행되는 데이터베이스의 가상 루트에 대한 상대 URL인 참조를 반환합니다. 이미지를 식별하기 위해서는 EmployeeID 키 특성을 지정해야 합니다. 이 예에서 설명된 것과 같이 이미지 참조를 검색할 때 테이블의 기본 키도 행을 고유하게 식별할 수 있도록 SELECT 절에서 지정되어야 합니다.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO
다음은 결과입니다.
-- result
<Production.ProductPhoto
ProductPhotoID="70"
ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />
BINARY BASE64 옵션으로도 동일한 쿼리가 실행됩니다. 다음 쿼리는 이진 데이터를 base64 인코딩 형식으로 반환합니다.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64
다음은 결과입니다.
-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />
기본적으로 이진 데이터를 검색하기 위해 AUTO 모드를 사용하는 경우 쿼리가 실행되는 데이터베이스의 가상 루트에 대한 상대 URL 참조가 이진 데이터 대신 반환됩니다. 이 경우는 BINARY BASE64 옵션이 지정되지 않은 경우에 해당합니다.
AUTO 모드가 쿼리에 지정된 테이블 또는 열 이름이 데이터베이스에 있는 테이블 또는 열 이름과 일치하지 않는 대/소문자를 구분하지 않는 데이터베이스에 있는 이진 데이터에 대한 URL 참조를 반환하는 경우 쿼리가 실행됩니다. 하지만 참조에 반환된 대/소문자는 일관적이지 않습니다. 예를 들면 다음과 같습니다.
SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM Production.PRODUCTPHOTO
WHERE PRODUCTPHOTOID=70
FOR XML AUTO
다음은 결과입니다.
<Production.PRODUCTPHOTO
PRODUCTPHOTOID="70"
THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />
이는 특히 대/소문자를 구분하는 데이터베이스에 대해 dbobject 쿼리를 실행하는 경우에 문제가 될 수 있습니다. 이러한 문제를 방지하려면 쿼리에 지정된 테이블 또는 열 이름의 대/소문자가 데이터베이스에 있는 테이블 또는 열 이름의 대/소문자와 일치해야 합니다.
5. 인코딩 이해
다음 예에서는 결과에서 발생하는 여러 가지 인코딩을 보여 줍니다.
다음 테이블을 만듭니다.
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))
테이블에 다음 데이터를 추가합니다.
INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)
다음 쿼리는 테이블에서 데이터를 반환합니다. FOR XML AUTO 모드가 지정됩니다. 이진 데이터가 참조로 반환됩니다.
SELECT * FROM [Special Chars] FOR XML AUTO
다음은 결과입니다.
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
다음은 결과에서 특수 문자를 인코딩하는 처리 과정입니다.
- 쿼리 결과에서 반환된 요소와 특성 이름의 특수 XML 및 URL 문자는 그에 해당되는 유니코드 문자의 16진수 값을 사용하여 인코딩됩니다. 이전 결과에서 요소 이름 <
Special Chars>는 <Special_x0020_Chars>로 반환됩니다. 특성 이름 <Col#&2>는 <Col_x0023__x0026_2>로 반환됩니다. XML 및 URL 특수 문자가 모두 인코딩됩니다. - 요소나 특성의 값에 5개의 표준 XML 문자 엔터티(', "", <, > 및 &)가 있는 경우 이 특수 XML 문자는 항상 XML 문자 인코딩을 사용하여 인코딩됩니다. 이전 결과에서 <
Col1> 특성 값의 & 값은 **&**로 인코딩됩니다. 그러나 # 문자는 유효한 XML 문자이고 특수 XML 문자가 아니기 때문에 #로 그대로 유지됩니다. - 요소나 특성의 값에 URL에서 특수한 의미가 있는 특수 URL 문자가 있으면 이 문자는 DBOBJECT URL 값에서만 인코딩되고 특수 문자가 테이블이나 열 이름의 일부일 경우에만 인코딩됩니다. 결국 테이블 이름 Col#&2의 일부인 문자 #는 DBOJBECT URL에서 _x0023_으로 인코딩됩니다.