How to form a where clause from XML data

Question

see the store procedure just to know how my where clause look like

CREATE PROCEDURE USP_GETData
(
    @ProductID          INT = NULL, 
    @ProductName        NVARCHAR(50) = NULL
)
AS
BEGIN
    DECLARE
        @lProductID             INT, 
        @lProductName           NVARCHAR(50),

    SET @lProductID     =       @ProductID
    SET @lProductName   =       LTRIM(RTRIM(@ProductName))


    ; WITH CTE_Results
        AS (
        SELECT ROW_NUMBER() OVER (ORDER BY
            CASE WHEN (@lSortCol = 'ProductID' AND @SortOrder='ASC')
                        THEN ProductID
            END ASC,
            CASE WHEN (@lSortCol = 'ProductID' AND @SortOrder='DESC')
                       THEN ProductID
            END DESC,

            CASE WHEN (@lSortCol = 'ProductName' AND @SortOrder='ASC')
                      THEN ProductName
            END ASC,
            CASE WHEN @lSortCol = 'ProductName' AND @SortOrder='DESC'
                     THEN ProductName
            END DESC

       ) AS ROWNUM,
       Count(*) over () AS TotalCount,
       ProductID,
       ProductName
     FROM Products
     WHERE
         (@lProductID IS NULL OR ProductID = @lProductID)
     AND (@lProductName IS NULL OR ProductName LIKE '%' + @lProductName + '%')
    )

    SELECT
        ProductID, 
        ProductName
    FROM CTE_Results AS CPC

i just like to know without hard code of where clause like below

 WHERE
     (@lProductID IS NULL OR ProductID = @lProductID)
 AND (@lProductName IS NULL OR ProductName LIKE '%' + @lProductName + '%')

can i form it from XML data. suppose my XML may look like

DECLARE @xmlvar xml          
SET @xmlvar='   
<?xml version = "1.0" encoding="UTF-8" standalone="yes"?>       
<Filters>          
  <param>          
    <SearchField>ProductID</SearchField>          
    <FilterCondition>=</FilterCondition>          
    <ConditionData>101</ConditionData>          
  </param>          
  <param>          
    <SearchField>ProductName</SearchField>          
    <FilterCondition>%</FilterCondition>          
    <ConditionData>Foo</ConditionData>          
  </param>         
</Filters>'  

so i want to parse the xml and build where clause without using dynamic sql. is it possible ?

looking for help and suggestion. thanks

Answer 1

I think you need to use dynamic query like this -

DECLARE @WhereCond VARCHAR(MAX) = '', @Query VARCHAR(MAX)

DECLARE @xmlvar xml         
SET @xmlvar='<?xml version = "1.0" encoding="UTF-8" standalone="yes"?>       
<Filters>          
  <param>          
    <SearchField>ProductID</SearchField>          
    <FilterCondition>=</FilterCondition>          
    <ConditionData>101</ConditionData>          
  </param>          
  <param>          
    <SearchField>ProductName</SearchField>          
    <FilterCondition>%</FilterCondition>          
    <ConditionData>Foo</ConditionData>          
  </param>         
</Filters>'
SELECT @WhereCond += ' AND ' + SearchField + REPLACE(FilterCondition, '%', 'LIKE ''%') + ConditionData + CASE WHEN FilterCondition = '%' THEN '%''' ELSE '' END FROM
(
    select T.N.value('SearchField[1]', 'varchar(50)') AS SearchField, T.N.value('FilterCondition[1]', 'varchar(50)') AS FilterCondition, T.N.value('ConditionData[1]', 'varchar(50)') AS ConditionData
    from @xmlvar.nodes('/Filters/param') as T(N)
) res

-- PRINT @WhereCond    ---> AND ProductID=101 AND ProductNameLIKE '%Foo%'

SET @Query = N'
    SELECT 
        Count(*) over () AS TotalCount,
        ProductID,
        ProductName
     FROM Products
     WHERE 1 = 1' + @WhereCond

EXEC (@Query)