ETL and the Case Against a Single System for All Business Processes

Nearly every business wants to centralize their business data so that they can get an at-a-glance overview of what is going on at any given point in the day. Suppose, for instance, a company has its business data on 5 separate database servers, in various databases therein, across several different tables. And much of the data will need to be slightly modified (transformed) in order to make data types consistent and optimize it for quick and meaningful data analysis.

Assume the database servers are:

• CMS, Orders (Dynamics 365 data)
• Accounting (Sage 50 data)
• Warehouse, Logistics (SAP data)
• Field Service, Product Support (SalesForce data)
• Call Center (TantaComm data)

Furthermore, the business wants to apply some custom logic to various fields so that certain calculations don't have to be made by the OLAP end users and applications.

ETL is just moving data from a Source to a Destination, often making some changes (transformations) along the way

Well, it all seems so complicated, right? How can all that disparate data be combined and standardized? It's not as complicated as allowing an ERP solution define how your various departments do their job. Some ERP products truly do have the functionality to satisfy 80-85% of the company business requirements. And when that is the case, by all means, companies should go that route.

But for instances where ERP of every business function is not working, you can use products like SSIS, Informatica, and Oracle Data Integrator to easily map ETL data visually and apply code operations to the data using a variety of programming languages.

But of course, there is no "one-size-fits-all 100% of our business needs" ERP solution; different business productivity software work better or worse for different industries, different departmental needs, for all manner of different reasons. Why care about the choice of business tool that your business' management and best minds know and have proven is most effective for their- often very specific- job?

Allowing a business to integrate the "best apps for each particular type of job" is nothing more than a sort of microservices architecture. But if we are working with custom code in these various LOB applications (at least some of the LOB apps will need custom API integrations so that they can talk to each other), with each new block of custom code, comes more decentralized code bases and decentralized test scripts...

All considered, I would argue that trying to put square pegs into round holes a la "ERP-everything" is a much, much, MUCH bigger effort than a well-developed and tested scheduled ETL Data Warehouse refresh process to centralize your company's data.

It's always a balance with software. Use the most effective apps for your organization and departmental requirements, but try to avoid the dependency hell that can come along with a poorly managed distributed application environment.

Here are the ETL steps in order, from the resource link below:

Extract the data from the external data sources such as line-of-business systems, CRM systems, relational databases, web services, and anywhere you have business data you want to summarize.

Transform the data. This includes cleansing the data and converting it to an OLAP-friendly data model. The OLAP-friendly data model traditionally consists of dimension and fact tables in a star or snowflake schema.

Load the data so that it can be quickly accessed by querying tools such as reports, dashboards, etc.

"90% of the requirements now is better than 100% of the requirements never" -Unknown

Reference: https://blogs.msdn.microsoft.com/andreasderuiter/2012/12/05/designing-an-etl-process-with-ssis-two-approaches-to-extracting-and-transforming-data/

SOLID

"These principles, when combined together, make it easy for a programmer to develop software that are easy to maintain and extend." -Robert 'Uncle Bob' Martin

S - Single-Responsibility - objects should only serve one purpose

O - Open-Closed - types should be open to extending capabilities, closed to base changes

L - Liskov Substitution - subtypes of base types should produce objects compatible with base

I - Interface Segregation - client types do not have to depend on unused interface methods

D - Dependency Inversion - rely on abstract templates, not prescriptive/restrictive concretes



Reference: https://scotch.io/bar-talk/s-o-l-i-d-the-first-five-principles-of-object-oriented-design

Levity on the Never-ending Race Towards the Next Big IT Trend

Because, c'mon now- at some level, every one of us can relate to 1 or both of these guys 😆

Reference: https://www.explainxkcd.com/wiki/index.php/198

Log Shipping and Log (and/or Differential Backup) Restoration

Most developers know what a full database backup is. Did you know a differential backup is just a subset of the full backup? But what is the point of transaction log shipping in SQL Server?

A "transaction log" is just a subset of a subset of a full backup containing a set of all T-SQL transactions for a given (usually short span) amount of time; they are typically set at intervals of every few minutes but this varies according to application and data compliance needs.

A commenter in the referenced web link below aptly notes:

"the point of log shipping is to keep the secondary server closer to 100% synced than the full backups (which are much larger and produced much less frequently than log backups)."

Log shipping can be used to serve as a constant "warm backup" (similar to SQL Server database mirroring) of all production transactions for a standby/failover SQL Server* as well as a dedicated Backup SQL Server as depicted here:

 Setup if you have a fully-functioning Secondary Production SQL Server

