Simplest way to query XML in MSSQL

For Accessing XML nodes:

DECLARE @xml xml
SET @xml = N'
         1002008-09-10
         1012008-09-11
       '
SELECT
doc.col.value('ponumber[1]', 'nvarchar(10)') ponumber
,doc.col.value('podate[1]', 'datetime') podate
FROM @xml.nodes('/polist/po') doc(col)

For Accessing element attributes:

DECLARE @xml xml
SET @xml = N'
         
         
       '
SELECT
doc.col.value('@ponumber', 'nvarchar(10)') ponumber
,doc.col.value('@podate', 'datetime') podate

FROM @xml.nodes('/polist/po') doc(col)

For accessing XML arrays:

DECLARE @xml xml
SET @xml = N'
         100
         101
       '
SELECT

Alternate Value of two fields in one column

I just found a very good tool that can find and replace any files that I selected with the set of old and new values I added all at the same time. I can even select a batch of folders to look for and millions of text that needs to be replaced with just a blink of an eye. This solves our problem with Hyperion where we need to migrate all the reports from an old system to a new system where with an all new sets of account TRULY HORRIBLE. As a programmer we don't want just to sit in my computer and replace each of the account with the new account manually. So when we found a way to automate the conversion we are all so happy that a report that can be converted in one whole day now can be converted in just a few minutes.

The ReplaceText Tool have an 'Import Replace Table' where you can import the set of old value and new value to its table in a text format.

But the problem is the text format is just one column where the old and new values just alternates on each line.

The challenge is how can I write a query where I could have one columns and the value of the old and new alternates on the row.

As fast as the Replace Text tool I have write the solution in SQL script:

SELECT Field FROM
((SELECT '>'+ OldAccount + '<' AS Field,
(((ROW_NUMBER() OVER(Order by ID))-1)*2 + 1) AS FieldOrder
FROM ConvertedAccounts)
UNION ALL
(SELECT '>'+NewAccount + '<' AS Field,
(((ROW_NUMBER() OVER(Order by ID)))*2) AS FieldOrder
FROM ConvertedAccounts)) A
ORDER BY FieldOrder
END

Add abilities to your Objects with Decorator Pattern

From the word decorator, it adds new functionality (Decoration) to the operations of your instantiated object. If you will try to analyze the design it is like specializing a class through inheritance but here we are doing it on runtime. The Idea is to have an abstract class that will be the base type of all the components. As you instantiate the object you are passing an object of the same type and return a newly instantiated object of the same base type but has additional process on the methods that is being decorated. As you continue on decorating the object by instantiating another decorator object, the functionality accumulates. recursion happens by calling an overidden method until it goes to the first base class that was instantiated.

To understand the concept, imagine += operator in C# which accumulates the sum of numbers:

A = 5;
B = 10;
C = 15;

A += B;
A += C;

on the last line A has a component of B and C (5 + 10 + 15) which we accumalate by the += operator.

Let us see now the design in Action. Below is the UML diagram of the DatabaseProvider Decorator I created:

Click Image to Enlarge

Before I adapted the Decorator Pattern here, the original purpose of the DatabaseProvider is to Provide a Template (IDatabase) of basic Database Execution for all kinds of database. With that, I can use this with my DatabaseProviderFactory without even worrying on what connection I am using. As the need arises, I have thought of a way of discovering Parameters of the stored procedure then extracting the values for that parameter on the Entity being passed on the decorator object. Actually this design is just part of my own MVC implementation which I might also discuss in the future.

Let us see now the codes :

Here is the IDatabase Interface which provides the template for all kind of Database

public interface IDatabase : IDisposable
{
string CommandText { get; set; }
CommandType CommandType { get; set; }
string ConnectionString { get; set; }

void AddParameterWithValue(string parameterName, object value);
void AddParameter(IDataParameter parameter);
IDataReader ExecuteReader();
int ExecuteNonQuery();
object ExecuteScalar();
DataSet ExecuteDataset();
List<T> ExecuteToEntity<T>(T instance) where T : IEntity<T>;

}

This next class is the Decorator Abstract Class which the main functionality is to save an object of IDatabase, which is also his type, that is passed on the constructor.

public abstract class DBDecorator : IDatabase
{
protected IDatabase _database;
public DBDecorator(IDatabase database)
{
_database = database;
}
public string CommandText
{
get{return _database.CommandText;}
set{_database.CommandText = value;}
}
public CommandType CommandType
{
get{return _database.CommandType;}
set{_database.CommandType = value;}
}
public string ConnectionString
{
get{return _database.ConnectionString;}
set{_database.ConnectionString = value;}
}
public virtual void AddParameterWithValue(string parameterName, object value)
{
_database.AddParameterWithValue(parameterName, value);
}

public virtual void AddParameter(IDataParameter parameter)
{
_database.AddParameter(parameter);
}
public virtual IDataReader ExecuteReader()
{
return _database.ExecuteReader();
}
public virtual int ExecuteNonQuery()
{
return _database.ExecuteNonQuery();
}
public virtual object ExecuteScalar()
{
return _database.ExecuteScalar();
}
public virtual DataSet ExecuteDataset()
{
return _database.ExecuteDataset();
}
public virtual List<T> ExecuteToEntity<T>(T instance) where T : IEntity<T>
{
return _database.ExecuteToEntity(instance);
}
public void Dispose()
{
_database.Dispose();
}
}

Now the last class adds two new private methods, DiscoverParameter and AssignParameter which is being called before the execusion of the actual Base Class.

public class SQLDatabaseDecorator<T> : DBDecorator
{
private List _parameterNames = new List();
private T _entity;

public SQLDatabaseDecorator(IDatabase db, T entity)
: base(db)
{
_entity = entity;

}
private void DiscoverParameter()
{
PersistenceManager<list<string>> parameterNamesPM =
new PersistenceManager<list<string>>(PersistIn.Application,
CommandText + "_SQLDatabaseDecorator");
if (parameterNamesPM.Exists())
{
_parameterNames = parameterNamesPM.Get();
}
else
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = this.CommandText;
command.CommandType = this.CommandType;

SqlCommandBuilder.DeriveParameters(command);

foreach (SqlParameter param in command.Parameters)
{
_parameterNames.Add(param.ParameterName);
}
parameterNamesPM.Add(_parameterNames);
}
}
}
private void AssignParameters()
{
foreach (string paramName in _parameterNames)
{
if (_entity.GetType().GetProperties().ToList()
.Exists(x => ("@" + x.Name.ToUpper()) == paramName.ToUpper()))
{
PropertyInfo pi = _entity.GetType().GetProperties().ToList()
.Find(x => ("@" + x.Name.ToUpper()) == paramName.ToUpper());
base.AddParameterWithValue(paramName, pi.GetValue(_entity, null));
}
}
}
public override IDataReader ExecuteReader()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteReader();
}
public override int ExecuteNonQuery()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteNonQuery();
}
public override object ExecuteScalar()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteScalar();
}
public override DataSet ExecuteDataset()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteDataset();
}
public override List<U> ExecuteToEntity<U>(U instance)
{
DiscoverParameter();
AssignParameters();
return base.ExecuteToEntity(instance);
}
}

Now the classes are ready, here is how we use the decorator objects :

Company InsertCompany = new Company();
InsertCompany.CompanyName = "My Company";
InsertCompany.Address = "Global City Taguig";
InsertCompany.PhoneNumber = "111-11-11";
InsertCompany.UserId = 1;

string connectionString = "Data Source=.;Initial Catalog=GE;
Integrated Security=false;User Id=sa;Password=password;";

IDatabase target = new SQLDatabaseProvider(connectionString);

using (target = new SQLDatabaseDecorator(target, InsertCompany))
{

target.CommandText = "AddCompany";
target.CommandType = System.Data.CommandType.StoredProcedure;

InsertCompany.CompanyId = Convert.ToInt32(target.ExecuteScalar());
System.Console.WriteLine("New Company Id was inserted with Company Id {0}",
InsertCompany.CompanyId);

}

If you notice, I did not pass any parameters on the DatabaseProvider. The Decorator is doing it for us. It reads the parameter in the database then locate the values on the entity which in this case is the Company Object.

Happy Programming!!!

Integrated Information Management System

Description: Monitors chassis movement and produce inventory report and billing statements per client.

Type: Web Application

Tools:

• C#.Net
• MSSQL
• AJAX
• ASP.Net
• Crystal Report
  

Duration: October

Client: Transcom Cargo Services

Function: Freelance Project

Global Experts

Description: The Global Expert (GE) online platform is a web based collaboration package that targets consultancy service providers and gives them a convenient and intelligent means to service customers with real-time tools that include voice and imaging. As a complete collaboration package, the GE platform also offers sophisticated features like desktop and file sharing, remote desktop capabilities, white boarding, presence, and event notifications. Customers only have to use their internet browsers and go to the GE website to avail of the service.

Type: Web Application

Tools:

• C#.Net
• MSSQL
• AJAX
• ASP.Net

Duration: November 5, 2008 to Present

Function: Senior Web Developer

Company Attached with : Venzo Business Solution

Online Vessel Sounding Report

Description: A Web application that generates report of the measurements of vessels during and after loading of oil products. It also tracks vessels of there voyage.

Type: Web Application

Tools:

• C#.Net
• MSSQL
• AJAX
• ASP.Net
• Crystal Report
  

Client: Petron, Philippines

Involvement: Free lance project

Recursive Call in Stored Procedures

Recursion is a way of allowing a function to call itself. It results to an iteration of process. Let us have the simple example below that computes for the result of an exponent function:

static void Main(string[] args)
{
double result = exponent(3,4);
Console.WriteLine(result);
Console.ReadKey();
}

private static double exponent(double _base, double power)
{
double result= _base;
if (power > 0)
{
result = result * exponent(_base, power - 1);
}
else
{
result = 1;
}

return result;
}

This is the sample recursive call function in C#. Take note that in recursion there should be a way to terminate the recursion through conditions or else it will bring you to an infinite loop.

Now how can we implement this in SQL? It is allowed to call a stored procedure within a stored procedure. Unfortunately Stored procedure does not return values directly. In case of User Defined Function(UDF), it is not allowed to call a function within a function. Recursions should have a return value in order to determine how the loop will terminate.

The trick here is to use Temporary table to communicate with the calling stored procedure. For simplicity, let's use the same example in getting the result of an exponent:

ALTER PROCEDURE [dbo].[RecursiveCallExponent] (
@base float,
@power float
)
AS
BEGIN

IF(@power > 0)
BEGIN
UPDATE #Result SET Result = Result * @Base
SET @power = @power -1
exec RecursiveCallExponent @base, @power
END
END
GO
-------------------------

ALTER PROCEDURE GetExponent (
-- Add the parameters for the stored procedure here
@base float,
@power float
)
AS
BEGIN
CREATE TABLE #Result (Result float)
insert into #Result (Result) values (1 )
exec RecursiveCallExponent @base, @power

SELECT Result FROM #Result
DROP TABLE #Result
END
GO

GetExponent here is the main stored procedure call. Observe that The Temporary table is created in the main stored procedure to be used by the recursive procedure (RecursiveCallExponent). We cannot create it inside the recursive procedure for it will raise "Object exists" error since we are iterating inside it. Now we can run
"EXEC GetExponent(3,4)"
and will give you a result of 81.

I used recursive on my application to get all consultant with a specific skill in the Heirarchy of skillsets. The scenario is this, I have a Hierachical table which represents the Heirarchy of Skills, e.g. Words, Excel and Powerpoint under MSOffice and MSOffice, Explorer and Media Player under Windows. When I choose specific skills like MSOffice, I should be able to get all consultant under MSOffice including those consultant who has special skills for Powerpoint, Excel and Word and even those under it if it still have sub categories.