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>'

[출처] ASP 에서 XML 을 이용한 데이타를 SP 로 전송하고 받기 Sample |작성자 빈센시오