/**********************************************************************************************
-- Title : [2k5] OPENXML 및 핸들(TABLE <-> XML)
-- Reference : hanbitbook.co.kr
-- Key word : xml openxml handle 핸들
**********************************************************************************************/
USE tempdb;
GO
CREATE TABLE userTbl
(userId NCHAR(8),
name NVARCHAR(10),
addr NCHAR(4),
height INT
);
GO
INSERT INTO userTbl
SELECT 'aaa','aaa','aaa', 170 UNION
SELECT 'aaa2','aaa2','aaa2', 190 UNION
SELECT 'aaa3','aaa3','aaa3', 200;
GO
DECLARE @handle INT; -- 핸들
DECLARE @xmlDoc XML; -- XML 데이터
SET @xmlDoc =
(SELECT userId,name,addr FROM userTbl
WHERE height > 180
FOR XML AUTO , ROOT
); -- xml 요소 형태로 변환
/* @xmlDoc값
<root>
<userTbl userId="aaa2" name="aaa2" addr="aaa2"/>
<userTbl userId="aaa3" name="aaa3" addr="aaa3"/>
</root>
*/
EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc; -- XML의 구문을 분석
SELECT *
FROM OPENXML(@handle, '/root/userTbl',0)
WITH (userId NCHAR(8),
name NVARCHAR(10),
addr NCHAR(4)
); -- 표 형태로 데이터 처리
EXEC sp_xml_removedocument @handle; -- 메모리 해제
GO
/*
userId name addr
------ ------ ------
aaa2 aaa2 aaa2
aaa3 aaa3 aaa3
*/