예제: AUTO 모드 사용하기

아티클
07/12/2024

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance

아래의 예제는 AUTO 모드 사용법을 보여줍니다. 이러한 쿼리는 대부분 AdventureWorks2022 예제 데이터베이스 내 ProductModel 테이블의 Instructions 열에 저장된 자전거 제조지시 XML 문서에 대해 지정됩니다.

예제: 고객, 주문, 주문 상세정보 검색하기

이 쿼리는 특정 고객에 대해 고객, 주문, 주문 상세정보를 검색합니다.

USE AdventureWorks2022;
GO
SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       Detail.SalesOrderID, Detail.LineTotal, Detail.ProductID,
       Product.Name,
       Detail.OrderQty
FROM Sales.Customer AS Cust
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
    ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail AS Detail
    ON OrderHeader.SalesOrderID = Detail.SalesOrderID
INNER JOIN Production.Product AS Product
    ON Product.ProductID = Detail.ProductID
WHERE Cust.CustomerID IN (29672, 29734)
ORDER BY OrderHeader.CustomerID,
         OrderHeader.SalesOrderID
FOR XML AUTO;

쿼리가 Cust, OrderHeader, Detail, Product 테이블 별칭을 식별하므로 AUTO 모드에 의해 상응하는 요소가 생성됩니다. 또한 SELECT 절에서 지정된 열로 식별되는 테이블 순서에 따라 이들 요소의 계층 구조가 결정됩니다.

다음은 결과의 일부입니다.

<Cust CustomerID="29672">
  <OrderHeader CustomerID="29672" 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="29672" SalesOrderID="47660">
    <Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
      <Product Name="Road-650 Black, 58" />
    </Detail>
  </OrderHeader>
  <OrderHeader CustomerID="29672" SalesOrderID="49857">
    <Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
      <Product Name="Women's Tights, S" />
    </Detail>
  </OrderHeader>
...
</Cust>

예제: GROUP BY 및 집계 함수 지정하기

다음 쿼리는 개별 고객 ID와 고객이 요청한 주문 건수를 반환합니다.

USE AdventureWorks2022;
GO
SELECT C.CustomerID, COUNT(*) AS NoOfOrders
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
On C.CustomerID = SOH.CustomerID
GROUP BY C.CustomerID
FOR XML AUTO;

다음은 결과의 일부입니다.

<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...

예제: AUTO 모드에서 계산 열 지정하기

이 쿼리는 연결된 개별 고객명과 주문 정보를 반환합니다. 계산 열은 해당 시점에서 대면하는 가장 안쪽 수준, 이 예제의 경우 <SOH> 요소에 할당되므로 연결된 고객명이 결과에서 <SOH> 요소의 특성으로 추가됩니다.

USE AdventureWorks2022;
GO
SELECT P.FirstName + ' ' + P.LastName AS Name,
       SOH.SalesOrderID
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
    ON  C.CustomerID = SOH.CustomerID
INNER JOIN Person.Person AS P
    ON P.BusinessEntityID = C.PersonID
FOR XML AUTO;

다음은 결과의 일부입니다.

<SOH Name="Jon Yang" SalesOrderID="43793" />
<SOH Name="Eugene Huang" SalesOrderID="43767" />

<IndividualCustomer> 특성이 각 판매 주문 헤더 정보를 하위 요소로 포함한다고 할 때 이러한 특성을 갖는 Name 요소를 검색하려면 하위 선택을 사용해 쿼리를 재작성합니다. 내부 선택은 개별 고객명을 포함하는 계산 열이 있는 임시 IndividualCustomer 테이블을 생성합니다. 다음으로 이 테이블은 SalesOrderHeader 테이블과 조인해 결과를 얻습니다.

Sales.Customer 테이블에는 고객의 PersonID 값을 비롯한 개별 고객 정보가 저장됩니다. 다음으로 이 PersonID은 Person.Person 테이블에서 연락처명을 찾는 데 사용됩니다.

SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName AS Name, C.PersonID, C.CustomerID
      FROM Sales.Customer AS C, Person.Person AS P
      WHERE C.PersonID = P.BusinessEntityID) AS IndividualCustomer
LEFT OUTER JOIN  Sales.SalesOrderHeader AS 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>
...

예제: 이진 데이터 반환하기

이 쿼리는 ProductPhoto 테이블에서 제품 사진을 반환합니다. ThumbNailPhoto는 ProductPhoto 테이블의 varbinary(max) 열입니다. AUTO 모드는 쿼리가 실행되는 데이터베이스 가상 루트의 상대 URL 레퍼런스를 이진 데이터에 기본값으로 반환합니다. 이미지를 식별하기 위해서는 ProductPhotoID 키 특성을 지정해야 합니다. 이 예제에서 보는 바와 같이 이미지 레퍼런스를 검색할 때는 행의 고유 식별을 위해 SELECT 절에 테이블의 기본 키를 또한 지정해야 합니다.

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto
WHERE ProductPhotoID = 70
FOR XML AUTO;

다음은 결과입니다.

<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;

다음은 결과입니다.

<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 쿼리를 실행할 때 문제가 될 수 있습니다. 이를 방지하려면 쿼리에 지정된 테이블 또는 열 이름의 대·소문자가 데이터베이스 테이블 또는 열 이름의 대·소문자와 일치해야 합니다.

예제: 인코딩 이해하기

이 예제는 결과에서 발생하는 다양한 인코딩을 보여줍니다.

다음과 같은 테이블을 만듭니다.

CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50));

테이블에 다음 데이터를 추가합니다.

INSERT INTO [Special Chars] VALUES ('&', 0x20), ('#', 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에 포함된 문자 #는 _x0023_ in the DBOJBECT URL로 인코딩됩니다.

참고 항목

FOR XML에서 AUTO 모드 사용

다음을 통해 공유

예제: AUTO 모드 사용하기

예제: 고객, 주문, 주문 상세정보 검색하기

예제: GROUP BY 및 집계 함수 지정하기

예제: AUTO 모드에서 계산 열 지정하기

예제: 이진 데이터 반환하기

예제: 인코딩 이해하기

참고 항목

피드백

피드백

추가 리소스