Query builder using XML as input to stored procedure

Question

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.

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. 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
– JWP, Commented Nov 12, 2014 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
– Michael Harper, Commented Nov 12, 2014 at 21:25
You can convert it to SQL with C# why do you need stored procedure? — Max Brodin
– Max Brodin, Commented Nov 12, 2014 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
– Michael Harper, Commented Nov 12, 2014 at 21:38

jbriggs · Accepted Answer · 2014-11-15 00:18:23Z

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

Collectives™ on Stack Overflow

Query builder using XML as input to stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related