How to convert coursor to set based operetion ?

Question

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;

Answer 1

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;
        }
    }
}

How to convert coursor to set based operetion ?

Follow this question

Related Questions