問題描述

我想弄清楚一個問題.

我已經(jīng)對簡單的訂購問題有疑問，但我想訂購更多詳細(xì)信息.在此鏈接下方檢查: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

結(jié)果:

<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>

預(yù)期結(jié)果:

<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>

最終結(jié)果:

<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 ----------------------------------------

謝謝大家，最后我解決了以下關(guān)于 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 }
        }
      }
    }  
  }')

這篇關(guān)于FOR XML 通過樹概念中的屬性進行多重控制的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！