x

How to convert coursor to set based operetion ?

I want to get an idea on how to convert a cursor into set based operation.

Below is a sample cursor code. It create an xml file for each invoice and writes it on a given location. Can you please advice me on how to do it in a set based operation

DECLARE @XML XML      
DECLARE @FILENAME VARCHAR(MAX)         
DECLARE INVOICE_CURSOR CURSOR FOR       
SELECT TRAN_TYPE_ID      
FROM T_INTERFACE_CNTL_MASTER      
WHERE   
PROCESSED_STATUS = 'Y'       

OPEN INVOICE_CURSOR      

FETCH NEXT FROM INVOICE_CURSOR       
INTO @TRX_TIMESTAMP      

WHILE @@FETCH_STATUS = 0      
BEGIN      


/*      
CREATING INVOICE HEAD DATA      
*/        

-----------------------------------------------------      
-------------------------------------------------------------      

 SELECT       
  *   
 INTO #_INVHEAD      
 FROM       
 INVOICE_HEAD_TABLE     
 WHERE  INVOICE_HEAD_TABLE.INVOICE_ID=@TRX_TIMESTAMP

-------------------------------------------------------------      
-------------------------------------------------------------      
/*      
CREATE INVOICE LINE DATA      
*/        

-------------------------------------------------------------      
-------------------------------------------------------------      

 SELECT       
   *  
  INTO #_INVLINE      
  FROM      
 INVOICE_LINE_TABLE  
WHERE   
 INVOICE_LINE_TABLE.INVOICE_ID=@TRX_TIMESTAMP

-----------------------------------------------------      
-------------------------------------------------------------      
/*      
WRITE XML TO A FOLDER LOCATION      
*/        

-----------------------------------------------------      
-------------------------------------------------------------      

  SELECT @filename = [File_Location]       
  FROM [INTERFACE_CONFIGRATION]      
  WHERE [Message_Name]='INVOICE'      

  SET @filename = @filename +'\'+ CONVERT(varchar(255), NEWID())+'.xml'      
  WITH XMLNAMESPACES ('http://InvoicePayable.PublicSchemas.InvoicePayable_XML/v1.0' as ns0)      
SElect @XML=(      
 SELECT       
  APInvoice.INVOICE_NUM,      
  APInvoice.INVOICE_TYPE_LOOKUP_CODE,      
  APInvoice.GL_DATE,      
  APInvoice.ACCTS_PAY_CODE_SEGMENT1,      
  APInvoice.ORG_ID,      
  APInvoiceLine.LINE_NUMBER,      
  APInvoiceLine.LINE_TYPE_LOOKUP_CODE,      
  APInvoiceLine.AMOUNT,      
  APInvoiceLine.ORG_ID      
 FROM      
  #_INVHEAD APInvoice      
 INNER JOIN      
  #_INVLINE APInvoiceLine      
 ON      
  APInvoiceLine.INVOICE_ID=APInvoice.INVOICE_ID      
 FOR XML AUTO , ELEMENTS, ROOT('ns0:Stolt_FF_AP_INVOICE'))      


 EXEC SP_WriteToFile @filename,@XML -- CALL SP TO WRITE XML FILE      

-------------------------------------------------------------------      


 IF EXISTS(SELECT * FROM TEMPDB.SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('TEMPDB..#_INVLINE' ))       
 BEGIN      
  DROP TABLE #_INVLINE      
 END      
 IF EXISTS(SELECT * FROM TEMPDB.SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('TEMPDB..#_INVHEAD' ))       
 BEGIN      
  DROP TABLE #_INVHEAD      
 END      

 FETCH NEXT FROM INVOICE_CURSOR       
 INTO @TRX_TIMESTAMP      

END      


CLOSE INVOICE_CURSOR;      
DEALLOCATE INVOICE_CURSOR;      
more ▼

asked Aug 02 '12 at 06:55 AM in Default

saketKashyap gravatar image

saketKashyap
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
more ▼

answered Aug 04 '12 at 08:00 PM

Scot Hauder gravatar image

Scot Hauder
5.9k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57

asked: Aug 02 '12 at 06:55 AM

Seen: 559 times

Last Updated: Aug 04 '12 at 08:00 PM