FOR XML 쿼리의 TYPE 지시어

  • 아티클

SQL Server 2000에서는 FOR XML 쿼리 결과가 항상 텍스트 형식으로 클라이언트에 직접 반환됩니다. SQL Server 2005 에서는 xml 데이터 유형이 지원되므로 필요에 따라 TYPE 지시어를 지정하여 FOR XML 쿼리가 xml 데이터 유형으로 반환되도록 요청할 수 있습니다. 그러면 서버에서 FOR XML 쿼리 결과를 처리할 수 있습니다. 예를 들어 이 결과에 대해 XQuery 쿼리를 지정하거나 xml 유형 변수에 결과를 할당하거나 중첩 FOR XML 쿼리를 쓸 수 있습니다.

[!참고] SQL Server에서는 TYPE 지시어를 사용하거나 xml 데이터 유형을 사용하여 SQL 테이블 열과 출력 매개 변수에서 XML 인스턴스 데이터 값을 반환하는 FOR XML 쿼리와 같은 여러 서버 구문의 결과로 XML 데이터 유형 인스턴스 데이터를 클라이언트에 반환합니다. 클라이언트 응용 프로그램 코드에서 ADO.NET 공급자가 이 XML 데이터 유형 정보를 서버에서 이진 인코딩으로 보내도록 요청합니다. 그러나 TYPE 지시어 없이 FOR XML을 사용하면 XML 데이터가 문자열 유형으로 반환됩니다. 클라이언트 공급자는 항상 두 XML 형식 중 하나를 처리할 수 있습니다.

예

다음 예에서는 FOR XML 쿼리를 사용하는 방법을 보여 줍니다.

1. FOR XML 쿼리 결과를 xml 유형으로 검색

다음 쿼리에서는 Contacts 테이블에서 고객 연락처 정보를 검색합니다. FOR XML에 TYPE 지시어가 지정되어 있으므로 결과는 xml 유형으로 반환됩니다.

SELECT ContactID, FirstName, LastName, Phone
FROM Person.Contact
ORDER BY ContactID
FOR XML AUTO, TYPE

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

<Contact ContactID="1" FirstName="Syed" LastName="Abbas" 
         Phone="398-555-0132"/>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel" 
         Phone="747-555-0171"/>
...

2. xml 유형 변수에 FOR XML 쿼리 결과 할당

다음 예에서는 FOR XML 결과가 xml 유형 변수 @x에 할당됩니다. 쿼리는 xml TYPE의 AdditionalContactInfo 열에서 ContactID, FirstName, LastName 및 추가 전화 번호와 같은 연락처 정보를 검색합니다. FOR XML 절은 TYPE 지시어를 지정하므로 XML은 xml 유형으로 반환되며 변수에 할당됩니다.

DECLARE @x XML
SET @x = (
   SELECT ContactID, 
          FirstName, 
          LastName, 
          AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
              //act:telephoneNumber/act:number') as MorePhoneNumbers
   FROM Person.Contact
   FOR XML AUTO, TYPE)
SELECT @x
GO

3. FOR XML 쿼리 결과 쿼리

FOR XML 쿼리가 XML을 반환하므로 FOR XML 쿼리에서 반환한 XML 결과에 query(), value() 등의 xml 유형 메서드를 적용할 수 있습니다.

다음 쿼리에서는 xml 데이터 유형의 query() 메서드를 사용하여 FOR XML 쿼리 결과를 쿼리합니다. 자세한 내용은 query() 메서드(xml 데이터 형식)를 참조하십시오.

SELECT (SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
 //act:telephoneNumber/act:number
') as PhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE).query('/Person.Contact[1]')

내부 SELECT … FOR XML 쿼리는 외부 SELECT가 query() 메서드를 xml 유형에 적용하는 xml 유형 결과를 반환합니다. 지정된 TYPE 지시어에 유의하십시오.

다음은 결과입니다.

<Person.Contact ContactID="1" FirstName="Gustavo" LastName="Achong">
  <PhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">111-111-1111</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">112-111-1111</act:number>
  </PhoneNumbers>
</Person.Contact>

다음 쿼리에서는 xml 데이터 유형의 value() 메서드를 사용하여 SELECT…FOR XML 쿼리가 반환한 XML 결과에서 값을 검색합니다. 자세한 내용은 value() 메서드(xml 데이터 형식)를 참조하십시오.

declare @FirstPhoneFromAdditionalContactInfo varchar(40);
SELECT @FirstPhoneFromAdditionalContactInfo = 
 ( SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

   //act:telephoneNumber/act:number
   ') as PhoneNumbers
   FROM Person.Contact Contact
   FOR XML AUTO, TYPE).value('
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
  /Contact[@ContactID="1"][1]/PhoneNumbers[1]/act:number[1]', 'varchar(40)'
 )
select @FirstPhoneFromAdditionalContactInfo

value() 메서드의 XQuery 경로 식이 ContactID가 1인 고객 연락처의 첫 번째 전화 번호를 검색합니다.

[!참고] TYPE 지시어를 지정하지 않으면 FOR XML 쿼리 결과가 nvarchar(max) 형식으로 반환됩니다.

4. INSERT, UPDATE 및 DELETE(Transact-SQL DML)에 FOR XML 쿼리 결과 사용

다음 예에서는 DML(데이터 조작 언어) 문에 FOR XML 쿼리를 사용할 수 있는 방법을 보여 줍니다. 예에서 FOR XML은 xml 유형의 인스턴스를 반환합니다. INSERT 문은 이 XML을 테이블에 삽입합니다.

CREATE TABLE T1(intCol int, XmlCol xml)
go
INSERT INTO T1 
VALUES(1, '<Root><ProductDescription ProductModelID="1" /></Root>')
go

CREATE TABLE T2(XmlCol xml)
go
INSERT INTO T2(XmlCol) 
SELECT (SELECT XmlCol.query('/Root') 
        FROM T1 
        FOR XML AUTO,TYPE) 
go