Communicate XML between SP’s

Hi,
I have two databases and need to transfer data between them. The problem is
that I’m facing the 8000 characters limitation of a varchar and I cannot use
a text declaration within the trigger/SP itself. Therefore I had the
following idea:
SP 1:
CREATE PROCEDURE [DBO].[testXMLSelect] AS
declare @.idoc as int
--Load and parse the XML document in Memory
EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
for xml auto
exec testXMLInsert @.idoc
--now clear the XML document from memory
EXEC sp_xml_removedocument @.idoc
GO
SP 2
CREATE PROCEDURE [dbo].[testXMLInsert]
@.idoc as int
AS
insert into testtext (test)
select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
GO
My Idea was to parse the handle to the XML document in Memory from one to
the other SP, and later on to the other database (same server). Does anyone
have an idea how to solve this?
Regards,
Merijn
You cannot use a select statement as a parameter to a stored procedure...
assign the result to a variable and pass the variable.
Also, you may want to upgrade to SQL Server 2005 where you don't have the 8k
limit anylonger (and have XML data type and much more).
Best regards
Michael
"Merijn" <Merijn@.discussions.microsoft.com> wrote in message
news:482A7B95-4FC7-4C36-B4C9-BDFDAC76C31C@.microsoft.com...
> Hi,
> I have two databases and need to transfer data between them. The problem
> is
> that I'm facing the 8000 characters limitation of a varchar and I cannot
> use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does
> anyone
> have an idea how to solve this?
> Regards,
> Merijn
>
|||On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
> Hi,
> I have two databases and need to transfer data between them. The problem is
> that I'm facing the 8000 characters limitation of a varchar and I cannot use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does anyone
> have an idea how to solve this?
> Regards,
> Merijn
I have a requirment to share data between two databases and I'm hoping
you could outline to me the steps you have taken to get me started. I
won't run into the varchar limit in my application.
Thanks,
lq
|||On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
wrote:
> On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
>
>
> I have a requirment to share data between two databases and I'm hoping
> you could outline to me the steps you have taken to get me started. I
> won't run into the varchar limit in my application.
> Thanks,
> lq
Now that you mentioned the Server is same, but have 2 seperate DBs,
how about using DB Name of the 1st DB in your QUERY of 2nd DB;
something like this
In DB2, you have your query as
Create table #t (MyXMLColumn XML )
Insert INTO #t (MyXMLColumn)
Select <WhatEverXMLColumnNameYouWant> From
<DBName>.<ObjectOwner>.testtext where id=3
|||On Mar 4, 2:59 pm, prabh...@.gmail.com wrote:
> On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
> wrote:
>
>
>
>
>
> Now that you mentioned the Server is same, but have 2 seperate DBs,
> how about using DB Name of the 1st DB in your QUERY of 2nd DB;
> something like this
> In DB2, you have your query as
> Create table #t (MyXMLColumn XML )
> Insert INTO #t (MyXMLColumn)
> Select <WhatEverXMLColumnNameYouWant> From
> <DBName>.<ObjectOwner>.testtext where id=3- Hide quoted text -
> - Show quoted text -
In my case it's two different servers I cave to communicate using SOAP/
XML to send data from the SQL Server 2000 database to the remote
server.