本文介紹了FOR XML 通過樹概念中的屬性進行多重控制的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
限時送ChatGPT賬號..
我想弄清楚一個問題.
我已經對簡單的訂購問題有疑問，但我想訂購更多詳細信息.在此鏈接下方檢查: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 }
}
}
}
}')
這篇關于FOR XML 通過樹概念中的屬性進行多重控制的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！