1.
2.
3.
4.
create proc dbo.temp
as
select 1 as rs1;
select 2 as rs2;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
declare @sConnectionString varchar(150) = 'Provider=SQLOLEDB;Server=(local);Database=test;Integrated Security=SSPI;'
declare
@cn int,
@rs int,
@hResult int,
@sErrorSource varchar(255),
@sErrorDesc varchar(255),
@sSQL varchar(255),
@xmlDoc int,
@rsState int
declare @t table (id int identity, xml_data xml)
-- **********************************
exec @hResult = sp_OACreate 'ADODB.Connection', @cn out;
exec @hResult = sp_OAMethod @cn, 'Open', null, @sConnectionString;
exec @hResult = sp_OACreate 'MSXML2.DOMDocument', @xmlDoc out;
set @sSQL = 'exec dbo.temp';
exec @hResult = sp_OAMethod @cn, 'Execute', @rs out, @sSQL;
while 1 = 1 begin
exec @hResult = sp_OAMethod @rs, 'Save', null, @xmlDoc, 1;
insert into @t (xml_data)
exec @hResult = sp_OAMethod @xmlDoc, 'xml';
exec @hResult = sp_OAMethod @rs, 'NextRecordset', @rs out;
exec @hResult = sp_OAGetProperty @rs, 'State', @rsState out;
if @rsState = 0 break;
end
exec @hResult = sp_OADestroy @xmlDoc;
exec @hResult = sp_OADestroy @rs;
exec @hResult = sp_OADestroy @cn;
select * from @t;
idxml_data1<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"><s:Schema id="RowsetSchema"><s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
<s:AttributeType name="rs1" rs:number="1"><s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false" /></s:AttributeType> <s:extends type="rs:rowbase" /></s:ElementType></s:Schema>
<rs:data><z:row rs1="1" /></rs:data> </xml>2<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"><s:Schema id="RowsetSchema"><s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
<s:AttributeType name="rs2" rs:number="1"><s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false" /></s:AttributeType> <s:extends type="rs:rowbase" /></s:ElementType></s:Schema>
<rs:data><z:row rs2="2" /></rs:data> </xml>