Or, more commonly, the shipped backup logs are sent to a single Backup Server:

Typical production database server scenario, though the steps are a bit out of sequential order, if not (or due to) visual order

In short, a full backup contains the entire database you to recover- up to a certain point in time.

A differential backup is a subset of a backup (generally "full" backups contain days worth of data for bigger organizations)- it would only be prudent to include one or even four differential backups to ensure quick disaster recovery (and full recovery of vital customer data).

Backup logs are chunks of SQL transactions that eventually compose the differential backup(s) and finally the entire full .bak file.

These 3 disaster recovery tools that SQL Server affords DBAs and developers allow you to restore your production data back to the point in time when your server failed:

• Apply the most recent full backup of the prod server that went down*
• Apply any differential backups since the most recent full backup
• Apply all transaction logs up to the point in time of the failure**
• Apply all StandBy server transactions logs that may have occurred (if not ReadIOnly)

With all of this said, do be aware that different scenarios and requirements call for different kind of disaster recovery solutions. Consult with the experts and never neglect to TEST YOUR RESTORE PROCEDURE WITH ACTUAL .bak and .trn files- and then test the applications and services connected to those database servers against their restored/Secondary version to ensure you suffer as little downtime as possible in the event of a database emergency.

What a restoration timeline might look like

*Note(!) to the questions on Microsoft Licensing and your StandBy server, should it need to be used for any production activity during your disaster recovery:

"I am not the licensing police, and I am not Microsoft – check with your licensing representative to clarify your exact situation. Generally, you can have one warm standby server. However, the second someone starts using it for reporting, testing, or anything else, you need to license it like any other server."-Brent Ozar

**"If the database is in Standby mode, users can query it – except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected." -Brent Ozar

References:

https://www.sqlshack.com/sql-server-disaster-recovery/ (SQL Server Replication is another compliment or alternative to the log shipping and backup/restore)

 https://www.brentozar.com/archive/2013/03/log-shipping-faq/

Blockchain

Blockchain, apart from all the hoopla heralding it as a "cryptic" means of facilitating monetary transactions, is essentially just a distributed digital ledger (multiple sources of the exact same truth) and distributed digital notaries (transaction verifiers) who, unlike human money handlers and human notaries- do not lie.

Transactions can be scheduled via any desired logic (ie. loan repayment terms, payroll distribution, annuity payouts, securities purchase/sale/options, etc.) and encryption lies at the foundation of the technology- ensuring data integrity and information security.

Blockchain is a type of DLT design. DLT is well-illustrated below:

© 2016 LPEA

Much like Git commits, each blockchain transaction is forever tied to an unmodifiable unique SHA-256 hash sum value that ensures the immutability (un-changeability) of each transaction- in brief, if the transaction changes, the original SHA-256 hash sum (unique file signature) that has already been distributed to all nodes of the blockchain- will not match the modified transaction's SHA-256 hash sum, and everyone connected to the blockchain will be able to see that someone is trying to commit a fraudulent transaction modification. So changes to already-posted transactions within a blockchain digital ledger (in theory at least) are virtually impossible.

Reference Video: https://anders.com/blockchain/

Temporal Tables in SQL Server

Definition from Microsoft: SQL Temporal Tables, also known as "system-versioned tables" are a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

What is it used for?
1). Auditing Data History- identifying who did what, when, and row-level restoration to the state of the temporal table data any point in time. (Time travel)

2). Monitor Data Deltas (ie. maintain company sales discounting policy by ensuring no product has had a price change that exceeds some company threshold (200% increase or decrease, etc.))

3). Slowly Changing Dimension management (eliminate the need for complicated ETL logic for tracking SCD- SQL Temporal Tables do this audit tracking automatically)

In Practice
Once upon a time, I worked on a project that had a "unique" (kind euphemism) legacy database schema design. This company had a custom "system" for tracking changes in data. There was no referential integrity so everything relied on application code not having any data access code bugs, which as we all know is not a particularly reliable setup.

Temporal Tables would have done the trick without all the complexity of having to manage a proprietary data versioning system that sometimes worked, oftentimes caused headaches and mass confusion over the root cause of production issues related to versioning of certain data entities.

Temporal Tables have the current or base table, as well as a history table that logs changes over time

Unless there is a specific reason that SQL Temporal Tables won't work for your scenario* you should take advantage of existing functionality like this so that you can focus on the unique functionality of your app and not waste precious time reinventing wheels.

