Call stored procedure for each XML element without cursors

Question

I have this stored procedure in SQL Server:

alter PROCEDURE [dbo].[spSendLogLinesAsXML]
(
    @device_id varchar(128),
    @application_name VARCHAR(64),
    @application_user_name VARCHAR(6),
    @log_lines_xml XML
)
AS
BEGIN

DECLARE
    @ixml INT,
    @log_line_dt DATETIME,
    @log_line_message varchar(max)

EXEC sp_xml_preparedocument @ixml OUTPUT,
    @log_lines_xml

SELECT @log_line_dt = dt,
    @log_line_message = data
FROM OPENXML(@ixml, '/lines/line', 3) WITH (
        dt DATETIME,
        data varchar(max)
        )   

--I want to do the following for each line element      
EXEC spSendLogLine
    @device_id = @device_id,
    @application_name = @application_name,
    @application_user_name = @application_user_name,
    @log_line_dt = @log_line_dt,
    @log_line_message = @log_line_message   

EXEC sp_xml_removedocument @ixml

return -100

END

I call the stored procedure like this:

EXEC @return_value = [dbo].[spSendLogLinesAsXML]
     @device_id = N'devid123',
     @application_name = N'CJA App 1',
     @application_user_name = N'anatoli',
     @log_lines_xml = '<lines><line><dt>2013-03-01T13:00:00</dt><data>Something happened and it was logged</data></line><line><dt>2013-03-01T13:01:00</dt><data>Oh my god the building is burning and people are dying</data></line></lines>'

How can I modify my stored procedure to call spSendLogLine for each line element?

Edit: According to SQL - Call Stored Procedure for each record cursors are bad. So I want to know a better way. I don't mind how much my stored procedure is changed to achieve this, as long as it ends up working properly and is nice.

score 1 · Accepted Answer · answered Mar 01 '13 at 16:41

1

alter PROCEDURE [dbo].[spSendLogLinesAsXML]
(
    @device_id varchar(128),
    @application_name VARCHAR(64),
    @application_user_name VARCHAR(6),
    @log_lines_xml XML
)
AS
BEGIN

declare @log_line_dt datetime,
        @log_line_message varchar(1024)

declare @curse cursor 
set @curse = cursor fast_forward for 
select n.d.value('dt[1]', 'datetime') as log_line_dt, n.d.value('data[1]', 'varchar(1024)') as log_line_message
from @log_lines_xml.nodes('/lines/line') n(d)

open @curse

fetch next from @curse into @log_line_dt, @log_line_message

while (@@fetch_status = 0)
begin
    EXEC spSendLogLine
        @device_id = @device_id,
        @application_name = @application_name,
        @application_user_name = @application_user_name,
        @log_line_dt = @log_line_dt,
        @log_line_message = @log_line_message   

    fetch next from @curse into @log_line_dt, @log_line_message
end

close @curse;

return -100
END
GO

answered Mar 01 '13 at 16:41

muhmud

4,474
2
15
22

1

But everyone at http://stackoverflow.com/questions/2077948/sql-call-stored-procedure-for-each-record seems to think cursors are bad – cja Mar 01 '13 at 16:44
1

@cja: either you can rewrite your stored procedure so it can handle multiple input parameters - then you can avoid a cursor - or then you have to live with the evilness and uglyness of cursors in T-SQL .... – marc_s Mar 01 '13 at 16:50
My question was "How can I modify my stored procedure..." I'm happy to change it. I don't want to learn something if it's evil and ugly. I want to know the best way of doing it. – cja Mar 01 '13 at 16:51
Cursors can be bad when they are used to do the same thing as a select, such as doing an aggregation, etc.. However, sometimes, they are necessary, like in your case, were you want to *iterate* and run some operation on each iteration. – muhmud Mar 01 '13 at 16:53
@marc_s how do I "rewrite [my] stored procedure so it can handle multiple input parameters"? – cja Mar 01 '13 at 16:57
Seriously, there's not always a problem using a cursor :) What do you think app developers do when they get a resultset back in .NET or Java? – muhmud Mar 01 '13 at 17:12

score 0 · Answer 2 · answered Mar 01 '13 at 17:07

This is what I ended up with, following muhmud's answer. Still wish someone would tell me how to do it sans cursors.

ALTER PROCEDURE [dbo].[spSendLogLinesAsXML] 
(
    @device_id VARCHAR(128),
    @application_name VARCHAR(64),
    @application_user_name VARCHAR(6),
    @log_lines_xml XML
)
AS
BEGIN
DECLARE @ixml INT,
    @log_line_dt DATETIME,
    @log_line_message VARCHAR(max),
    @cursor CURSOR

SET @cursor = CURSOR FAST_FORWARD
FOR

SELECT n.d.value('dt[1]', 'datetime') AS log_line_dt,
    n.d.value('data[1]', 'varchar(max)') AS log_line_message
FROM @log_lines_xml.nodes('/lines/line') n(d)

OPEN @cursor

FETCH NEXT
FROM @cursor
INTO @log_line_dt,
    @log_line_message

WHILE (@@fetch_status = 0)
BEGIN
    EXEC spSendLogLine @device_id = @device_id,
        @application_name = @application_name,
        @application_user_name = @application_user_name,
        @log_line_dt = @log_line_dt,
        @log_line_message = @log_line_message

    FETCH NEXT
    FROM @cursor
    INTO @log_line_dt,
        @log_line_message
END

RETURN - 100
END

score 0 · Answer 3 · answered Mar 01 '13 at 17:17

If you're using SQL Server 2008 or newer, you could do something like this:

define a user-defined table type that holds all the columns that are relevant to your procedure:

CREATE TYPE dbo.SendLogLineType 
     AS TABLE (LineDate DATETIME2(3), LineData VARCHAR(200), DeviceID VARCHAR(128), 
               ApplicationName VARCHAR(64), AppUserName VARCHAR(6) )

then, extract your lines into that table, and add the other "fixed" parameters (this happens inside your [spSendLogLinesAsXML] procedure):

DECLARE @InputTable dbo.SendLogLineType

INSERT INTO @InputTable(LineDate, LineData, DeviceID, ApplicationName, AppUserName)
   SELECT
      XLine.value('(dt)[1]', 'DATETIME2'),
      XLine.value('(data)[1]', 'varchar(200)'), 
      @device_id,
      @application_name,
      @application_user_name
   FROM     
      @log_lines_xml.nodes('/lines/line') AS XTbl(XLine)

and lastly, change your spSendLogLine procedure to accept a table valued parameter of that defined table type and change its logic so that it will iterate over all rows that get passed into it:
```
CREATE PROCEDURE spSendLogLine
    (@SendLogLineData dbo.SendLogLineType READONLY)
AS
BEGIN
    -- iterate over all rows in @SendLogLineData and send out those e-mails!
END
```

Read more about Table-Valued Parameters on the relevant MSDN SQL Server Books Online documentation page

Wouldn't you need a cursor to do that iteration where you're sending out the emails? I suppose you would call `spSendLogLine` without having to use one ... — muhmud, Mar 01 '13 at 17:25
@Cja: just like you *iterate* over a table - use `SELECT` etc. — marc_s, Mar 04 '13 at 13:13
It seems to me this answer doesn't solve the problem: the question is "how do you iterate", but this answer is just a "you split the xml in a table, then you iterate". Ok. How? — Alberto Chiesa, Nov 05 '18 at 15:54

Call stored procedure for each XML element without cursors

3 Answers3