Home > Archive > SQL Server Programming > November 2005 > Writing code to do a dynamic insert
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Writing code to do a dynamic insert
|
|
|
| I have a situation where I am using Service Broker that sends an XML payload
upon a DML action. On the recipient server I am consuming the XML payload
and processing the request (please don't tell me this is what replication
does...there are several reasons we need to use this method over
replication). I have the whole process working but I would like to know if
there is a way to process the request without hardcoding in the column
names?
create procedure recProc
as
declare @m xml; --the received message
declare @h uniqueidentifier; -- conversation handle
begin
declare @ID as int;
declare @FirstName as varchar(150);
declare @LastName as varchar(150);
receive top(1) @m = message_body,
@h = conversation_handle
from q2 -- receive the message from the queue
begin transaction
--INSERT-----------------------------------------------------
declare cursorInserted cursor read_only for
select n.value('./ID[1]','int'), <------- this is the part I would like to
be dynamic rather than hardcoded...such as SELECT *
n.value('./LastName[1]','varchar(150)'),
n.value('./FirstName[1]','varchar(150)')
FROM @m.nodes('contacts[@table_name="contacts"]/Inserted')
t(n);
open cursorInserted
fetch next from cursorInserted into @ID,@LastName,@FirstName;
while @@fetch_status = 0
begin
insert into [rem_ssb2].dbo.[Contacts](ID,LastName,FirstName) values
(@ID,@LastName,@FirstName);
fetch next from cursorInserted into @ID,@LastName,@FirstName;
end
close cursorInserted;
deallocate cursorInserted;
Also how do I make the script process multiple messages?
Thanks for any help you might be able to provide.
Ron
| |
| John Bell 2005-11-29, 4:00 am |
| Hi
Posting example xml and desired results would help to clarify your question.
If your XML is well defined then I am not sure why you want to make your
query dynamic. If you want to insert different nodes you can use a union.
You should be able to remove the cursor and just use INSERT... SELECT.
John
"RSH" wrote:
> I have a situation where I am using Service Broker that sends an XML payload
> upon a DML action. On the recipient server I am consuming the XML payload
> and processing the request (please don't tell me this is what replication
> does...there are several reasons we need to use this method over
> replication). I have the whole process working but I would like to know if
> there is a way to process the request without hardcoding in the column
> names?
>
> create procedure recProc
>
> as
>
> declare @m xml; --the received message
>
> declare @h uniqueidentifier; -- conversation handle
>
> begin
>
> declare @ID as int;
>
> declare @FirstName as varchar(150);
>
> declare @LastName as varchar(150);
>
> receive top(1) @m = message_body,
>
> @h = conversation_handle
>
> from q2 -- receive the message from the queue
>
> begin transaction
>
> --INSERT-----------------------------------------------------
>
> declare cursorInserted cursor read_only for
>
> select n.value('./ID[1]','int'), <------- this is the part I would like to
> be dynamic rather than hardcoded...such as SELECT *
>
> n.value('./LastName[1]','varchar(150)'),
>
> n.value('./FirstName[1]','varchar(150)')
>
> FROM @m.nodes('contacts[@table_name="contacts"]/Inserted')
>
> t(n);
>
> open cursorInserted
>
> fetch next from cursorInserted into @ID,@LastName,@FirstName;
>
> while @@fetch_status = 0
>
> begin
>
> insert into [rem_ssb2].dbo.[Contacts](ID,LastName,FirstName) values
> (@ID,@LastName,@FirstName);
>
> fetch next from cursorInserted into @ID,@LastName,@FirstName;
>
> end
>
> close cursorInserted;
>
> deallocate cursorInserted;
>
>
>
> Also how do I make the script process multiple messages?
>
>
>
> Thanks for any help you might be able to provide.
>
>
>
> Ron
>
>
>
>
>
>
>
|
|
|
|
|