Creating a temporal table is relatively straightforward. In SSMS (connected to an instance of SQL Server 2016 or later), select the option as seen below.

Menu option in SSMS

Here is the T-SQL for basic Temporal Table DDL:

 USE [KpItSimpl]  
 GO  
 --Create system-versioned temporal table. It must have primary key and two datetime2 columns that are part of SYSTEM_TIME period definition  
 CREATE TABLE dbo.SimplTemporalTable  
 (  
      --Table PK and columns definition  
   ProductID int NOT NULL PRIMARY KEY CLUSTERED,  
   ProductDescription varchar(max) NULL,  
      ProductPrice decimal,  
   --Period columns and PERIOD FOR SYSTEM_TIME definition  
   ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START,   
   ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END,  
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)   
 )  
  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimplTemporalTableHistory));   
 GO  

T-SQL DDL for creating a new Temporal Table

For a practical example, I inserted some data into this temporal table, and then committed some UPDATE statements on that data, changing the ProductPrice value of some of the product records. The query below demonstrates how we can look for records (we are likely most interested in the exact change time) that had a price change exceeding +/- 200%:

Example of a query utilizing the Temporal Table feature to see an excessive product price change

Note(!) one limitation that stands out right away- you cannot drop a temporal table like a standard SQL table. You must turn versioning off on the table, drop the table and drop the history table. So instead of selecting "Delete" from the Table menu options or executing "DROP TABLE", you will want to select "SCRIPT AS >> DROP To" option as seen below:

You must script the deletion/drop of a SQL temporal table

As you can see, SQL Temporal Tables can make managing data changes a lot easier. Certainly much easier than a proprietary change versioning system and much faster than restoring the entire database to inspect the state of data at a particular point in time.


*Temporal Table Considerations and Limitations: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations?view=sql-server-2017

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-2017

Binary Notation

What is it? 
"In mathematics and digital electronics, a binary number is a number expressed in the base-2 numeral system or binary numeral system, which uses only two symbols: typically 0 and 1." - Wikipedia

Binary Notation Formula
From right to left, starting with the rightmost binary digit index = 0, multiply each binary digit by 2-to-the-power-of-its-index and sum the results.

See the basic math breakdown of how binary 011001 represents the sum of these 6 expressions:

0*2^5 = 0
1*2^4 = 16
1*2^3 = 8
0*2^2 = 0
0*2^1 = 0
1*2^0 = 1 

...which sum to a decimal-based system (also called Hindu-Arabic, or Arabic system) number of 25

Why does any number to the 'zero' power equal 1? A good and simple explanation can be found in Answer #4 here: http://scienceline.ucsb.edu/getkey.php?key=2626

Answer 4: Let's look at what it means to raise a number to a certain power: it means to multiply that number by itself a certain number of times. Three to the second power is three multiplied by itself 2 times, or 3*3=9. Let's look at a few examples:35 = 3*3*3*3*3 = 24334 = 3*3*3*3 = 8133 = 3*3*3 = 2732 = 3*3 = 931 = 3 = 3But how do you go from 31 to 30? If you look at the pattern, you can see that each time we reduce the power by 1 we divide the value by 3. Using this pattern we can not only find the value of 30, we can find the value of 3 raised to a negative power! Here are some examples:30 = 3/3 = 13-1 = 1/3 = 0.3333... (this decimal repeats forever)3(-2) = 1/3/3 = 0.1111...3(-3) = 1/3/3/3 = 0.037037...

See this useful binary reading tool below for your own practice: http://www.advanced-ict.info/interactive/binary.html

SSRS Extension Methods - The 3 Levels

When developing SSRS reports, if you are unable to achieve what you want through SQL and RDL alone, there are essentially 3 ways you can add custom functions/functionality to SSRS.

1). Inline SSRS "Expressions". (VBScript code)

2). Report.Code references (VB code)

3). Custom Assembly references (C# or VB .NET .dll)

The following is a brief example of each.

1). Inline SSRS Expressions
In SSRS, virtually every report property can be dynamic. The way to set the dynamic behavior of any report item is through SSRS Expressions which are simply VBScript code blocks that evaluate some logic against the report data and return the property value at runtime.

For instance, here we are setting the Font >> FontWeight property of the Order Date column for an Orders report (AdventureWorks sample data). If the Date is more than 1 year in the past, this inline expression sets the FontWeight of the OrderDate field to bold:

Method 1 Code

Method 1 Result

You can use IIF(), Choose(), Switch() or any valid VBScript syntax. The report field values are referenced as Fields!OrderDate.Value. As in the example above, you will oftentimes need to cast the report dataset data to the data type which you are evaluating (a cast to CDate in this example as we are evaluating a Date).

2). Report Code references (VB code)
This method is similar to Method #1, but it allows you to define a VB Code space where you can define functions in a central place and reference them any number of times throughout your report.

For this example, let's say we need to have a report function that takes a string as a parameter and then evaluates the length of that string. If the string value is over 10 characters long, it sets the Color to Red, if not, the color is the default Black. Your Report Code methods are defined in: Report >> Report Properties >> Code:

Method 2 Code

Method 2 Reference

Method 2 Result

3). Custom Assembly references (C# or VB .NET .dll)
This is the most comprehensive of the 3 methods and allows you to do anything that you could normally do with a .NET assembly.

For illustrative purposes, let's say we have a need to interface with a backend system and get a URI which will fill a report image item with the appropriate image from an external server that is not connected to the Report Server. You can implement the necessary .NET logic and API calls, etc. in your custom assembly and then reference that .dll in your report:

 namespace SSRSCustomAssembly  
 {  
   public class ErpReferenceLib  
   {  
     public static string GetImageForCustomerAccount(int customerID)  
     {  
       //this example is static; in real-world implementation the customerID might fetch value from db or API  
       bool isActiveAndValid = CheckCustomerIDIsValidAndActive(customerID);  
       if (isActiveAndValid)  
       {  
         return "https://abccorp.imagesrv?customerID=" + customerID + ".svg";  
       }  
       else  
       {  
         return "https://abccorp.imagesrv?inactive.svg";  
       }  
     }  
     public static bool CheckCustomerIDIsValidAndActive(int customerID)  
     {  
       return customerID >= 100 ? true : false;  
     }  
   }  
 }  

Method 3 Code

Method 3 Report Reference


Method 3 Report Item Reference- NOTE: You cannot use namespaces separated with ".", ie: MyNamespace.Subnamespace

Method 3 Visual Studio Report Design View

Method 3 Result in VS2017 Preview Mode

Method 3 Result on local Report Server

For more information (esp. how to do the necessary custom assembly security permission config changes, for creating object instances vs. static methods, etc.), see the relevant references below.

Note(!): your custom assembly must have permission to run within Visual Studio in order to Preview a report that calls a custom assembly method and it must also have permission within Report Server. You can grant it FullTrust or whatever CAS group you deem is appropriate. The paths to deploy the .dll are as follows (may vary by SQL Server and Visual Studio version- this is for SQL 2017 and VS2017):

Visual Studio Report Designing/Preview assemblies*: C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\CommonExtensions\Microsoft\SSRS

Report Server assemblies*: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin

GitHub for SSRS Project: https://github.com/Radagast27/CustomSSRS

GitHub for SSRS Custom Assembly: https://github.com/Radagast27/CustomAssembly


*https://www.mssqltips.com/sqlservertip/3224/sql-server-reporting-services-custom-code-assemblies/

Serialization and Deserialization in .NET

Definition: "Serialization is the process of going from in-memory representation of an object to a disk-based format which can be in any form like binary, JSON, BSON, XML, etc. Deserialization is the reverse process, in which you recreate an object from disk storage and store it in RAM"

Purpose: "This is mainly used in document store, ORM style databases, storing config files etc. The main benefit is the speed in populating an object as opposed to querying multiple tables in the case of database storage."

Serialization saves object state so that the object can be persisted and recreated without the need for inserting/updating one or more records across several tables in an RDBMS.

.NET Example: The following is an example of serialization of a simple C# object to XML, and deserialization from the XML back into the same C# object:

 using System;  
 using System.IO;  
 using System.Xml;  
 using System.Xml.Serialization;  
 namespace SerializationDeserializationSimpl  
 {  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       Album album = new Album(){ AlbumId = 1, AlbumName = "James Vincent McMorrow", ArtistName="Post Tropical", CurrentBillboardRank=1045, HighestBillboardRank=102, SoundScanUnits=207000};  
       Console.WriteLine("Original object in memory:");  
       album.WriteSummary();  
       Console.WriteLine("Press Enter to Serialize this object to XML");  
       Console.ReadLine();  
       //Serialize it to XML file (disk) form  
       var serializer = new XmlSerializer(album.GetType());  
       using (var writer = XmlWriter.Create("album.xml"))  
       {  
         serializer.Serialize(writer, album); //serializes to XmlWriter for later deserialization back into Album obj  
         var sWriter = new StringWriter();  
         serializer.Serialize(sWriter, album); //serializes to StringWriter for display in the Console via WriteLine  
         Console.WriteLine(sWriter.ToString());  
         Console.WriteLine("------------------------------------");  
         Console.WriteLine("------------------------------------");  
         Console.WriteLine("------------------------------------");  
       }  
       Console.WriteLine("Serialization to XML sucessfull!");  
       Console.WriteLine("------------------------------------");  
       album.WriteSummary();  
       Console.WriteLine("------------------------------------");  
       Console.WriteLine("Press Enter to Deserialize from the XML");  
       Console.ReadLine();  
       //Deserialize from that XML back into object (RAM) form  
       using (var reader = XmlReader.Create("album.xml"))  
       {  
         var albumDeserialized = (Album)serializer.Deserialize(reader);  
         Console.WriteLine("Deserialization from XML sucessfull!");  
         Console.WriteLine("------------------------------------");  
         albumDeserialized.WriteSummary();  
         Console.WriteLine("------------------------------------");  
         Console.WriteLine("Press any key to exit");  
         Console.ReadLine();  
       }  
     }  
   }  
   public class Album  
   {  
     public int AlbumId;  
     public string AlbumName;  
     public string ArtistName;  
     public int SoundScanUnits;  
     public int CurrentBillboardRank;  
     public int HighestBillboardRank;  
     public void WriteSummary()  
     {  
       Console.WriteLine("AlbumId: " + AlbumId);  
       Console.WriteLine("Album: " + AlbumName);  
       Console.WriteLine("Artist: " + ArtistName);  
       Console.WriteLine("SoundScanUnits: " + SoundScanUnits);  
       Console.WriteLine("CurrentBillboardRank: " + CurrentBillboardRank);  
       Console.WriteLine("HighestBillboardRank: " + HighestBillboardRank);  
     }  
   }  
 }  

