Recursive join? SQL query to extract tree diagram (all possible combinations) out of self-inheriting table

Question

I've read many post on the topic, but can't seem to quite get want I want.

Having the four tables: Product – Product_Version – Product_Version_Rule – Rule and creating a recordset accordingly...

(Notice the self-inheriting InheritsFromID. Enabling a so called Product Chain. In theory Product could inherit itself infinitely, i.e. making a never ending Product Chain, however IRL a Product inheriting-chain is no longer then three to five Product heritage.)

SELECT
    Product.ProductID AS ID,
    Product.InheritsFromID,
    Product.Name AS ProductName,
    Product_Version.Name AS VersionName,
    -- Note, I have no real use for table *Product_Version_Rule*,
    -- I'm just join-using it below to get to *Rule*(Name).
    [Rule].Name AS RuleName,
    [Rule].Value AS RuleValue
FROM
(
    (
        Product 
        FULL OUTER JOIN Product_Version
            ON Product.ProductID = Product_Version.ProductID
    )
    FULL OUTER JOIN Product_Version_Rule
        ON Product_Version.ProductVersionID
            = Product_Version_Rule.ProductVersionID
)
LEFT JOIN [Rule]
    ON Product_Version_Rule.RuleID = [Rule].RuleID

... resulting in...

row |  ID  InheritsFromID  ProductName     VersionName  RuleName  RuleValue |  note
----+-----------------------------------------------------------------------+------
 1  |  1   NULL            ProdTemplateA   (0)          a         5         |  *
 2  |  1   NULL            ProdTemplateA   (0)          b         15        |  *
 3  |  2   1               ProdComponentA  (0)          d         3         |  **
 4  |  2   1               ProdComponentA  (0)          c         11        |  **
 5  |  3   2               ProdEndA        (0)          s         1         |  ***
 6  |  3   2               ProdEndA        (1)          t         hello     |  ***
 7  |  4   NULL            ProdTemplateB   (0)          a         3         |  *
 8  |  5   4               ProdEndB        (1)          c         21        |  ***

* A "Start"-productcomponent since it does not inherits from a parent.

** A "Middle"-productcomponent since it both inherits from parent and has an inheriting child.

*** An "End"-productcomponent since no one inherits from it.

However I would like to get it presented hierarchically, preferably in XML. But finishing the query with just FOR XML AUTO, ELEMENTS does not do the trick since I want a nested resultset, i.e:

<Product>
  <ID>1</ID>
  <InheritsFromID/>
  <ProductName>ProdTemplateA</ProductName>
  <Product_Version>
    <VersionName>(0)</VersionName>
    <Rule>
      <RuleName>a</RuleName>
      <RuleValue>5</RuleValue>
    </Rule>
    <Rule>
      <RuleName>b</RuleName>
      <RuleValue>15</RuleValue>
    </Rule>
  </Product_Version>
  <Product>
    <ID>2</ID>
    <InheritsFromID>1</InheritsFromID>
    <ProductName>ProdComponentA</ProductName>
    <Product_Version>
      <VersionName>(0)</VersionName>
      <Rule>
        <RuleName>d</RuleName>
        <RuleValue>3</RuleValue>
      </Rule>
      <Rule>
        <RuleName>c</RuleName>
        <RuleValue>13</RuleValue>
      </Rule>
    </Product_Version>
    <Product>
      <ID>3</ID>
      <InheritsFromID>2</InheritsFromID>
      <ProductName>ProdEndA</ProductName>
      <Product_Version>
        <VersionName>(0)</VersionName>
        <Rule>
          <RuleName>s</RuleName>
          <RuleValue>1</RuleValue>
        </Rule>
      </Product_Version>
      <Product_Version>
        <VersionName>(1)</VersionName>
        <Rule>
          <RuleName>t</RuleName>
          <RuleValue>hello</RuleValue>
        </Rule>
      </Product_Version>
    </Product>
  </Product>
</Product>
<Product>
  <ID>4</ID>
  <InheritsFromID/>
  <ProductName>ProdTemplateB</ProductName>
  <Product_Version>
    <VersionName>(0)</VersionName>
    <Rule>
      <RuleName>a</RuleName>
      <RuleValue>3</RuleValue>
    </Rule>
  </Product_Version>
  <Product>
    <ID>5</ID>
    <InheritsFromID>4</InheritsFromID>
    <ProductName>ProdEndB</ProductName>
    <Product_Version>
      <VersionName>(1)</VersionName>
      <Rule>
        <RuleName>c</RuleName>
        <RuleValue>21</RuleValue>
      </Rule>
    </Product_Version>
  </Product>
</Product>

Any thought on how I can achieve above structure?

I'm thinking CTE but can't really get my head around it.

CTE's won't do the trick. Take a look at: http://stackoverflow.com/questions/14765937/cte-and-for-xml-to-generate-nested-xml — Dan, Aug 23 '13 at 11:42

score 0 · Answer 1 · answered Jul 07 '14 at 21:15

Assumed schema:

DROP TABLE Product
DROP TABLE Product_Version
DROP TABLE Product_Version_Rule
DROP TABLE [Rule] 

