반응형

/*******************************************************************************************
-- Title : [2k5] XML 인덱스 및 XQUERY
-- Reference : hanbitbook.co.kr
-- Key word : create primary xml index exists
*******************************************************************************************/
USE sqldb;
GO

SELECT Title, FirstName, MiddleName, LastName, EmailAddress, Phone
FROM  AdventureWorks.Person.Contact
WHERE contactid = 1
FOR XML RAW;
GO
/*
<row Title="Mr." FirstName="Gustavo" LastName="Achong" EmailAddress="gustavo0@adventure-works.com" Phone="398-555-0132"/>
*/

DROP TABLE indexXmlTbl;
GO

--XML 인덱스 실습 테이블 생성
CREATE TABLE indexXmlTbl (id INT IDENTITY NOT NULL PRIMARY KEY,
 fullName VARCHAR(30), xmlInfo XML);
GO

--2만여건의 데이터 입력
DECLARE @xml XML -- XML 데이터
DECLARE @fullName VARCHAR(20)
DECLARE @contactId INT -- ContactID 변수
DECLARE @i INT -- 반복할 변수
DECLARE @cnt INT -- 전체 행 개수

SET @i = 1;
SET @contactId = 1;

SELECT @cnt = COUNT(contactID) FROM AdventureWorks.Person.Contact; -- 행 데이터 개수

WHILE (@i <= @cnt) -- 행데이터 개수 만큼
BEGIN
 SET @fullName = (SELECT FirstName + ' ' + LastName
    FROM  AdventureWorks.Person.Contact
    WHERE contactID = @contactId )

 IF (@fullName <> '') -- 해당 contactId의 사용자가 있다면..
 BEGIN
  SET @xml = (SELECT Title, FirstName, MiddleName, LastName, EmailAddress, Phone
     FROM  AdventureWorks.Person.Contact
     WHERE contactID = @contactId
     FOR XML RAW )
  INSERT INTO indexXmlTbl VALUES (@fullName, @xml);
  SET @i = @i + 1
 END
 SET @contactId = @contactId + 1
END;
GO

--테이블 확인
SELECT * FROM indexXmlTbl;
GO

--인덱스 생성하지 않을 테이블 생성
SELECT * INTO noIndexXmlTbl FROM indexXmlTbl;
GO

--인덱스 생성
CREATE PRIMARY XML INDEX xmlIdx_indexXmlTbl_xmlInfo
 ON indexXmlTbl (xmlInfo);
GO

--실행계획 비교(XQuery 사용)
SELECT * FROM indexXmlTbl
 WHERE xmlInfo.exist('/row[@Phone="334-555-0137"]') = 1;
SELECT * FROM noIndexXmlTbl
 WHERE xmlInfo.exist('/row[@Phone="334-555-0137"]') = 1;
GO

반응형

+ Recent posts