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


   

+ Recent posts

티스토리 툴바