For XML Explicit SQL duplicating elements

I need two elements both to appear as children of the root. They must be in alphabetical order so elementA must be before elementZ. ElementZ should appear only once whereas elementA needs to appear many times. Any help must appreciated. This is greatly simplified part of a much bigger query that prevents 'path' being used so I need a solution using explicit.

Thanks a lot

Declare  @xml xml 

DECLARE @tab table (
                   开发者_开发知识库 root_element nvarchar(10),
                    elementA nvarchar(10),
                    elementZ nvarchar(10)
                    )

insert @tab
(root_element, elementA, elementZ)
select 'one' , 'many', 'one' union all
select 'one' , 'many1', 'one' union all
select 'one' , 'many2', 'one' union all
select 'one' , 'many3', 'one' union all
select 'one' , 'many4', 'one' union all
select 'one' , 'many5', 'one' 

SET @xml =(
    SELECT DISTINCT
        TAG, 
        Parent,
        [root_element!1!value],
        [elementA!2!value],
        [elementZ!3!value]
    FROM
    (
    SELECT  DISTINCT
        1   AS TAG, 
        NULL    AS Parent,
        root_element AS [root_element!1!value],
        NULL AS [elementA!2!value],
        NULL AS [elementZ!3!value]
    FROM @tab
    UNION ALL
    SELECT  DISTINCT
        2,      
        1,          
        root_element,
        elementA,
        NULL
    FROM @tab
    UNION ALL
    SELECT  DISTINCT
        3,       
        1,          
        root_element,
        elementA,
        elementZ 
        FROM @tab
    )a
    ORDER BY 
    [root_element!1!value],
        [elementA!2!value],
        [elementZ!3!value]
    FOR XML EXPLICIT
    )

    select  @xml

    --results in this 
    '<root_element value="one">
  <elementA value="many" />
  <elementZ value="one" />
  <elementA value="many1" />
  <elementZ value="one" />
  <elementA value="many2" />
  <elementZ value="one" />
  <elementA value="many3" />
  <elementZ value="one" />
  <elementA value="many4" />
  <elementZ value="one" />
  <elementA value="many5" />
  <elementZ value="one" />
</root_element>'

--but i want this.
'<root_element value="one">
  <elementA value="many" />
  <elementA value="many1" />
  <elementA value="many2" />
  <elementA value="many3" />
  <elementA value="many4" />
  <elementA value="many5" />
  <elementZ value="one" />
</root_element>'
</code>

When you create the 3 Tag don't reselect ElementA. This will remove the duplicates. Ordering by tag will also put the elementA's at the top

    SELECT DISTINCT 
        TAG, 
        Parent,
        [root_element!1!value],
        [elementA!2!value],
        [elementZ!3!value]
    FROM
    (
    SELECT  DISTINCT
        1   AS TAG, 
        NULL    AS Parent,
        root_element AS [root_element!1!value],
        NULL AS [elementA!2!value],
        NULL AS [elementZ!3!value]
    FROM @tab
    UNION ALL
    SELECT  DISTINCT
        2,      
        1,          
        root_element,
        elementA,
        NULL
    FROM @tab


     UNION SELECT  DISTINCT
        3,       
        1,          
        root_element,
        NULL, --ElementA 
        elementZ 
        FROM @tab


    )a
    ORDER BY 
    TAG,
    [root_element!1!value],
        [elementA!2!value],
        [elementZ!3!value]
        FOR XML EXPLICIT

If you remove the XML Explicit and look at the tabular output its a little easier to see what was going wrong

Original

TAG         Parent      root_element!1!value elementA!2!value elementZ!3!value
----------- ----------- -------------------- ---------------- ----------------
1           NULL        one                  NULL             NULL
2           1           one                  many             NULL
3           1           one                  many             one
2           1           one                  many1            NULL
3           1           one                  many1            one
2           1           one                  many2            NULL
3           1           one                  many2            one
2           1           one                  many3            NULL
3           1           one                  many3            one
2           1           one                  many4            NULL
3           1           one                  many4            one
2           1           one                  many5            NULL
3           1           one                  many5            one

Modified to Null instead of ElementA

TAG         Parent      root_element!1!value elementA!2!value elementZ!3!value
----------- ----------- -------------------- ---------------- ----------------
1           NULL        one                  NULL             NULL
3           1           one                  NULL             one
2           1           one                  many             NULL
2           1           one                  many1            NULL
2           1           one                  many2            NULL
2           1           one                  many3            NULL
2           1           one                  many4            NULL
2           1           one                  many5            NULL