CODEWITHSUNDEEP
sql-serverxml
Thomas
Thomas

Reputation: 34188

Generate dynamic where clause from xml

i need to send a xml to a store procedure. the xml would look like

<NewDataSet>  
<param>  
<SearchField>EmployeeID</SearchField>  
<FilterCondition> >= </FilterCondition>  
<ConditionData>201</ConditionData>  
<MatchCase>0</MatchCase>  
</params>
<param>    
<SearchField>DeptID</SearchField>  
<FilterCondition> = </FilterCondition>  
<ConditionData>AC01</ConditionData>  
<MatchCase>0</MatchCase>  
</params>  
<param>    
<SearchField>Dob</SearchField>  
<FilterCondition> <= </FilterCondition>  
<ConditionData>23-MAR-2010</ConditionData>  
<MatchCase>0</MatchCase>  
</params>  
</NewDataSet>
  1. SearchField tag hold the search field name on which search will occur
  2. FilterCondition tag will hold the operator i use to search and compare like '%' '<=' '<=' etc
  3. ConditionData tag will actual value which we will search
  4. MatchCase indicate it will case sensitive or not

i can load the xml and iterate in while loop of cursor and generate a where clause. my question is how can i generate where clause based on field data type.

if the field is string then we always compare like with in '' single quote if the field is numeric or bit type then we can compare without single quote if the field is date time then i like to compare like convert(@dob,varchar,112) > '20100101'

so my question is how to generate where clause based on data type. i need to fetch the field data type dynamically and accordingly build the where clause. please share the best idea. thanks

i change my xml bit. i include table name for every field. so please tell me what i need to change in this sql to show the data type for every field. to show the data type i need to join with system table. so please do necessary change in ur sql for join with sys table to fetch & show the data type for each field.

INSERT INTO @tbl_WhereClause (SearchField, Operator, ConditionData,   MatchCase,TableName)
SELECT  A.B.value('(SearchField)[1]', 'VARCHAR(255)' ) SearchField, 
    A.B.value('(FilterCondition)[1]', 'VARCHAR(25)' ) Operator, 
    A.B.value('(ConditionData)[1]', 'VARCHAR(MAX)' ) ConditionData,
    A.B.value('(MatchCase)[1]', 'BIT' ) MatchCase,
    A.B.value('(Table)[1]', 'VARCHAR(MAX)' ) TableName
FROM    @WhereClause_XML.nodes('/NewDataSet/param') A(B)

here is my solution.

/*
XML DATA SAMPLE
<NewDataSet>
<param>
    <SearchField>EmpID</SearchField>
    <FilterCondition> >= </FilterCondition>
    <ConditionData>201</ConditionData>
    <MatchCase>0</MatchCase>
    <Table>Employee</Table>
</param>
<param>  
    <SearchField>DeptName</SearchField>
    <FilterCondition> = </FilterCondition>
    <ConditionData>AC01</ConditionData>
    <MatchCase>1</MatchCase>
    <Table>Department</Table>
</param>
<param>  
    <SearchField>Dob</SearchField>
    <FilterCondition> >= </FilterCondition>
    <ConditionData>20120104</ConditionData>
    <MatchCase>0</MatchCase>
    <Table>Employee</Table>
</param>
  </NewDataSet>'
  */

  CREATE PROCEDURE GenericSearch
  (
@WhereClause_XML XML,
@LogicalOperator VARCHAR(3)
  )
  AS

 BEGIN

 DECLARE @SearchField VARCHAR(255),
 @Operator VARCHAR(25),
 @ConditionData VARCHAR(MAX),
 @MatchCase BIT,
 @TableName VARCHAR(MAX),
 @Validity VARCHAR(100),
 @ColumnType VARCHAR(128),
 @ColumnPrecision INT,
 @ColumnScale INT,
 @ColumnNullable bit,
 @WhereClause VARCHAR(MAX)

 DECLARE @tbl_WhereClause AS TABLE 
 (
SearchField VARCHAR(255),
Operator VARCHAR(25),
ConditionData VARCHAR(MAX),
MatchCase BIT,
TableName VARCHAR(MAX),
Validity VARCHAR(100),
ColumnType VARCHAR(128),
ColumnPrecision INT,
ColumnScale INT,
ColumnNullable bit
 )


 INSERT INTO @tbl_WhereClause (SearchField, Operator, ConditionData,   MatchCase,TableName, 
 Validity, ColumnType, ColumnPrecision, ColumnScale, ColumnNullable)

