Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Using XSLT to Generate SQL Script

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

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 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 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)

_1__XSLT_Tester.jpg

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>.

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

_2__XSLT_TESTER_RESULT_1.jpg

_3__IE_RESULT_2.jpg

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.

_4__SQL_SERVER_SUCCESS.jpg

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

  • 27th February, 2010: Initial post

License

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

Share

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: http://blog.bigcay.com/
Follow on   Twitter

Comments and Discussions

 
QuestionWhy HTML PinmemberChris Mannion15-Sep-10 4:37 
GeneralBetter way... PinmemberDave Elliott3-Mar-10 5:42 
GeneralWell done PinmemberMichael Brookie2-Mar-10 0:09 
GeneralRe: Well done PinmemberLeo Bi2-Mar-10 19:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web02 | 2.8.140905.1 | Last Updated 27 Feb 2010
Article Copyright 2010 by Leo Bi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid