/*******************************************************************************************
-- 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