none
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.

    B

     

    Monday, September 13, 2010 3:05 PM

Answers

  • You can do this type of thing with XML Location Paths, although it can be rather complicated and I wouldn't recommend it for an SQL Server / XML beginner.  Demo below.  This demo works for your test data but would need a fair bit of testing to ensure it returns valid results and behaves in the way you expect.  The demo script includes a lot of your original setup code but is designed to run end-to-end.

    Good luck!

    USE tempdb
    GO
    
    IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'test' )
    EXEC ( 'CREATE SCHEMA test' )
    GO
    
    
    --------------------------------------------------------
    --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:
    
     
    IF OBJECT_ID('tempdb..tvw_productXML') IS NOT NULL
    DROP VIEW dbo.tvw_productXML
    GO
    CREATE VIEW dbo.tvw_productXML
    
    AS
    
    SELECT *
    FROM
    (
    SELECT 
    		productId AS "@productId",
    		product AS "@name",
    		(
    		SELECT c.category AS "@name"
    		FROM [test].[Products_Categories] pc
    			INNER JOIN [test].[Categories] c ON pc.categoryId = c.categoryId
    		WHERE p.productId = pc.productId
    		FOR XML PATH('category'), TYPE
    		) AS "categories",
    		(
    		SELECT b.brand AS "@name"
    		FROM [test].[Products_Brands] pc
    			INNER JOIN [test].[Brands] b ON pc.brandId = b.brandId
    		WHERE p.productId = pc.productId
    		FOR XML PATH('brand'), TYPE
    		) AS "brands",
    		(
    		SELECT t.type AS "@name"
    		FROM [test].[Products_types] pc
    			INNER JOIN [test].[types] t ON pc.typeId = t.typeId
    		WHERE p.productId = pc.productId
    		FOR XML PATH('type'), TYPE
    		) AS "types"
    
    	FROM [test].[Products] p
    	FOR XML PATH('product'), ROOT('products'), TYPE
    ) x(productXML)
    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>';
    
    -- Create you schema as XML (save this as a view for reuse)
    DECLARE @xml XML
    
    SELECT @xml = productXML
    FROM dbo.tvw_productXML
    
    -- Combine filter and product XML 
    SELECT @xml = 
    	(
    	SELECT @XMLInput AS "*", @xml AS "*"
    	FOR XML PATH('')
    	)
    
    -- Now use XML Location path to filter the data
    SELECT 'All', @xml.query('
    products/product
    	[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    	[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    	[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    	')
    
    -- Shred the XML
    SELECT 
    	a.b.value( '@name', 'VARCHAR(50)' ) AS Products,
    	a.b.query( 'data(categories/category/@name)' ) Categories,
    	a.b.query( 'data(brands/brand/@name)' ) Brands,
    	a.b.query( 'data(types/type/@name)' ) [Types]
    FROM
    	(
    	SELECT @xml.query('
    	products/product
    		[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    		[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    		[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    		')
    	) x(y)
    		CROSS APPLY x.y.nodes('product') a(b)
    GO
     
    -- Try with a different filter
    DECLARE @XMLInput XML = '<FilterData>
        <Filter id="Category">
        <select id="4" value="Blu-Ray"/>
        </Filter>
       </FilterData>';
    
    DECLARE @xml XML
    
    SELECT @xml = productXML
    FROM dbo.tvw_productXML
    
    -- Combine filter and product XML 
    SELECT @xml = 
    	(
    	SELECT @XMLInput AS "*", @xml AS "*"
    	FOR XML PATH('')
    	)
    
    -- Now use XML Location path to filter the data
    SELECT 'Blu-Ray', @xml.query('
    products/product
    	[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    	[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    	[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    	')
    GO
    
    
    -- Try with a different filter
    DECLARE @XMLInput XML = '<FilterData>
        <Filter id="Type">
        <select id="2" value="Plasma"/>
        </Filter>
       </FilterData>';
    
    DECLARE @xml XML
    
    SELECT @xml = productXML
    FROM dbo.tvw_productXML
    
    -- Combine filter and product XML 
    SELECT @xml = 
    	(
    	SELECT @XMLInput AS "*", @xml AS "*"
    	FOR XML PATH('')
    	)
    
    -- Now use XML Location path to filter the data
    SELECT 'Plasma', @xml.query('
    products/product
    	[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    	[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    	[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    	')
    
    
    
    • Marked as answer by BenHamiin Monday, September 13, 2010 6:14 PM
    Monday, September 13, 2010 5:41 PM

All replies

  • Just a quick observation regarding current approach - the temp table don't have to be global as you create them before calling dynamic SQL, therefore they will be visible to it.

    BTW, this may be irrelevant to the question, but take a look at 

    http://www.codeproject.com/KB/custom-controls/SqlWhereBuilder.aspx


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 13, 2010 3:11 PM
    Moderator
  • thanks for the tip...not sure why I thought they had to be global temps to ref in my Dynamic SQL.

     

    --B

    Monday, September 13, 2010 3:59 PM
  • You can do this type of thing with XML Location Paths, although it can be rather complicated and I wouldn't recommend it for an SQL Server / XML beginner.  Demo below.  This demo works for your test data but would need a fair bit of testing to ensure it returns valid results and behaves in the way you expect.  The demo script includes a lot of your original setup code but is designed to run end-to-end.

    Good luck!

    USE tempdb
    GO
    
    IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'test' )
    EXEC ( 'CREATE SCHEMA test' )
    GO
    
    
    --------------------------------------------------------
    --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:
    
     
    IF OBJECT_ID('tempdb..tvw_productXML') IS NOT NULL
    DROP VIEW dbo.tvw_productXML
    GO
    CREATE VIEW dbo.tvw_productXML
    
    AS
    
    SELECT *
    FROM
    (
    SELECT 
    		productId AS "@productId",
    		product AS "@name",
    		(
    		SELECT c.category AS "@name"
    		FROM [test].[Products_Categories] pc
    			INNER JOIN [test].[Categories] c ON pc.categoryId = c.categoryId
    		WHERE p.productId = pc.productId
    		FOR XML PATH('category'), TYPE
    		) AS "categories",
    		(
    		SELECT b.brand AS "@name"
    		FROM [test].[Products_Brands] pc
    			INNER JOIN [test].[Brands] b ON pc.brandId = b.brandId
    		WHERE p.productId = pc.productId
    		FOR XML PATH('brand'), TYPE
    		) AS "brands",
    		(
    		SELECT t.type AS "@name"
    		FROM [test].[Products_types] pc
    			INNER JOIN [test].[types] t ON pc.typeId = t.typeId
    		WHERE p.productId = pc.productId
    		FOR XML PATH('type'), TYPE
    		) AS "types"
    
    	FROM [test].[Products] p
    	FOR XML PATH('product'), ROOT('products'), TYPE
    ) x(productXML)
    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>';
    
    -- Create you schema as XML (save this as a view for reuse)
    DECLARE @xml XML
    
    SELECT @xml = productXML
    FROM dbo.tvw_productXML
    
    -- Combine filter and product XML 
    SELECT @xml = 
    	(
    	SELECT @XMLInput AS "*", @xml AS "*"
    	FOR XML PATH('')
    	)
    
    -- Now use XML Location path to filter the data
    SELECT 'All', @xml.query('
    products/product
    	[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    	[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    	[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    	')
    
    -- Shred the XML
    SELECT 
    	a.b.value( '@name', 'VARCHAR(50)' ) AS Products,
    	a.b.query( 'data(categories/category/@name)' ) Categories,
    	a.b.query( 'data(brands/brand/@name)' ) Brands,
    	a.b.query( 'data(types/type/@name)' ) [Types]
    FROM
    	(
    	SELECT @xml.query('
    	products/product
    		[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    		[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    		[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    		')
    	) x(y)
    		CROSS APPLY x.y.nodes('product') a(b)
    GO
     
    -- Try with a different filter
    DECLARE @XMLInput XML = '<FilterData>
        <Filter id="Category">
        <select id="4" value="Blu-Ray"/>
        </Filter>
       </FilterData>';
    
    DECLARE @xml XML
    
    SELECT @xml = productXML
    FROM dbo.tvw_productXML
    
    -- Combine filter and product XML 
    SELECT @xml = 
    	(
    	SELECT @XMLInput AS "*", @xml AS "*"
    	FOR XML PATH('')
    	)
    
    -- Now use XML Location path to filter the data
    SELECT 'Blu-Ray', @xml.query('
    products/product
    	[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    	[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    	[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    	')
    GO
    
    
    -- Try with a different filter
    DECLARE @XMLInput XML = '<FilterData>
        <Filter id="Type">
        <select id="2" value="Plasma"/>
        </Filter>
       </FilterData>';
    
    DECLARE @xml XML
    
    SELECT @xml = productXML
    FROM dbo.tvw_productXML
    
    -- Combine filter and product XML 
    SELECT @xml = 
    	(
    	SELECT @XMLInput AS "*", @xml AS "*"
    	FOR XML PATH('')
    	)
    
    -- Now use XML Location path to filter the data
    SELECT 'Plasma', @xml.query('
    products/product
    	[categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]
    	[brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]
    	[types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]
    	')
    
    
    
    • Marked as answer by BenHamiin Monday, September 13, 2010 6:14 PM
    Monday, September 13, 2010 5:41 PM
  • Thanks wBob that is sweet.

     

    --B

    Monday, September 13, 2010 6:14 PM
  • wBob,

     

    One question in this piece:

     

    products/product

    [categories/category/@name=(/FilterData/Filter[@id="Category"]/select/@value, categories/category/@name)[1]]

    [brands/brand/@name=(/FilterData/Filter[@id="Brand"]/select/@value, brands/brand/@name)[1]]

    [types/type/@name=(/FilterData/Filter[@id="Type"]/select/@value, types/type/@name )[1]]

     

    What does the [1] denote in the logic? Is it an xml node (array) pointer?

     

    Thanks again!

    --B

    Monday, September 13, 2010 6:40 PM
  • This is known as an ordinal or positional predicate, it can [1], [2], [7] etc and basically means get me the 1st, 2nd or 7th item from the preceding sequence.  In our example above, the sequence, as indicated by the bracketted comma-separated list is behaving a bit like ISNULL.

     

    Start here:

    Introduction to XQuery in SQL Server 2005
    http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx

    xml Data Type Methods
    http://msdn.microsoft.com/en-us/library/ms190798(SQL.90).aspx

    Tuesday, September 14, 2010 11:49 AM