SELECT  A.B.value('(SearchField)[1]', 'VARCHAR(255)' ) SearchField,
A.B.value('(FilterCondition)[1]', 'VARCHAR(25)' ) Operator,
A.B.value('(ConditionData)[1]', 'VARCHAR(MAX)' ) ConditionData,
A.B.value('(MatchCase)[1]', 'BIT' ) MatchCase,
A.B.value('(Table)[1]', 'VARCHAR(MAX)' ) TableName,
CASE WHEN t.NAME+c.NAME IS NULL THEN 'invalid' ELSE 'valid' END ,
ty.NAME,
c.PRECISION,
c.Scale,
c.Is_Nullable
FROM    @WhereClause_XML.nodes('/NewDataSet/param') A(B)
LEFT JOIN sys.tables t ON t.name = A.B.value('(Table)[1]', 'VARCHAR(MAX)' )
LEFT JOIN sys.COLUMNS c ON T.OBJECT_ID = c.OBJECT_ID AND c.name =     A.B.value('(SearchField)[1]', 'VARCHAR(255)' )
LEFT JOIN sys.types ty ON c.system_type_id = ty.system_type_id 

--SELECT * FROM @tbl_WhereClause

SET @WhereClause= 'WHERE 1=1'
DECLARE SearchCursor CURSOR FOR 
SELECT * FROM @tbl_WhereClause

OPEN SearchCursor

FETCH NEXT FROM SearchCursor 
INTO @SearchField,  @Operator,@ConditionData,@MatchCase,@TableName,@Validity,@ColumnType,@ColumnPrecision,@Colu    mnScale,@ColumnNullable

WHILE @@FETCH_STATUS = 0
BEGIN

IF CHARINDEX('INT', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) +  @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('NUMERIC', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('BIT', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('DECIMAL', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('FLOAT', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('REAL', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('MONEY', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+space(1)+@ConditionData
END
ELSE IF CHARINDEX('DATE', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + 'CONVERT(varchar,'+@SearchField+',112)' +  space(1)+@Operator+'CONVERT(varchar,'''+@ConditionData+''',112)'
END
ELSE IF CHARINDEX('CHAR', UPPER(@ColumnType)) > 0 
BEGIN
    SET @WhereClause = @WhereClause + space(1)+ @LogicalOperator + space(1) + @SearchField + space(1)+@Operator+''''+@ConditionData+''''
END 

FETCH NEXT FROM SearchCursor 
INTO @SearchField,  @Operator,@ConditionData,@MatchCase,@TableName,@Validity,@ColumnType,@ColumnPrecision,@ColumnScale,@ColumnNullable
    END

    CLOSE SearchCursor  
    DEALLOCATE SearchCursor 

    SELECT @WhereClause
    END

Upvotes: 0

Views: 900

Answers (1)

HeavenCore
HeavenCore

Reputation: 7683

2 small things, 1 < & > etc are very illegal in XML markup, if you want to reliably parse it, whatever is generating the XML will need to encode these to the approriate entity reference:

&lt;    <    less than
&gt;    >    greater than
&amp;   &    ampersand
&apos;  '    apostrophe
&quot;  "    quotation mark

Also, your closing nodes and opening nodes have a typo, for instance <param> is closed by <params> (note the 'S') - assuming this is a typo.

That said, the hardest part is parsing the XML into usefull data that you can use to build your dynamic SQL. You could do this by parsing the XML into a table:

DECLARE @tbl_WhereClause AS TABLE (
    SearchField VARCHAR(255),
    Operator VARCHAR(25),
    ConditionData VARCHAR(MAX),
    MatchCase BIT
)

DECLARE @WhereClause_XML XML 
SET @WhereClause_XML = '
<NewDataSet>   
<param>   
<SearchField>EmployeeID</SearchField>   
<FilterCondition> &gt;= </FilterCondition>   
<ConditionData>201</ConditionData>   
<MatchCase>0</MatchCase>   
</param> 
<param>     
<SearchField>DeptID</SearchField>   
<FilterCondition> = </FilterCondition>   
<ConditionData>AC01</ConditionData>   
<MatchCase>0</MatchCase>   
</param>   
<param>     
<SearchField>Dob</SearchField>   
<FilterCondition> &lt;= </FilterCondition>   
<ConditionData>23-MAR-2010</ConditionData>   
<MatchCase>0</MatchCase>   
</param>   
</NewDataSet>'

INSERT INTO @tbl_WhereClause (SearchField, Operator, ConditionData, MatchCase)
SELECT  A.B.value('(SearchField)[1]', 'VARCHAR(255)' ) SearchField, 
        A.B.value('(FilterCondition)[1]', 'VARCHAR(25)' ) Operator, 
        A.B.value('(ConditionData)[1]', 'VARCHAR(MAX)' ) ConditionData,
        A.B.value('(MatchCase)[1]', 'BIT' ) ConditionData
FROM    @WhereClause_XML.nodes('/NewDataSet/param') A(B) 

SELECT * FROM @tbl_WhereClause

Once you have the parsed data, its just an easy case (if not tedious) of iterating through the table and building the where clase string - let me know if you need assistance with that part.

Upvotes: 1

Related Questions