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, 2012 at 06:55 AM in Default

avatar image

saketKashyap
10 1 1 1

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

1 answer: sort voted first

You can create a CLR scalar function to write the invoice files. This is similar to Pavel's recent answer here

The assembly needs External Access permission so you need to set your db to TRUSTWORTHY:

 ALTER DATABASE [YourDB] SET TRUSTWORTHY ON

Also, the CLR needs to be enabled, if not already:

 sp_configure 'clr enabled', 1
 RECONFIGURE WITH OVERRIDE

Once CLR is enabled and TRUSWORTHY SET, you can register your assembly and create the function:

 CREATE ASSEMBLY [XMLUtil]
 AUTHORIZATION [dbo]
 FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103007C741D500000000000000000E00002210B010800000A000000060000000000000E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B427000057000000004000005803000000000000000000000000000000000000006000000C000000202700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000001408000000200000000A000000020000000000000000000000000000200000602E72737263000000580300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000F0270000000000004800000002000500842000009C06000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020020000000010000110002281100000A281200000A0A0603281100000A6F1300000A00170B2B00072A1E02281400000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000028020000237E000094020000E802000023537472696E6773000000007C050000080000002355530084050000100000002347554944000000940500000801000023426C6F620000000000000002000001471502000900000000FA2533001600000100000014000000020000000200000002000000140000000E00000001000000010000000300000000000A0001000000000006003B0034000A0063004E000600A70095000600BE0095000600DB0095000600FA00950006001301950006002C01950006004701950006006201950006009A017B010600AE017B010600BC0195000600D501950006000502F2013F00190200000600480228020600680228020A00A10286020E00D202C2020000000001000000000001000100010010001600230005000100010050200000000096006D000A0001007C200000000086187A001200030000000100800000000200880019007A00160021007A00160029007A00160031007A00160039007A00160041007A00160049007A00160051007A00160059007A001B0061007A00160069007A00160071007A00160079007A00200089007A00260091007A00120099007A0012001100B6024300A100DC024900A100E202160009007A001200200083002B002E0033007C002E00130067002E001B0067002E0023006D002E002B0055002E000B0055002E003B0067002E004B0067002E0053009D002E006300C7002E006B00D4002E007300DD002E007B00E6004F000480000001000000000000000000000000002300000002000000000000000000000001002B000000000002000000000000000000000001004200000000000300050000000000000000000100C202000000000000003C4D6F64756C653E00584D4C5574696C2E646C6C00584D4C46756E6374696F6E7300584D4C5574696C006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67005772697465584D4C46696C65002E63746F7200584D4C446174610046756C6C46696C65506174680053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006F705F4578706C696369740053797374656D2E586D6C2E4C696E710058446F63756D656E74005061727365005361766500000003200000000000F40927BBD9FC0443A9AA4F9E340D5BB40008B77A5C561934E089070002081109110903200001042001010E04200101020520010111410420010108170100010054020F497344657465726D696E6973746963010500010E110905000112510E0507021251081101000C5772697465584D4C46696C6500000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200002901002462616461666531662D366465332D343534662D383035312D37386166653538376633393900000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000007C741D500000000002000000780000003C2700003C090000525344532E7B49181AD14F46B9BA0946C0D1FAE001000000633A5C75736572735C61646D696E6973747261746F725C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C5772697465584D4C46696C655C6F626A5C44656275675C584D4C5574696C2E70646200DC2700000000000000000000FE270000002000000000000000000000000000000000000000000000F02700000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000044000D000100460069006C0065004400650073006300720069007000740069006F006E00000000005700720069007400650058004D004C00460069006C00650000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D006500000058004D004C005500740069006C002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310032000000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000058004D004C005500740069006C002E0064006C006C0000003C000D000100500072006F0064007500630074004E0061006D006500000000005700720069007400650058004D004C00460069006C00650000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
 WITH PERMISSION_SET = EXTERNAL_ACCESS
 GO
 
 CREATE FUNCTION [dbo].[WriteXMLFile](
  @XMLData nvarchar(max),
  @FullFilePath nvarchar(500)
 )
 RETURNS [int]
 WITH EXECUTE AS CALLER
 AS --         [assembly].[namespace.class].[method]
 EXTERNAL NAME [XMLUtil].[XMLUtil.XMLFunctions].[WriteXMLFile]
 GO

Once the above are created run these SQL statements:

 DECLARE @FilePath varchar(500)
 
 SELECT @FilePath = [File_Location]       
 FROM [INTERFACE_CONFIGRATION]      
 WHERE [Message_Name] = 'INVOICE'
 
  SELECT dbo.WriteXMLFile(
         '<ns0:Stolt_FF_AP_INVOICE xmlns:ns0="http://InvoicePayable.PublicSchemas.InvoicePayable_XML/v1.0">'
       + '<APInvoice>'
       + '<INVOICE_NUM>' + iht.INVOICE_NUM + '</INVOICE_NUM>'      
       + '<INVOICE_TYPE_LOOKUP_CODE>' + iht.INVOICE_TYPE_LOOKUP_CODE + '</INVOICE_TYPE_LOOKUP_CODE>'      
       + '<GL_DATE>' + CONVERT(varchar(10),iht.GL_DATE,110) + '</GL_DATE>'      
       + '<ACCTS_PAY_CODE_SEGMENT1>' + iht.ACCTS_PAY_CODE_SEGMENT1 + '</ACCTS_PAY_CODE_SEGMENT1>'      
       + '<ORG_ID>' + iht.ORG_ID + '</ORG_ID>'      
       + CONVERT(varchar(MAX),
         (SELECT ilt.LINE_NUMBER,      
                 ilt.LINE_TYPE_LOOKUP_CODE,      
                 ilt.AMOUNT,      
                 ilt.ORG_ID      
          FROM INVOICE_LINE_TABLE ilt
          WHERE ilt.INVOICE_ID = iht.INVOICE_ID 
          FOR XML PATH('APInvoiceLine'),TYPE))
       + '</APInvoice></ns0:Stolt_FF_AP_INVOICE>',
       @FilePath + '\' + CONVERT(varchar(36), NEWID()) + '.xml')
  FROM T_INTERFACE_CNTL_MASTER ticm
  JOIN INVOICE_HEAD_TABLE iht ON (iht.INVOICE_ID = ticm.TRAN_TYPE_ID) 
  WHERE ticm.PROCESSED_STATUS = 'Y'  

The code contained in the CREATE ASSEMBLY statement above is simply:

 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Xml.Linq;
 using System.Data.SqlTypes;
 using Microsoft.SqlServer.Server;
 
 
 namespace XMLUtil
 {
     public class XMLFunctions
     {
         [SqlFunction(IsDeterministic = true)]
         public static int WriteXMLFile(SqlString XMLData, SqlString FullFilePath)
         {
             XDocument doc = XDocument.Parse((String)XMLData);
             doc.Save((string)FullFilePath); 
             
             return 1;
         }
     }
 }

more ▼

answered Aug 04, 2012 at 08:00 PM

avatar image

Scot Hauder
6.4k 13 16 22

(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.

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:

x68

asked: Aug 02, 2012 at 06:55 AM

Seen: 790 times

Last Updated: Aug 04, 2012 at 08:00 PM

Copyright 2016 Redgate Software. Privacy Policy