Program.cs

Console Output

GitHub: https://github.com/Radagast27/SerializationDeserializationSimpl

Quote Attribution: Mehdi Gholam

References:

https://stackoverflow.com/questions/11447529/convert-an-object-to-an-xml-string?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

https://www.codeproject.com/Questions/277995/What-is-serialization-and-deserialization-in-cshar

https://stackoverflow.com/questions/3356976/how-to-serialize-deserialize-simple-classes-to-xml-and-back

T-SQL STUFF, STRING_SPLIT

In cases where you must extract a series of values from a delimited string, you can utilize SQL Server's built-in string_split() function. You need only specify the delimited string and the delimiting character (commas in this case ",") as the arguments.

When a requirement calls for you to roll up data into a single row, you can use STUFF() (with or without combined with FOR XML, depending on your data). Just specify an inner query as the argument for STUFF().

 --Get ids from a csv of ids  
 SELECT * FROM string_split('100,101,201,301,411,414', ',')  
 

--Push a series of values into one row  
 SELECT playerID,  
 STUFF((select '; ' + teamID  
      from Managers  
      where teamID like('%P%')  
            group by teamID  
      for xml path('')), 1, 1, '') allTeams  
 FROM Managers  

Result of these functions

Application Walkthroughs with Anno.js

If you need to provide your users a quick intro to your app or new features that you have just released, you might want to give Anno.js a try. Of the several frameworks I sampled, this is was the sharpest looking and easiest to work with (for me, maybe for you too).


To configure:

1). Link to or host the required files in the DemoAnno.html <head> (see code on GitHub).

2). Configure the annoJS object by setting targets (by element selector, '#demo', '.walkDivs', etc.)

3). Create a button and set its onClick() == anno.show(); //anno, or the name of the annoJS object


And in 3 simple steps, you have a walkthrough:

All credit to @iamdanfox who created this great .js plug in. Here is the htm page source code, see references below for more:

 <html>  
 <head>  
 <script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>  
 <script src="anno.js" type="text/javascript"></script>  
 <script src="jquery.scrollintoview.min.js" type="text/javascript"></script>  
 <link href="anno.css" rel="stylesheet" type="text/css" />  
 </head>  
 <body>  
 <div style="background-color='gray'; margin-left:33%;">  
 <b><i>Use this guided tour to see our latest features!</i></b>  
 <br/>  
 <button type="button" class="btn btn-default" onclick="anno.show();">Show Tour</button>  
 <br/>  
 <br/>  
 <div id="demo" style="width:27%;">  
 Your new login button is here: <button name="LOGIN" value="LOGIN">LOGIN</button>  
 <div>  
 </div>  
 <br />  
 </body>  
 <script>  
      var anno = new Anno([{  
       target: '#demo',  
       position:'left',  
       content: "This is our shiny new left div edge!",  
       buttons: [AnnoButton.NextButton]  
      }, {  
       target: '#demo',  
       position:'right',  
       content: "...and on the right...",  
       buttons: [AnnoButton.BackButton]  
      }]);       
 </script>  
 <html>  

