For XML Explicit SQL复制元素

Question

我需要两个元素都显示为根的子元素。它们必须按字母顺序排列，因此elementA必须在elementZ之前。ElementZ应该只出现一次，而elementA需要出现多次。任何帮助都必须感谢。这是一个更大的查询的一个大大简化的部分，防止使用'path‘，所以我需要一个使用显式的解决方案。

非常感谢

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>

Answer 1

创建3标记时，不要重新选择ElementA。这将删除重复项。按标签排序也会将元素A放在顶部

    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

如果删除XML Explicit并查看表格输出，就会更容易看到出了什么问题

原创

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

修改为空，而不是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