CREATE TABLE Product (ProductID INT, InheritsFromID INT, Name VARCHAR(100))
CREATE TABLE Product_Version
    (ProductVersionID INT, ProductID INT, Name VARCHAR(100))
CREATE TABLE Product_Version_Rule (ProductVersionID INT, RuleID INT)
CREATE TABLE [Rule] (RuleId INT, Name VARCHAR(100), Value VARCHAR(100))

Sample data:

INSERT Product VALUES
    (1, NULL, 'ProdTemplateA'),
    (2, 1, 'ProdComponentA'),
    (3, 2, 'ProdEndA'),
    (4, NULL, 'ProdTemplateB'),
    (5, 4, 'ProdEndB')

INSERT Product_Version VALUES
    (1, 1, '(0)'),
    (2, 2, '(0)'),
    (3, 3, '(0)'),
    (4, 3, '(1)'),
    (5, 4, '(0)'),
    (6, 5, '(1)')


INSERT [Rule] VALUES
    (1, 'a', '5'),
    (2, 'b', '15'),
    (3, 'd', '3'),
    (4, 'c', '11'),
    (5, 's', '1'),
    (6, 't', 'hello'),
    (7, 'a', '3'),
    (8, 'c', '21')


INSERT Product_Version_Rule VALUES
    (1, 1),
    (1, 2),
    (2, 3),
    (2, 4),
    (3, 5),
    (4, 6),
    (5, 7),
    (6, 8)

Recursive user defined function:

CREATE FUNCTION dbo.ProductXML(@ParentProductID INT)
RETURNS XML
AS
BEGIN
    RETURN (
        SELECT
            ProductID AS ID,
            (
                SELECT Product.InheritsFromID
                FOR XML PATH ('InheritsFromID'), TYPE
            ),
            Name AS [ProductName],
            (
                SELECT
                    Product_Version.Name AS [VersionName],
                    (
                        SELECT
                            [Rule].Name AS [RuleName],
                            [Rule].Value AS [RuleValue]
                        FROM Product_Version_Rule
                        LEFT JOIN [Rule]
                            ON Product_Version_Rule.RuleID = [Rule].RuleID
                        WHERE Product_Version.ProductVersionID
                            = Product_Version_Rule.ProductVersionID
                        FOR XML PATH ('Rule'), TYPE
                    )
                FROM Product_Version
                WHERE Product.ProductID = Product_Version.ProductID
                FOR XML PATH (''), ROOT ('Product_Version'), TYPE
            ),
            (
                SELECT dbo.ProductXML(ProductID)
            )
        FROM Product
        WHERE InheritsFromID = @ParentProductID
        OR (InheritsFromID IS NULL AND @ParentProductID IS NULL)
        FOR XML PATH ('Product'), TYPE
    )
END

Usage:

SELECT dbo.ProductXML(NULL)

Output:

<Product>
  <ID>1</ID>
  <InheritsFromID />
  <ProductName>ProdTemplateA</ProductName>
  <Product_Version>
    <VersionName>(0)</VersionName>
    <Rule>
      <RuleName>a</RuleName>
      <RuleValue>5</RuleValue>
    </Rule>
    <Rule>
      <RuleName>b</RuleName>
      <RuleValue>15</RuleValue>
    </Rule>
  </Product_Version>
  <Product>
    <ID>2</ID>
    <InheritsFromID>
      <InheritsFromID>1</InheritsFromID>
    </InheritsFromID>
    <ProductName>ProdComponentA</ProductName>
    <Product_Version>
      <VersionName>(0)</VersionName>
      <Rule>
        <RuleName>d</RuleName>
        <RuleValue>3</RuleValue>
      </Rule>
      <Rule>
        <RuleName>c</RuleName>
        <RuleValue>11</RuleValue>
      </Rule>
    </Product_Version>
    <Product>
      <ID>3</ID>
      <InheritsFromID>
        <InheritsFromID>2</InheritsFromID>
      </InheritsFromID>
      <ProductName>ProdEndA</ProductName>
      <Product_Version>
        <VersionName>(0)</VersionName>
        <Rule>
          <RuleName>s</RuleName>
          <RuleValue>1</RuleValue>
        </Rule>
        <VersionName>(1)</VersionName>
        <Rule>
          <RuleName>t</RuleName>
          <RuleValue>hello</RuleValue>
        </Rule>
      </Product_Version>
    </Product>
  </Product>
</Product>
<Product>
  <ID>4</ID>
  <InheritsFromID />
  <ProductName>ProdTemplateB</ProductName>
  <Product_Version>
    <VersionName>(0)</VersionName>
    <Rule>
      <RuleName>a</RuleName>
      <RuleValue>3</RuleValue>
    </Rule>
  </Product_Version>
  <Product>
    <ID>5</ID>
    <InheritsFromID>
      <InheritsFromID>4</InheritsFromID>
    </InheritsFromID>
    <ProductName>ProdEndB</ProductName>
    <Product_Version>
      <VersionName>(1)</VersionName>
      <Rule>
        <RuleName>c</RuleName>
        <RuleValue>21</RuleValue>
      </Rule>
    </Product_Version>
  </Product>
</Product>

Recursive join? SQL query to extract tree diagram (all possible combinations) out of self-inheriting table

1 Answers1