树概念中基于属性的XML多重控制

Question

我想找出一个问题。

我已经对简单的订购问题提出了疑问，但我想订购更多的细节。检查下面的链接：SQL Server : FOR XML sorting control by attribute

我举了个例子。

SQL查询

select (
    select '123' AS '@id', ( 
        select 
        (
            select 'test' AS '@testid' , '20' AS '@order'
            FOR XML path ('tree') , TYPE
        ),
        (
            select 'test2' AS '@testid' , '30' AS '@order'
            FOR XML path ('tree-order') , TYPE
        ),
        (
            select 'test' AS '@testid' , '10' AS '@order'
            FOR XML path ('tree') , TYPE
        )
        FOR XML path ('Node') , TYPE
    )
    FOR XML path ('Sample') , TYPE
    ),
    (select '456' AS '@id', ( 
        select 
        (
            select 'test' AS '@testid' , '20' AS '@order'
            FOR XML path ('tree') , TYPE
        ),
        (
            select 'test2' AS '@testid' , '30' AS '@order'
            FOR XML path ('tree-order') , TYPE
        ),
        (
            select 'test' AS '@testid' , '10' AS '@order'
            FOR XML path ('tree') , TYPE
        )
        FOR XML path ('Node') , TYPE
    )
    FOR XML path ('Sample') , TYPE)
FOR XML path ('Main') , TYPE

结果：

<Main>
  <Sample id="123">
    <Node>
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
      <tree testid="test" order="10" />
    </Node>
  </Sample>
  <Sample id="456">
    <Node>
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
      <tree testid="test" order="10" />
    </Node>
  </Sample>
</Main>

预期结果：

<Main>
  <Sample id="123">
    <Node>
      <tree testid="test" order="10" />
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
    </Node>
  </Sample>
  <Sample id="456">
    <Node>
      <tree testid="test" order="10" />
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
    </Node>
  </Sample>
</Main>

最终结果：

<Main>
  <Sample id="123">
    <Node>
      <tree testid="test" />
      <tree testid="test" />
      <tree-order testid="test2" />
    </Node>
  </Sample>
  <Sample id="456">
    <Node>
      <tree testid="test" />
      <tree testid="test" />
      <tree-order testid="test2" />
    </Node>
  </Sample>
</Main>

这是一棵一棵树的秩序。

最后，我不想在属性中显示订单信息

有人有好主意吗？

感谢所有对此感兴趣的人。

更新

谢谢大家，最后我解决了以下关于有序和移除属性问题的问题：

declare @resultData xml = (select @data.query('
  element Main {
    for $s in Main/Sample
    return element Sample {
      $s/@*,
      for $n in $s/Node
      return element Node {
        for $i in $n/* 
        order by $i/@order
        return $i 
      }
    }  
  }'));

  SET @resultData.modify('delete (Main/Sample/Node/tree/@order)');
  SET @resultData.modify('delete (Main/Sample/Node/tree-order/@order)');

  select @resultData

Answer 1

select @data.query('
  element Main {
    for $s in Main/Sample
    return element Sample {
      $s/@*,
      for $n in $s/Node
      return element Node {
        for $i in Node/* 
        order by $i/@order
        return 
          if ($i/self::tree)
          then element tree { $i/@testid }
          else element tree-order { $i/@testid }
        }
      }
    }  
  }')

Answer 2

我感兴趣的是，在您的original post中，您是作为一个SQL查询的结果来生成XML的。如果是我的话，我会控制那个级别的订单。