Using XSLT to Generate SQL Script

• Download source - 13.01 KB

Situation

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: http://www.codeproject.com/KB/custom-controls/SqlWhereBuilder.aspx)

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.

Example

1. FilterConfig.xml
   XML

   <?xml version="1.0" encoding="utf-8"?>
   <FilterSchema>
    <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" />
    </Module>
   <FilterSchema>

2. ScanConfig.xml
   XML

   <?xml version="1.0" encoding="utf-8"?>
   <ScanSchema>
    <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" />
    </Module>
   </ScanSchema>

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:

SELECT TOP(1) ADDRESS1, ADDRESS2, ADDRESSTYPE FROM VIEW_MASTER_INFO

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.

Preparation

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)

Solution

(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:

<root>
 <FilterSchema>
  <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" />
  </Module>
 </FilterSchema>
 <ScanSchema>
  <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" />
  </Module>
 </ScanSchema>
</root>

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

3. integration.xml
   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.

4. judge.xsl
   XML

   <?xml version="1.0"?>
   <xsl:stylesheet version="1.0">
    <xsl:template match="/" name="TemplateA">
     <xsl:param name="param">
     </xsl:param>
     <xsl:value-of select="/root/ScanSchema/Module[@ID=$param]/@TableName"/>
    </xsl:template>
    <xsl:template match="/">
     <html>
      <title>
       Generated SQL Selecting Result
      </title>
      <body>
       <xsl:for-each select="/root/FilterSchema/Module">
        <br/>
        <![CDATA[SELECT TOP(1) ]]>
        <br/>
        <xsl:for-each select="./Field">
         <xsl:value-of select="@ID"/>
         <xsl:if test="position() &lt; last()">
          <xsl:text><![CDATA[,]]></xsl:text>
         </xsl:if>
         <xsl:if test="position()=last()">
          <xsl:text></xsl:text>
         </xsl:if>
         <br/>
        </xsl:for-each>
        <![CDATA[ FROM ]]>
        <xsl:call-template name="TemplateA">
         <xsl:with-param name="param" select="concat('&apos;,./@ID,&apos;')">
   	</xsl:with-param>
        </xsl:call-template><br/>
        <![CDATA[-----------------------------------------------]]>
        <br/>
       </xsl:for-each>
       <br/>
      </body>
     </html>
    </xsl:template>
   </xsl:stylesheet>

   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.

SELECT TOP(1) 
ADDRESS1,
ADDRESS2,
ADDRESSTYPE,
AUTHOR_EMAIL,
AUTHOR_FAX,
BUSINESS_CODE,
CATEGORY_NAME,
SOCIETY_CODE,
CITY,
CLASS,
COMPANYID,
COMPANY_NAME1,
COMPANY_SORT
FROM VIEW_MASTER_INFO

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.

Conclusion

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.

History

• 27^th February, 2010: Initial post