title: For XML Explicit SQL duplicating elements
body: <p>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.</p>
  
  <p>Thanks a lot</p>
  
  <pre><code>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 
      '&lt;root_element value="one"&gt;
    &lt;elementA value="many" /&gt;
    &lt;elementZ value="one" /&gt;
    &lt;elementA value="many1" /&gt;
    &lt;elementZ value="one" /&gt;
    &lt;elementA value="many2" /&gt;
    &lt;elementZ value="one" /&gt;
    &lt;elementA value="many3" /&gt;
    &lt;elementZ value="one" /&gt;
    &lt;elementA value="many4" /&gt;
    &lt;elementZ value="one" /&gt;
    &lt;elementA value="many5" /&gt;
    &lt;elementZ value="one" /&gt;
  &lt;/root_element&gt;'
  
  --but i want this.
  '&lt;root_element value="one"&gt;
    &lt;elementA value="many" /&gt;
    &lt;elementA value="many1" /&gt;
    &lt;elementA value="many2" /&gt;
    &lt;elementA value="many3" /&gt;
    &lt;elementA value="many4" /&gt;
    &lt;elementA value="many5" /&gt;
    &lt;elementZ value="one" /&gt;
  &lt;/root_element&gt;'
  &lt;/code&gt;
  </code></pre>