.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Parse XML To build Dynamic Query

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
Hi All, I am trying to figure out the best way to handle a scenario where I will be passed XML that will contain criteria for a search. If the user has selected specific filters then those will be sent in the XML and if there is a section that they left unfiltered then it will not be present in the XML (Which would mean everything for that filter should be returned). My question is around the best process to shred the XML and build a dynamic query out of what i am getting out of the XML object. Is there a better way to handle this scenario? Here is my current approach: Shred the XML and put the filtered data into Global Temp Tables so that I can use them to build my dynamic query. Use those temp tables to create "Where Exists" Criteria within the query to filter down the results based on what was passed to me in XML. If one of the search criteria sections wasn't filtered the temp table would have zero rows and I wouldn't add that to the where clause with an exists statement. I used FOR XML PATH(') in the queries to roll up the data into comma separated values. Build the test schema / objects:   -------------------------------------------------------- --Build Test Schema to demonstrate XML Parsing -------------------------------------------------------- SET NOCOUNT ON; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products]

View Complete Post

More Related Resource Links

Dynamic query for Null field checking

Hi I want dynamiclly find the table to null values finding.. Please give me quickly   Regards Ram

Dynamic query for datalength field checking

Hi   Please tell me the dynamic query length in table

Dynamic query for Null field checking

Hi I want dynamically check the data Formation query in SQL server please  provide me the query

Dynamic Class with Linq query Result

Hi, var   query = db.Customers.Where( "City == @0 and Orders.Count >= @1", "London", 10). OrderBy( "CompanyName"). Select( "New(CompanyName as Name, Phone)");    I am using this query as a sample which returns a dynamicclass type, and i need this should to converted to a list collections or datatable etc., As i am holding it in my wcf service with linqtosql as dataaccesslayer. Trying to send the results to the UI which i have consumes this service. Kindly help me in this. Thanks query = db.Customers


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Dynamic Query alternative?

Hi, I was trying to create a query to get some results for next five years and need to show next five years as Column names. So whatever the year user runs this query, it gives the results for the next five years. Then I realized this was not easy as I expected but managed to implement it using Dynamic Queries.I wrote the following code Ito implement that. But I'm not sure is this the best way to do this.   SET ANSI_NULLS ON  GO  SET QUOTED_IDENTIFIER ON  GO  CREATE PROCEDURE sp_RS_ScenariosforNext5Years  @ticker nvarchar(10),  @variable nvarchar(100)  AS  Declare @SQL varchar(1000)  SELECT @SQL = 'SELECT CASE Scenario '  SELECT @SQL = @SQL + ' WHEN ''A'' THEN ''case1'' '  SELECT @SQL = @SQL + 'WHEN ''B'' THEN ''case2'' '  SELECT @SQL = @SQL + 'WHEN ''C'' THEN ''case3'' '  SELECT @SQL = @SQL + 'WHEN ''D'' THEN ''case4'' '  SELECT @SQL = @SQL + 'WHEN ''E'' THEN ''case5'' '  SELECT @SQL = @SQL + 'WHEN ''F'' THEN ''case6'' '  SELECT @SQL = @SQL + 'WHEN ''G''

Dynamic SQL query cannot store more than 4000 characters even with NVARCHAR(MAX)

Hello, everyone. I am having a rather strange problem when building a dynamic query in SQL Server. I declare a variable of NVARCHAR(MAX) type, and build some queries into it, running them later with sp_executesql. One or two of these queries are so large that they surpass the 4000 char limit that, supposedly, NVARCHAR(MAX) overcomes. However, I still am having the classic problem of storing the whole query inside the variable! I use SQL Server 2008. All the documentation online I checked states that the solution is just using NVARCHAR(MAX) and the problem should not happen. How can this keep happening? Cheers, Ivo Pereira IT Consultant PortugalComputerDoc

Dynamic Linq query?

Please excuse my rookiness but is it possible to make a Linq query dynamic in the following pseduo code (to show what I'm trying to do)Dim objCustomoer = (From c In dc.CustomerLocation _ Where c.customerID = a1 _ And { c.zip = a2  OR  c.city = a3  OR  c.state = a4} _                             Select c).ToList() where a2 and a3  and a4 are being populated from user controls and only one will apply.  So query is done by state,  zip or by city, but not by more than one, so the other two would be null.  So the operator is really "OR" but I haven't found anything on a "or" or "whereOr" operator.  How can I accomplish this?

Problems writing a dynamic L2E query

I'm trying to re-work a L2E query to be more dynamic, but I'm not having much luck. Basically, I have two parameters (and many more to come, just laying the foundation), and the parameters are both optional from a user-endpoint.Originally I wrote this static expression:int personnelId = 1234; int divisionId = 1234; var results = (from a in ctx.Attendees from d in a.Divisions join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId && d.DivisionID == divisionId select new Attendee { firstName = a.FirstName, lastName = a.LastName });Attendee is a POCO. After I wrote this, I realized that if personnelId or divisionId weren't passed in (or if just one were passed in) I'd want a different result set. I'm conceptualizing the idea like this (doesn't compile, but you get the drift):var results = (from a in ctx.Attendees select new Attendee { firstName = p.FirstName, lastName = p.LastName }); if (personnelId != null) { results = (from a in results join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId select a); } if (divisionId != null) { results = (from a in results from d in a.Divisions where d.DivisionID == divisionId select a); } results = results.ToList();Doesn't work well though, because my

How to apply dynamic filter in Datasheet View from Query String

The datasheet view doesn't support either connections/dynamic filters from Query string. How to filter the data using query string?

Trying to build query using DISTINCT or GROUP BY...beginner here

Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

dynamic table name linq-to-sql query


I am writing a function in which i generate autocode in specific format i want to make this function generalize so that i send tablename and columnname  parameter and function return the next code. I am using linq to sql with MVC e.g. i want to make table name and column name dyanamic

from m in db.<tblnameparam> 
                       orderby m.<colnameparam> descending
                       select m.<colnameparam>

Best way to build a query /sproc to support filtering


Hi All,


I have a scenario where I will be getting search results from a front end UI.  If nothing is passed I should return all results, but if the users have selected one to many choices I will need to filter based on those choices.  My delima is how to handle this approach.  The data will be coming in the form of XML and I will need to shred it to get the criteria.

I was thinking I could use "WHERE EXISTS" Clauses and if there are any results passed to me I could fill a table variable with everything and join to it.....if they did make selections then I could fill the table with just those choices. 

I really just don't like the idea of having to fill the table with everythign when nothing is selected. 

Just wondering what others have done to handle this type of scenario?

Thanks !


How to build long query string



I know that it is basic questions, but I am stuck here.

I have TableOne have fields: USERNAME, PID, GID, TID, SID, NEWSID. I need to write query string to check if those value is already in the table. If not, insert those value to the table.

string AddUserName = UsernameToken(GetUsername);
string InsertString = "insert into TableOne(USERNAME, PID, GID, TID, SID, NEWSID) values (?, ?, ?, ?, ?, ? )" + "  WHERE NOT " + " ( "  [USERNAME] = '" + AddUserName + "' + " PID = '" + DropDownBoxPID.Text.Trim() +
 '" + " AND " + " GID = '" + DropDownBoxGID.Text.Trim()  +
 '"  + " AND " + "  TID = '" + DropDownBoxtid.Text.Trim()  +  
 '" + " AND " + " SID = '" + Textboxsid.Text.Trim() +  
 '" + " AND " + " NEWSID = '" + DropDownssid.Text.Trim() +
 '" + ")" ;

However, I got the many errors:

Error    1    Too many characters in character literal  

Error    3    Newline in constant   

Is any way to debug this? As always, your help is highly a

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. dynamic query with a null parameter to dont take in cosideration if null


    Hi guys,

    Do you know in a dynamic query how can I manage if a parameter isnull or not.

    declare @businessunitidname varchar(15)
    set @businessunitidname = NULL;
    --print @businessunitidname
    declare @eu_reporthubname varchar(15);
    set @eu_reporthubname = Null;
    declare @ownerid varchar(15);
    set @ownerid = Null;
    declare @SQL varchar(4000);
    SET @SQL = ';with cte as
    	select ''eu_mondayamid'' as WeekName,0 as WeekVal union all
    	select ''eu_mondaypmid'',0 union all
    	select ''eu_tuesdayamid'',1 union all
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend