I want to figure out one issue.
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
I made a example case.
SQL Query.
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
Result :
<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>
Expected result :
<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>
final result :
<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>
That's order by tree-order.
finally I don't want to show order information in attribute
Any one has great Idea?
Thank you for everybody who interesting to this.
Updated ----------------------------------------
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