ASP 에서 XML 을 이용한 데이타를 SP 로 전송하고 받기 Sample
<!--METADATA TYPE= "typelib" NAME= "ADODB Type Library"
FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" -->
<% Option Explicit %>
<%
Dim objConn
Dim strConnection
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=test;User id=sa; Password=test;"
objConn.Open strConnection
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objConn
objCmd.CommandText = "Usp_testCrud"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@xml_data", advarwchar, adParamInput, 8000)
objCmd.Parameters("@xml_data") = "<xml><comm><para>3</para><etc>2</etc></comm></xml>"
dim rs
set rs = objCmd.Execute
Dim intResult
intResult = rs(0)
Response.write intResult
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
--[프로시져 시작]
alter proc Usp_testCrud
@xml_data ntext
as
begin
declare
@ixml_data int,
@para varchar(10),
@etc varchar(10)
exec sp_xml_preparedocument @ixml_data output, @xml_data
--[쿼리시작]
----[공통시작 : 고정인자 para, etc]
SELECT
@para = para,
@etc = etc
FROM
openxml(@ixml_data, '/xml/comm', 2)
with (
para varchar(10),
etc varchar(10)
)
SELECT @para, @etc
--[쿼리끝]
exec sp_xml_removedocument @ixml_data
end
--[프로시져 끝]
exec Usp_testCrud '<xml><comm><para>1</para><etc>2</etc></comm></xml>'