Note the NextButton and BackButton- these are very useful for app walkthroughs

GitHub: https://github.com/Radagast27/AnnoJS_WalkthroughDemo

References: http://iamdanfox.github.io/anno.js/

https://github.com/iamdanfox/anno.js#usage

T-SQL and .NET for XSLT

Steps to perform transformation from SQL to XML to XSLT Output data (csv, htm, tab-delim, fixed-width, etc.):

1). Get your SQL data (assuming you are using SQL Server) into XML format via:

 SELECT [OrderId], [PersonId], [OrderDateTime] FROM [Order] FOR XML AUTO, ELEMENTS, ROOT ('Orders');  

XML Result of "FOR XML AUTO, ELEMENTS, ROOT"..

2). Load XSL transform file (OrderXform.xsl) into a new XslCompiledTransform object (transform):

       XslCompiledTransform transform = new XslCompiledTransform();  
       transform.Load("C:\\Xform\\OrderXform.xsl");  

XSL file used to transform the XML to the desired file output (EDI 834 standard, etc)

3). Using a StringWriter (writer), StringReader (inputReader) and XmlReader (inputXmlReader), read the inputReader XML string into inputXmlReader and apply the XSL transformation to the transform object via: transform.Transform(inputXmlReader, args, writer); //(or another override of Transform())

  using (StringWriter writer = new StringWriter())   
     {   
      // Make an XML reader (inputReader) out of the string (input)  
      XmlReader inputXmlReader;   
      using (var inputReader = new StringReader(input))   
      {   
       inputXmlReader = XmlReader.Create(inputReader);   
       // Apply the transformation to XmlReader (inputXmlReader) and write it to StringWriter (writer)   
       transform.Transform(inputXmlReader, null, writer);   
      }      
      // Retrieve the output as string from writer object  
      return writer.GetStringBuilder().ToString();   
     }   

4). Return the result and write the bits (to memory as Label Text for a demo like this, to file, to domain network location, to non-domain customer ftp, etc.).

The result normally gets written to remote SFTP location or posted to API endpoint for processing

Nothing incredibly complex about this, but it seems to frustrate a lot of people in .NET land so I have documented a quick and easy example.

GitHub Source: https://github.com/Radagast27/Simpl_XSLT_dot_net

Tool for XSLT validation: https://www.freeformatter.com/xsl-transformer.html

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://www.w3schools.com/xml/xsl_examples.asp

https://www.c-sharpcorner.com/article/transform-xml-output-of-sql-query-using-for-xml-auto-statement-to-html-using-xsl/

Dynamic SQL

"a programming technique that enables you to build SQL statements dynamically at runtime." -Oracle Docs

Dynamic SQL is a very useful tool but should only be used when absolutely necessary. Having SQL compiled at runtime amplifies the threat of SQL injection and the readability of the SQL code (dynamic SQL SPs result in large double and quadruple quoted red text which can make things much more difficult to read than when reading regular highlighted SQL code).

But when circumstances require you to use dynamic SQL to get the data you need (ie. there is no other way to structure your database environment and/or the "dynamism" can't be done equally as easy or easier using some sort of data adapter like .NET CLR code) dynamic SQL may be your best option.

For more in-depth info on this subject, see this great reference from SQL Server MVP Erland Sommarskog on this subject and the pros and cons of using dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Here is a brief example of dynamic SQL:

 DECLARE @mult bit = 0  
 DECLARE @sqlToExecute varchar(500)  
 DECLARE @srvWest varchar(50) = 'WestZoneSqlSrv08'  
 DECLARE @srvEast varchar(50) = 'EastZoneSqlSrv08'  
 IF @mult = 0  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ALGO.dbo.[Order]'  
 ELSE  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ' + @srvEast + '.ALGO.dbo.[Order] UNION ALL SELECT TOP 1 OrderID, PersonId FROM ' + @srvWest + '.ALGO.dbo.Order'   
 EXEC(@sqlToExecute)