FOR XML 通过树概念中的属性进行多重控制

2021-10-02 00:00:00 xml sql xquery sql-server sqlxml

我想弄清楚一个问题.

我已经对简单的订购问题有疑问，但我想订购更多详细信息.在此链接下方检查:SQL Server : FOR XML 按属性排序控制

I already had question about simple ordering issue but I want to order more detail. check below this link : 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>

这是按树序排列的.

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

finally I don't want to show order information in attribute

有人有好主意吗?

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

Thank you for everybody who interesting to this.

更新----------------------------------------

Updated ----------------------------------------

谢谢大家，最后我解决了以下关于 order by 和 remove 属性问题的问题:

Thank you every body finally I solved problem as below about order by and remove attribute issue : 

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

推荐答案

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 }
        }
      }
    }  
  }')

相关文章