Click here to Skip to main content
11,920,413 members (51,408 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


18 bookmarked

Using XSLT to Generate SQL Script

, 26 Feb 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
How to use XSLT to generate SQL script


Recently, I received a task to check every field configured in the FilterConfig.xml file to make sure that each field has the relative columns' name in Table or View which is configured at ScanConfig.xml file.

P.S.: The FilterConfig.xml is used for the filter module in our system, and this module is based on the web control "SqlWhereBuilder". (Refer:

If some field is configured at FilterConfig.xml file but does not exist in relative table or view, there must be something wrong with either configuration or the database schema.

When dipping into the two configuration files, I found that each field is configured under the node "Module", and each module has the relative "Module" node with the same ID name in ScanConfig.xml, and “TableName” is also configured as a property of the "Module" node of that file, the “TableName” shows the real table name or view name in database.


  1. FilterConfig.xml
    <?xml version="1.0" encoding="utf-8"?>
     <Module ID="MasterInfo">
      <Field ID="ADDRESS1" Text ="Address" OperatorList ="datatype_text" />
      <Field ID="ADDRESS2" Text ="Address2" OperatorList ="datatype_text" />
      <Field ID="ADDRESSTYPE" Text ="Address Type" OperatorList ="datatype_text" />
  2. ScanConfig.xml
    <?xml version="1.0" encoding="utf-8"?>
     <Module ID="MasterInfo" TableName="VIEW_MASTER_INFO" 
         DataKeyName="ID" EnableDataKey="true" 
         OrderByFieldName="ID ASC" PageSize="5" 
         WhereClause="" MasterIdField="ID">
      <Field RealName="ID" ShownName="Master ID" 
              Width="33%" Align="left" />
      <Field RealName="COMPANY_NAME1" 
         ShownName="Company Name" Width="33%" 
         Align="left" />
      <Field RealName="FULL_NAME" ShownName="Sort Name" 
             Width="33%" Align="left" />

Now, if we can generate a SELECT clause with all the fields configured at file FilterConfig.xml, and then run this clause in the SQL Server Management Studio. We will easily find out if there exists any problem.

According to the above example, the generated SELECT clause should be like this:


So far, it seems quite easy, isn't it? But, DON'T ever try to generate the SELECT clause manually, because there are hundreds of modules and thousands of fields configured in the file FilterConfig.xml. What we need is an auto-generated method to solve this issue. That is exactly what I'd do.


Firstly, I'd be glad to introduce a useful tool called "XSLT Tester" written by Riaan Hanekom. It did help me preview the result faster than using Internet Explorer. (Refer: XSLT_Tester.aspx)



(1) In order to fetch the TableName, we need to combine the two file FilterConfig.xml and ScanConfig.xml. It should be as shown below:

  <Module ID="MasterInfo">
   <Field ID="ADDRESS1" Text ="Address" 
           OperatorList ="datatype_text" />
   <Field ID="ADDRESS2" Text ="Address2" 
           OperatorList ="datatype_text" />
   <Field ID="ADDRESSTYPE" Text ="Address Type" 
           OperatorList ="datatype_text" />

Actually, we could use the External Entity Reference to combine these two XML files into the new added parent node <root></root>.

  1. integration.xml
    <?xml version="1.0"?>
    <?xml-stylesheet type="text/xsl" href="judge.xsl"?>
    <!DOCTYPE root [
     <!ENTITY claimer1 SYSTEM "./FilterConfig.xml">
     <!ENTITY claimer2 SYSTEM "./ScanConfig.xml">

(2) Then, we write the XSLT file.

  1. judge.xsl
    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0">
     <xsl:template match="/" name="TemplateA">
      <xsl:param name="param">
      <xsl:value-of select="/root/ScanSchema/Module[@ID=$param]/@TableName"/>
     <xsl:template match="/">
        Generated SQL Selecting Result
        <xsl:for-each select="/root/FilterSchema/Module">
         <![CDATA[SELECT TOP(1) ]]>
         <xsl:for-each select="./Field">
          <xsl:value-of select="@ID"/>
          <xsl:if test="position() &lt; last()">
          <xsl:if test="position()=last()">
         <![CDATA[ FROM ]]>
         <xsl:call-template name="TemplateA">
          <xsl:with-param name="param" select="concat('&apos;,./@ID,&apos;')">
    1. Fetch each module, and fetch each field ID from the module. If the field is not the last one, then add comma after the field ID, and add nothing when the last one is encountered.
    2. For each module, call template "TemplateA" to show the relative TableName configured inside the node "/root/ScanSchema/Current Module". Use concat() to concat the string with single quotation marks.
    3. In TemplateA, use the <xsl:value-of> element selecting "/root/ScanSchema/Module[@ID=$param]/@TableName" to show the TableName.

(3) Well, it will be finished within a hair's breadth.

Now, we can use the small useful tool "XSLT Tester" or Internet Explorer to show the result.




And then copy and paste the content to your SQL Server Management Studio and run the script. If you get the "success" message, the configuration is okay.



Well, till now, I have finished my task. There are totally four problems in the configuration file according to the current database schema. During the script execute process, I found that if there were more than 100 select clauses, the SQL Server would show the error message like:

"The query has exceeded the maximum number of result sets that can be displayed 
in the results grid. Only the first 100 result sets are displayed in the grid". 

If you have a better solution for this issue, please let me know. Thank you.


  • 27th February, 2010: Initial post


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


About the Author

Leo Bi
Software Developer E5 Systems
China China
I am currently a Programmer/Analyst working at E5 Systems, and had been engaged in software outsourcing industry for two years. I have strong interest and passion in software architecture and web application security fields. I am proficient in Java and C#.

My Blog:

You may also be interested in...

Comments and Discussions

QuestionWhy HTML Pin
Chris Mannion15-Sep-10 5:37
memberChris Mannion15-Sep-10 5:37 
GeneralBetter way... Pin
Dave Elliott3-Mar-10 6:42
memberDave Elliott3-Mar-10 6:42 
GeneralWell done Pin
Michael Brookie2-Mar-10 1:09
memberMichael Brookie2-Mar-10 1:09 
XSLT is so versatile isn`t it. Thanks for the article. Well done.
GeneralRe: Well done Pin
Leo Bi2-Mar-10 20:40
memberLeo Bi2-Mar-10 20:40 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151120.1 | Last Updated 27 Feb 2010
Article Copyright 2010 by Leo Bi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid