0

I want to create a query builder which allows a user to build a query by selecting drop downs/text input etc on the web page which in turn creates an XML file containing the query filters such as (XML constructed from web page/View):

<Filters>
   <Filter Table="MyTable" Column="Total" Value="1234" Predicate="0"></Filter>
</Filters>

The idea would be for a stored procedure to receive this XML markup and parse it somehow so that it would translate to something like 

SELECT * FROM MyTable WHERE Total > 1234

I've found examples here that query XML or take XML in and then insert into tables but can anyone give me a simple example of how I would set the proc up to parse an XML parameter and build SQL?

I'm using C# (MVC) and SQL Server 2008.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Michael Harper
  • 1,531
  • 2
  • 25
  • 42
  • The first part is to be able to create the XML document, I think you already know how to do that right? Second you have to parse it prior to sending query to Server.... Check this post. http://stackoverflow.com/questions/7798852/parsing-xml-using-xdocument Finally, you have to build the query string from the parsed content and send it on to the SQL Server. Which part do you need help? – JWP Nov 12 '14 at 21:03
  • Hi, yes I can create the XML and parse the table name, column(s), value etc. I need some help on how to set up a stored procedure to receive the parsed values an execute them as a SQL query - Many thanks – Michael Harper Nov 12 '14 at 21:25
  • You can convert it to SQL with C# why do you need stored procedure? – Max Brodin Nov 12 '14 at 21:35
  • I just guessed stored proc, the idea is that users can build their own queries from the web page and something translates this in to executable SQL - Thanks – Michael Harper Nov 12 '14 at 21:38

1 Answers1

1

You could xsl to transform your xml file to a SQL file.

Something like:

<xsl:transform version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:strip-space elements="*"/>
 <xsl:output method="text" omit-xml-declaration="yes"/>
 <xsl:template match="/">
    <xsl:text>&#10;</xsl:text>
    <xsl:for-each select="Filters">
        <xsl:apply-templates select="Filter" />
    </xsl:for-each>

 </xsl:template>
 <xsl:template match="Filter">
    <xsl:text>Select * from </xsl:text><xsl:value-of select="@Table"/>
    <xsl:text> WHERE </xsl:text><xsl:value-of select="@Column"/>
    <xsl:choose>
        <xsl:when test="@Predicate = 0"><xsl:text disable-output-escaping="yes"> &#62; </xsl:text></xsl:when>
        <xsl:when test="@Predicate = 1"><xsl:text disable-output-escaping="yes"> &#60; </xsl:text></xsl:when>
        <xsl:when test="@Predicate = 2"><xsl:text> = </xsl:text></xsl:when>
    </xsl:choose>
    <xsl:value-of select="@Value"/>
    <xsl:text>&#10;</xsl:text>
  </xsl:template>
</xsl:transform>

This will translate this:

<Filters>
  <Filter Table="MyTable" Column="Total" Value="1234" Predicate="0"></Filter>
  <Filter Table="MyTable" Column="Total" Value="1234" Predicate="1"></Filter>
</Filters>

into this:

Select * from MyTable WHERE Total > 1234
Select * from MyTable WHERE Total < 1234
jbriggs
  • 353
  • 2
  • 10