내용 보기

작성자

관리자 (IP : 172.17.0.1)

날짜

2020-07-09 04:53

제목

[MSSQL] SP에 XML 파라메터 설정하기


DECLARE @LDAPUserData XML

DECLARE @LDAPUserTable table (Idx bigint NOT NULL, UserID nvarchar(50), MobilePhone nvarchar(20), Email nvarchar(50), Department nvarchar(100), UserName nvarchar(100))

INSERT INTO @LDAPUserTable
SELECT
T.C.value('@Idx','bigint') as Idx,
T.C.value('@UserID','nvarchar(50)') as UserID,
T.C.value('@MobilePhone','nvarchar(20)') as MobilePhone,
T.C.value('@Email','nvarchar(50)') as Email,
T.C.value('@Department','nvarchar(100)') as Department,
T.C.value('@UserName','nvarchar(100)') as UserName
FROM
@LDAPUserData.nodes('/LDAPUsers/User') AS T(C)


XML로 받은 데이터를 @LDAPUserTable 임시 테이블에 Insert

xml 데이터 : '<LDAPUsers><User Idx="1" UserID="Test" MobilePhone="11" Email="11" Department="11" UserName="11" /><User Idx="2" UserID="Test2" MobilePhone="7777" Email="7777" Department="7777" UserName="7777" /></LDAPUsers>'

출처1

출처2