SQL Server XML add multiple nodes of different types

Question

I am looking to create an output xml in this format but I'm not able to add the nodes Features and Categories under Child. I can add one at a time but not both.

<Parent>
    <Child>
        <Features>
            <Feature Id="f1" />
            <Feature Id="f2" />
            <Feature Id="f3" />
        </Features>
        <Categories>
            <Category Name="c1">
                <Feature>f1</Feature>
                <Feature>f2</Feature>
            </Category>
            <Category Name="c2">
                <Feature>f2</Feature>
                <Feature>f3</Feature>
            </Category>
            <Category Name="c3">
                <Feature>f2</Feature>
            </Category>
    </Child>
</Parent>

Here's my SQL to create the two XML strings with the <Features> node and <Category> node:

DECLARE @CategoryFeatures TABLE (CategoryId VARCHAR(5), FeatureId VARCHAR(5))

INSERT INTO @CategoryFeatures VALUES ('c1', 'f1')
INSERT INTO @CategoryFeatures VALUES ('c1', 'f2')
INSERT INTO @CategoryFeatures VALUES ('c2', 'f2')
INSERT INTO @CategoryFeatures VALUES ('c2', 'f3')
INSERT INTO @CategoryFeatures VALUES ('c3', 'f2')

SELECT 
(
    SELECT
    (
        SELECT [@Id] = FeatureId
        FROM CategoryFeatures
        GROUP BY FeatureId
        FOR XML PATH ('Feature'), ROOT ('Features'), TYPE)
    FOR XML PATH ('Child'), TYPE)
FOR XML PATH ('Parent')


SELECT 
(
    SELECT
    (
        SELECT [@Name] = cat.CategoryId,
        (
            SELECT Feature = cf.FeatureId
            FROM CategoryFeatures cf
            WHERE cf.CategoryId = cat.CategoryId
            FOR XML PATH (''), TYPE)
        FROM CategoryFeatures cat
        GROUP BY cat.CategoryId
        FOR XML PATH ('Category'), ROOT ('Categories'), TYPE)
    FOR XML PATH ('Child'), TYPE)
FOR XML PATH ('Parent')

How can I get these two nodes at the same level to make it look like the expected output above? Thank you.

This is a good question: You added sample data in consumable format, the expected output and your own attempt. +1 from my side! — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 25, 2019 at 8:51

sticky bit · Accepted Answer · 2019-01-24 23:36:07Z

1

One possibility is an orgy of subqueries.

SELECT (SELECT (SELECT cf1.featureid "Feature/@Id"
                       FROM (SELECT DISTINCT
                                    cf0.featureid
                                    FROM @categoryfeatures cf0) cf1
                       FOR XML PATH(''),
                               TYPE)
               FOR XML PATH('Features'),
                       TYPE),
       (SELECT (SELECT cf1.categoryid "Category/@Name",
                       (SELECT cf2.featureid "Feature"
                               FROM @categoryfeatures cf2
                               WHERE cf2.categoryid = cf1.categoryid
                               FOR XML PATH(''),
                                       TYPE) "Category"
                       FROM (SELECT DISTINCT
                                    cf0.categoryid
                                    FROM @categoryfeatures cf0) cf1
                       FOR XML PATH(''),
                               TYPE)
               FOR XML PATH('Categories'),
                       TYPE)
       FOR XML PATH('Child'),
               ROOT('Parent');

db<>fiddle

edited Jan 24, 2019 at 23:36

answered Jan 24, 2019 at 23:29

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Cappelletti Over a year ago

"Orgy of subqueries" ... Now that's a phrase I never had to see or hear :)

Gottfried Lesigang Over a year ago

I like the "Orgy of sub-queries" too, and you are right, this will need some sub-queries. But not quite as many as you've placed...

Gottfried Lesigang Over a year ago

@JohnCappelletti, This is a really nice expression I've to keep in mind ;-) I just imagine this together with xml namespaces :-) Btw: I've added a FLWOR solution... I know you like them ;-)

Gottfried Lesigang · Accepted Answer · 2019-01-25 09:04:24Z

btw: I like sticky bit's expression Orgy of sub-queries ;-)

This will need sub-queries, but can be put a bit simpler:

DECLARE @CategoryFeatures TABLE (CategoryId VARCHAR(5), FeatureId VARCHAR(5))

INSERT INTO @CategoryFeatures VALUES 
 ('c1', 'f1')
,('c1', 'f2')
,('c2', 'f2')
,('c2', 'f3')
,('c3', 'f2');

--The query will use some AS [Path/Path2/@Attr] to avoid nested queries...

SELECT 
( 
    SELECT cf.FeatureId AS [Feature/@Id]
    FROM @CategoryFeatures cf
    GROUP BY cf.FeatureId
    FOR XML PATH(''),TYPE
) AS [Child/Features]
,
(
    SELECT cf.CategoryId AS [Category/@Name]
          ,(
            SELECT cf2.FeatureId AS [*]
            FROM @CategoryFeatures cf2
            WHERE cf2.CategoryId=cf.CategoryId
            FOR XML PATH('Feature'),TYPE
           ) AS [Category]
    FROM @CategoryFeatures cf
    GROUP BY cf.CategoryId
    FOR XML PATH(''),TYPE
) AS [Child/Categories]
FOR XML PATH(''),ROOT('Parent');

The result:

<Parent>
  <Child>
    <Features>
      <Feature Id="f1" />
      <Feature Id="f2" />
      <Feature Id="f3" />
    </Features>
    <Categories>
      <Category Name="c1">
        <Feature>f1</Feature>
        <Feature>f2</Feature>
      </Category>
      <Category Name="c2">
        <Feature>f2</Feature>
        <Feature>f3</Feature>
      </Category>
      <Category Name="c3">
        <Feature>f2</Feature>
      </Category>
    </Categories>
  </Child>
</Parent>

UPDATE You can do the same with FLWOR

Try this

SELECT
(SELECT * FROM @CategoryFeatures FOR XML PATH('row'),ROOT('Parent'),TYPE)
.query
('
    <Parent>
    <Child>
    {
        <Features>
        {
            for $f in distinct-values(/Parent/row/FeatureId)
            return <Feature Id="{$f}" />
        }
        </Features>
    }
    {
        <Categories>
        {
            for $c in distinct-values(/Parent/row/CategoryId)
            return <Category Name="{$c}">
                   {
                    for $f in distinct-values(/Parent/row[CategoryId=$c]/FeatureId)
                    return <FeatureId>{$f}</FeatureId>
                   }
                   </Category>          
        }
        </Categories>
    }
    </Child>
    </Parent>
');

I still don't get FLWOR. I'm like a dog trying to understand Physics.

Collectives™ on Stack Overflow

SQL Server XML add multiple nodes of different types

2 Answers 2

3 Comments

UPDATE You can do the same with FLWOR

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

UPDATE You can do the same with FLWOR

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related