Parse XML To build Dynamic Query

Question

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:

1. Shred the XML and put the filtered data into Global Temp Tables so that I can use them to build my dynamic query.
2. 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.
3. 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]') AND type in (N'U'))
    DROP TABLE [test].[Products]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Categories]') AND type in (N'U'))
    DROP TABLE [test].[Categories]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Brands]') AND type in (N'U'))
    DROP TABLE [test].[Brands]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Types]') AND type in (N'U'))
    DROP TABLE [test].[Types]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products_Categories]') AND type in (N'U'))
    DROP TABLE [test].[Products_Categories]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products_Brands]') AND type in (N'U'))
    DROP TABLE [test].[Products_Brands]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products_Types]') AND type in (N'U'))
    DROP TABLE [test].[Products_Types]
    GO
    --IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'test')
    --DROP SCHEMA [test]
    --GO
    --CREATE SCHEMA [test] AUTHORIZATION [dbo]
    --GO

    Create Table test.Categories(
    CategoryID  INT IDENTITY(1,1),
    Category varchar(100));

    Insert Into test.Categories
    Values('HDTV');


    Insert Into test.Categories
    Values('Receiver');


    Insert Into test.Categories
    Values('Headphones');

    Insert Into test.Categories
    Values('Blu-Ray');

    GO

    Create Table test.Brands(
    BrandID  INT IDENTITY(1,1),
    Brand varchar(100));

    Insert Into test.Brands
    Values('Sony');


    Insert Into test.Brands
    Values('Samsung');


    GO
    Create Table test.[Types](
    TypeID  INT IDENTITY(1,1),
    [Type] varchar(100));

    Insert Into test.[Types]
    Values('LCD');


    Insert Into test.[Types]
    Values('Plasma');

    Insert Into test.[Types]
    Values('Rear Projection');


    Insert Into test.[Types]
    Values('LED');

    GO
    Create Table test.Products_Categories(
    ProductCategoryID  INT IDENTITY(1,1),
    ProductID          INT,
    CategoryID          INT)

    GO
    Create Table test.Products_Brands(
    ProductBrandID  INT IDENTITY(1,1),
    ProductID          INT,
    BrandID          INT)

    GO
    Create Table test.Products_Types(
    ProductTypeID  INT IDENTITY(1,1),
    ProductID          INT,
    TypeID          INT)

    GO

    Insert Into test.Products_Categories
    Select 1,1
    UNION
    Select 1,2
    UNION
    Select 1,3
    UNION 
    Select 1,4
    UNION
    Select 2,1
    UNION
    Select 2,2
    UNION
    Select 2,3


    GO

    Insert Into test.Products_Brands
    Select 1,1
    UNION
    Select 1,2
    UNION
    Select 1,3
    UNION 
    Select 1,4
    UNION
    Select 2,1
    UNION
    Select 2,2
    UNION
    Select 2,3
    UNION 
    Select 2,4

    GO

    Insert Into test.Products_Types
    Select 1,1
    UNION
    Select 1,2
    UNION
    Select 2,1



    GO


    CREATE TABLE [test].[Products](
        ProductID [int] IDENTITY(1,1) NOT NULL,
        Product [varchar](25) NULL
    ) ON [PRIMARY]

    GO

    Insert Into [test].[Products]
    Select 'A.1'
    UNION
    Select 'B.1'

SET NOCOUNT OFF;

Build procedure to shred xml and build dynamic query:

--------------------------------------------------------
--Create Sproc to Parse XML Input
--------------------------------------------------------
GO
ALTER PROCEDURE dbo.GetMySearchResults
         @XML           XML,
         @Debug         BIT = 0

AS

BEGIN

    SET NOCOUNT ON;

        DECLARE @SearchOutput TABLE(
                Product             VARCHAR(50),
                Category            VARCHAR(50),
                Brand               VARCHAR(50),
                [Type]              VARCHAR(50));


        DECLARE @Category                VARCHAR(200) = '',
                @Brand                   VARCHAR(200) = '',
                @Type                    VARCHAR(200) = '',
                @Where                   VARCHAR(500) = '',
                @SQL                     NVARCHAR(4000)

        ------Shred Material Data---
        IF OBJECT_ID('tempdb..##Category') IS NOT NULL DROP TABLE ##Category;
        CREATE TABLE ##Category (ID INT PRIMARY KEY);
        INSERT INTO ##Category SELECT Nodes.ID.value('@id', 'int') FROM @xml.nodes('//Filter[@id="Category"]//select') AS Nodes(ID);
        IF (Select COUNT(*) From ##Category) > 0
            SET @Category = 'and exists (Select 1 From ##Category el Where el.ID = e.CategoryID)'

        ------Component Material Data---
        IF OBJECT_ID('tempdb..##Brand') IS NOT NULL DROP TABLE ##Brand;
        CREATE TABLE ##Brand (ID INT PRIMARY KEY);
        INSERT INTO ##Brand SELECT Nodes.ID.value('@id', 'int') FROM @xml.nodes('//Filter[@id="Brand"]//select') AS Nodes(ID);
        IF (Select COUNT(*) From ##Brand) > 0
            SET @Brand = 'and exists (Select 1 From ##Brand cl Where cl.ID = c.BrandID)'

        ------Shred Environment Data---
        IF OBJECT_ID('tempdb..##Type') IS NOT NULL DROP TABLE ##Type;
        CREATE TABLE ##Type (ID INT PRIMARY KEY);
        INSERT INTO ##Type SELECT Nodes.ID.value('@id', 'int') FROM @xml.nodes('//Filter[@id="Type"]//select') AS Nodes(ID);
        IF (Select COUNT(*) From ##Type) > 0
            SET @Type = 'and exists (Select 1 From ##Type ml Where ml.ID = m.TypeID)'

        ----Build Where Exists Clauses
        IF @Category <> '' OR @Brand <> '' OR @Type <> ''
            SET @Where = 'Where 1 = 1 ' + @Category + @Brand + @Type

        ---Build Dynamic SQL to generate results from XML--
        SET @SQL = ';WITH SearchData
                    AS(
                        Select 
                            Distinct
                            li.Product,
                            ---------Material------
                            (Select Distinct m2.Category + '',''
                             From test.Products li2
                             join test.Products_Categories lm on li2.ProductID = lm.ProductID
                             join test.Categories m on lm.CategoryID = m.CategoryID
                             join test.Products_Categories lm2 on lm.ProductID = lm2.ProductID
                             join test.Categories m2 on lm2.CategoryID = m2.CategoryID
                             Where li2.ProductID = li.ProductID
                             FOR XML PATH('''')) Category,
                             ---------Component------
                             (Select Distinct c2.Brand + '',''
                             From test.Products li2
                             join test.Products_Brands lc on li2.ProductID = lc.ProductID
                             join test.Brands c on lc.BrandID = c.BrandID
                             join test.Products_Brands lc2 on lc.ProductID = lc.ProductID
                             join test.Brands c2 on lc2.BrandID = c2.BrandID
                             Where li2.ProductID = li.ProductID
                             FOR XML PATH('''')) Brand,
                             ---------Environment------
                             (Select Distinct e2.[Type] + '',''
                              From test.Products li2
                              join test.Products_Types le on li2.ProductID = le.ProductID
                              join test.[Types] e on le.TypeID = e.TypeID
                              join test.Products_Types le2 on le.ProductID = le2.ProductID
                              join test.[Types] e2 on le2.TypeID = e2.TypeID
                              Where li2.ProductID = li.ProductID
                              FOR XML PATH('''')) [Type]
                        From test.Products li
                        join test.Products_Categories le on li.ProductID = le.ProductID
                        join test.Categories e on le.CategoryID = e.CategoryID
                        join test.Products_Brands lc on li.ProductID = lc.ProductID
                        join test.Brands c on lc.BrandID = c.BrandID
                        join test.Products_Types lm on li.ProductID = lm.ProductID
                        join test.[Types] m on lm.TypeID = m.TypeID ' 

                        + @Where +  ')
                        Select
                                sd.Product,
                                SUBSTRING(sd.Category,1,LEN(sd.Category)-1) Category,
                                SUBSTRING(sd.Brand,1,LEN(sd.Brand)-1) Brand,
                                SUBSTRING(sd.[Type],1,LEN(sd.[Type])-1) [Type]
                        From SearchData sd '

        IF @Debug = 1
            PRINT @SQL;

        Insert Into @SearchOutput
        exec sp_executesql @SQL;

        Select 
            Distinct
                Product,
                Category,
                Brand,
                [Type]
        From @SearchOutput; 

        DROP TABLE ##Category;
        DROP TABLE ##Brand;
        DROP TABLE ##Type;

    SET NOCOUNT OFF;

END

GO
-----------------------------------------------------------------------
--Test XML Parsing
-----------------------------------------------------------------------
DECLARE @XMLInput XML = '<FilterData>
                            <Filter id="Category">
                                <select id="1" value="HDTV"/>
                                <select id="2" value="Receiver"/>
                                <select id="3" value="Headphones"/>
                                <select id="4" value="Blu-Ray"/>
                            </Filter>
                            <Filter id="Brand">
                                <select id="1" value="Sony"/>
                                <select id="2" value="Samsung"/>
                            </Filter>
                            <Filter id="Type">
                                <select id="1" value="LCD"/>
                                <select id="2" value="Plasma"/>
                                <select id="3" value="Rear Projection"/>
                                <select id="4" value="LED"/>
                            </Filter>
                        </FilterData>';


exec dbo.GetMySearchResults 
                        @XML = @XMLInput,
                        @Debug = 1

GO

Is there a better way to handle Shredding the XML or building the dynamic pieces?

Always appreciate the info.

S

Answer 1

Consider the following.

Primary keys:

ALTER TABLE [test].[Brands] ADD CONSTRAINT [PK_Brands] PRIMARY KEY CLUSTERED ( [BrandID] ASC ) ON [PRIMARY]
ALTER TABLE [test].[Categories] ADD CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [CategoryID] ASC ) ON [PRIMARY]
ALTER TABLE [test].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [ProductID] ASC ) ON [PRIMARY]
ALTER TABLE [test].[Products_Brands] ADD CONSTRAINT [PK_Products_Brands] PRIMARY KEY CLUSTERED ( [ProductID] ASC, [BrandID] ASC ) ON [PRIMARY]
ALTER TABLE [test].[Products_Categories] ADD CONSTRAINT [PK_Products_Categories] PRIMARY KEY CLUSTERED ( [ProductID] ASC, [CategoryID] ASC ) ON [PRIMARY]
ALTER TABLE [test].[Products_Types] ADD CONSTRAINT [PK_Products_Types] PRIMARY KEY CLUSTERED ( [ProductID] ASC, [TypeID] ASC ) ON [PRIMARY]
ALTER TABLE [test].[Types] ADD CONSTRAINT [PK_Types] PRIMARY KEY CLUSTERED ( [TypeID] ASC ) ON [PRIMARY]

Indexed views:

create view [test].[vw_Products] with schemabinding as
select li.ProductID ,
        Product ,
        le.CategoryID ,
        Category ,
        lc.BrandID ,
        Brand ,
        lm.TypeID ,
        Type
from test.Products li
join test.Products_Categories le on li.ProductID = le.ProductID
join test.Categories e on le.CategoryID = e.CategoryID
join test.Products_Brands lc on li.ProductID = lc.ProductID
join test.Brands c on lc.BrandID = c.BrandID
join test.Products_Types lm on li.ProductID = lm.ProductID
join test.[Types] m on lm.TypeID = m.TypeID 
go
create unique clustered index IX_vw_Products on test.vw_Products (ProductID, CategoryID, BrandID, TypeID)
go

--Categories
create view test.vw_Product_Category 
with schemabinding
as
select p.ProductID, c.CategoryID, c.Category from test.Products as p
join test.Products_Categories as pc on p.ProductID = pc.ProductID
join test.Categories as c on pc.CategoryID = c.CategoryID
go
create unique clustered index IX_vw_Product_Category on test.vw_Product_Category (ProductID, CategoryID)
go

--Brands
create view test.vw_Product_Brand
with schemabinding
as
select p.ProductID, b.BrandID, b.Brand 
from test.Products as p
join test.Products_Brands as pb on p.ProductID = pb.ProductID
join test.Brands as b on pb.BrandID = b.BrandID
go
create unique clustered index IX_vw_Product_Brand on test.vw_Product_Brand (ProductID, BrandId)
go

--Types
create view test.vw_Product_Types
with schemabinding
as
select p.ProductID, t.typeid, t.[type]
from test.Products as p
join test.Products_Types as pt on p.ProductID = pt.ProductID
join test.Types as t on pt.TypeID = t.TypeID
go
create unique clustered index IX_vw_Product_Types on test.vw_Product_Types (ProductID, TypeId)
go

New search without dynamic sql:

declare @xml xml = 
'<FilterData>
    <Filter id="Category">
        <select id="1" value="HDTV"/>
        <select id="2" value="Receiver"/>
        <select id="3" value="Headphones"/>
        <select id="4" value="Blu-Ray"/>
    </Filter>
    <Filter id="Brand">
        <select id="1" value="Sony"/>
        <select id="2" value="Samsung"/>
    </Filter>
    <Filter id="Type">
        <select id="1" value="LCD"/>
        <select id="2" value="Plasma"/>
        <select id="3" value="Rear Projection"/>
        <select id="4" value="LED"/>
    </Filter>
</FilterData>';


;with SearchData as(
    select distinct
        vp.Product ,
        (select distinct ',' + vpc.Category from test.vw_Product_Category as vpc where vp.ProductID = vpc.ProductID for xml path('')) Categories,
        (select distinct ',' + vpb.Brand from test.vw_Product_Brand as vpb where vp.ProductID = vpb.ProductID for xml path('')) Brands,
        (select distinct ',' + vpt.[Type] from test.vw_Product_Types as vpt where vp.ProductID = vpt.ProductID for xml path('')) Types
    from test.vw_Products as vp
    where
     exists (
        select top 1 1 
        from @xml.nodes('/FilterData/Filter[@id="Category"]/select') f(n) 
        where f.n.value('@id', 'int') = vp.CategoryID
    )
    and exists (
        select top 1 1 
        from @xml.nodes('/FilterData/Filter[@id="Brand"]/select') f(n) 
        where f.n.value('@id', 'int') = vp.BrandID
    )
    and exists (
        select top 1 1 
        from @xml.nodes('/FilterData/Filter[@id="Type"]/select') f(n) 
        where f.n.value('@id', 'int') = vp.TypeID
    )
)
select 
    sd.Product,
    stuff(sd.Categories,1,1,'') Categories,
    stuff(sd.Brands,1,1,'') Brands,
    stuff(sd.Types,1,1,'') Types
from SearchData sd