XML AND Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML AND Cursors

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-08-27 : 05:49:01

Hi All

I know many people in this forum are dead against cursors and I am begining to understand why. so I am requesting assistance with the following Stored proc that produces hardcoded XML and stores it in the database. The stored procedure uses a cursor to loop throught a set of records and then produce XML data for each record. Can any one show me how:

1. I can eliminate the use of the cursor ;
2 Get rid of the hardcoded XML tags and produce The XML with the XML funcionaility that is shipped with SQL server.

I have over 55 stored procedures that use this method and this is one of the smaller ones, I would appreciate if any one can advise on how I can improve performance and code.

Good Luck



DROP PROC spXMLOutput_LastMonthVol
go

CREATE PROC spXMLOutput_LastMonthVol

@Clearall 	BIT = 0,
@ClearISIN      VARCHAR(20) = NULL

AS
BEGIN

DECLARE @return_status int, @databaseName VARCHAR(50)
SELECT @databaseName = DB_NAME(DB_ID())
EXEC @return_status = spSendMessages @databaseName
IF @return_status = 0 RETURN

SET NOCOUNT ON

DECLARE @TempDate VARCHAR(12)
select @tempDate = CONVERT(VARCHAR(12),DATEADD(mm,-1,getdate()),112)
SELECT @TempDate = SUBSTRING(@TempDate,1,4) + SUBSTRING(@TempDate,5,2)

-- varaibles needed
DECLARE 
  @Vol                 VARCHAR(30),
  @ISIN                VARCHAR(30)
       
-- Get the data from vMarks
DECLARE curVol CURSOR FOR
select isin,
CONVERT(VARCHAR(30), sum(ISNULL(volume,0)) )
from t1..companyMonthlySummary (NOLOCK)
where yearmonth = @TempDate
group by ISIN
ORDER BY ISIN


IF @Clearall = 1
BEGIN
SELECT @vol = ' '
SELECT @ISIN = @ClearISIN
END




OPEN curVol
FETCH NEXT FROM curVol INTO @ISIN, @Vol

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO XML1..ML1Messages (
ID,
ISIN,
Subscriber,
Datecreated,
Message,
XMLText1
)
VALUES (
@ID,
@ISIN,
'TEST1',
Getdate(),
'MESSAGE1'
  '<MESSAGE>' + char(13) + char(10) +
  '   <HEADER>' + char(13) + char(10) +
  '      <MESSAGETYPE>TEST1</MESSAGETYPE>' + char(13) + char(10) +
  '      <ISIN>' + @ISIN + '</ISIN>' + char(13) + char(10) +
  '   </HEADER>' + char(13) + char(10) +
  '   <BODY>' + char(13) + char(10) + 
  '         <LastMonthVol>' + @Vol + '</LastMonthVol>' + char(13) + char(10) +
  '   </BODY>' + char(13) + char(10) +
  '</MESSAGE>' + char(13) + char(10) 
  )

  FETCH NEXT FROM curVol INTO @ISIN, @Vol
END

CLOSE curVol
DEALLOCATE curVol

SET NOCOUNT OFF

END

go

GRANT ALL ON spXMLOutput_LastMonthVol to PUBLIC
go

Thanxs OMB

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-27 : 09:09:01

Hardly you can avoid using cursors. Is it really so slow?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-27 : 12:37:27

I hope the isin column never contains a '<' or '&'!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-27 : 19:24:28

I cannot see why you need a cursor at all here...

Use the INSERT..SELECT Syntax


INSERT INTO XML1..ML1Messages (ID,ISIN,Subscriber,Datecreated,Message,XMLText1)
SELECT ID, ISIN, 'TEST1', Getdate(), 
'MESSAGE1' + 
  '<MESSAGE>' + char(13) + char(10) +
  '   <HEADER>' + char(13) + char(10) +
  '      <MESSAGETYPE>TEST1</MESSAGETYPE>' + char(13) + char(10) +
  '      <ISIN>' + @ISIN + '</ISIN>' + char(13) + char(10) +
  '   </HEADER>' + char(13) + char(10) +
  '   <BODY>' + char(13) + char(10) + 
  '         <LastMonthVol>' + CONVERT(VARCHAR(30), sum(ISNULL(volume,0)) ) + '</LastMonthVol>' + char(13) + char(10) +
  '   </BODY>' + char(13) + char(10) +
  '</MESSAGE>' + char(13) + char(10) 
 FROM
from t1..companyMonthlySummary (NOLOCK)
where yearmonth = @TempDate
group by ISIN
ORDER BY ISIN

Just set your variables as before...And I can't see where you are setting the @ID variable....

EDIT: So many spelling mistakes...

DavidM

"SQL-3 is an abomination.."

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-08-28 : 05:21:23

Sorry cut and paste error, I dont actually use @ID in this SP, should replace with string, as for the spelling never been my strong point

.

If I dont use a cursor how will i be able to create a XML Message for each record!

OMB

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-28 : 07:22:22

David's code does just that. Try running it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources