Click here to Skip to main content
14,874,239 members
Articles / Database Development / SQL Server
Posted 31 Jul 2005


28 bookmarked

Writing SQL queries in XML – A support intensive approach

Rate me:
Please Sign up or sign in to vote.
4.84/5 (13 votes)
31 Jul 2005CPOL2 min read
Article that helps writing SQL in XML to provide better support


Providing real time support in software’s industry is not easy. The experience gets worse when you have to provide support of buggy applications. I am providing support for around 3 years of such an application and the experience leads me to design SQL in such way that requires instant fixes if possible. Goals of the design are:

• We need to minimize the support time. That is if a bug has been identified in the system than this has to be fixed/sent to the client instantly.

• Fix should be sent without changing the code if possible

• Can accommodate missing functionality easily if possible


To achieve these goals the basic idea comes to mind is to use XML for SQL. Storing complete SQL Templates in SQL would seem an ideal solution for these kinds of applications. But storing Complex SQL in XML is not possible due to special characters restriction. For example I can not do this

    <SQL ID="GetBookings">

And then I thought how easy my life had be if I were able to do it in XML.

Once I finalize structure the rest becomes very easy. This structure helps me in achieving the goal.

• It reduces the compiled code size.

• It makes SQL code readable because otherwise I need to do so many string concatenation operations to make a SQL string.

• SQL debugging becomes very easy because now I don’t need to fetch the large SQL from SQL profiler then indent it properly to understand what it is actually doing

• I now have the flexibility to update the SQL (joins) without actually compiling the code

For example, Lets take a basic scenario (Support persons who understands what priority 1 issue meansJ), we released the product and a priority 1 issue comes that booking status have not been taken care of.

Luckily we have handled this scenario instantly. How, lets look into the solution:

    <SQL ID="GetBookings">
        AND BOOKINGDATE >= {0}

As you can see, we introduced new joins in the condition, added the filter criteria test the code and instantly sent the changed XML to client and the priority 1 has been resolved.


Below is the sample code that loads the XML and a sample client that uses it. Though I know this is very easy and lots of developers can code it in much better form. The idea here is to just provide an idea of how to implement the structure.


public class SqlManager
private StringDictionary Queries;
    private static SqlManager sqlManager = null;
    protected SqlManager()

    public static SqlManager Create()
        if (sqlManager == null)
            sqlManager = new SqlManager();
        return sqlManager;

    public void LoadSqlFile()
        string FName=ConfigurationSettings.AppSettings["XMLQuery"];
        XmlTextReader qr = new XmlTextReader(FName);

        if (Queries == null)
            Queries = new StringDictionary();

        string id = "";
        while (queryXMLReader.Read())
            switch (queryXMLReader.NodeType)
                case XmlNodeType.Element:
                    if (qr.Name.Equals("SQL"))
                        while (qr.MoveToNextAttribute())
                            if (qr.Name.Equals("ID"))
                            id = qr.Value;
                case XmlNodeType.Comment:
                    Queries.Add(id, qr.Value);
                    id = "";

The code shown above will load all the SQL present in the XML into a dictionary object; this is done to get fastest lookup time.

Below is the sample client code to use the SqlManager class and execute the query.

Public class BookingSearchAgent
    public static SqlManager QueryManager;
    public BookingSearchAgent()
        QueryManager = SqlManager.Create();

    Public DataSet GetBookingIds()
        string Query = QueryManager.GetQuery("GetBookings");
        Query = string.Format(Query,;
        DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, Query);
        Return ds;

The Ending

This is a very simple support intensive approach for handling similar situations and i like to have comments on this approach



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Architect Avanza Solutions
Pakistan Pakistan
Asif has started programming back in 1991 on 80286 8-16 MHZ systems. Started from dBase III+, FoxPro, C, assembly (exceptional skills in Assembly language have added a significant confidence in his development career). The programming saga continues exploring new technologies and languages ranging from C++, VC++, Java, Delphi, RPG400, SQL Server, Oracle to name a few and the exploration still continues to DOT Net Technologies, SOA architectures, BI, DSL, etc. These learning experiences are backed by strong theoretical background with a flavor of research.

Asif show significant interest in reading fiction, bio technology, Astronomy. He Loves watching movies and in free time love to play with his kids.

Comments and Discussions

QuestionHow to Work with Parameters Pin
Devkranth Kishore Vanja23-Mar-20 5:36
MemberDevkranth Kishore Vanja23-Mar-20 5:36 
QuestionqueryXMLReader Pin
Trupti.Samant26-Mar-15 4:19
MemberTrupti.Samant26-Mar-15 4:19 
AnswerRe: queryXMLReader Pin
AprNgp27-Jan-20 16:56
MemberAprNgp27-Jan-20 16:56 
QuestionGetQuery Pin
sarvanik4-May-13 4:38
Membersarvanik4-May-13 4:38 
Questionhow to write sql query in xml file Pin
vpooja27-Feb-12 20:19
Membervpooja27-Feb-12 20:19 
Generalgreat Pin
radioman.lt13-Aug-07 0:47
Memberradioman.lt13-Aug-07 0:47 
GeneralIs it Efficent Pin
sutha10-Mar-08 20:30
Membersutha10-Mar-08 20:30 
GeneralRe: Is it Efficent Pin
_Asif_23-Mar-08 2:25
professional_Asif_23-Mar-08 2:25 
GeneralRe: Is it Efficent Pin
Bhushan198023-Feb-10 20:30
MemberBhushan198023-Feb-10 20:30 
QuestionWhy not use CDATA? Pin
Anoop Pillai15-Mar-06 20:48
MemberAnoop Pillai15-Mar-06 20:48 
QuestionWhy don&#180;t you use strored procedures? Pin
machocr1-Aug-05 5:04
Membermachocr1-Aug-05 5:04 
AnswerRe: Why don&#180;t you use strored procedures? Pin
malharone1-Aug-05 12:55
Membermalharone1-Aug-05 12:55 
AnswerRe: Why don&#180;t you use strored procedures? Pin
_Asif_1-Aug-05 19:22
professional_Asif_1-Aug-05 19:22 
GeneralRe: Why don´t you use strored procedures? Pin
Esoteric !23-Feb-09 8:09
MemberEsoteric !23-Feb-09 8:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.