May 23, 2014 at 12:52 am
Hi there
I have a plain XML variable
<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>
When I insert this into a temp table, the order get mixed up.
SELECT I.value('@Id', 'INT') ProductId
INTO #ProductList
FROM @Products.nodes('/BS/B') AS T(I)
when I select out of #ProductList the order is random and different from the XML.
why does this happen?
May 23, 2014 at 1:04 am
Not certain what the problem is, testing your code with an addition of a row_number consistently brings back the results in the expected order.
😎
declare @Products xml = N'<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>';
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS PR_RID
,I.value('@Id', 'INT') ProductId
FROM @Products.nodes('/BS/B') AS T(I);
Results
PR_RID ProductId
-------- ----------
1 5
2 3
3 4
4 6
5 15
6 7
May 23, 2014 at 1:06 am
Hi there
I also fixed the problem with a Row Number, but I want to know why it reacts like this?
Why do the results get sorted randomly when and not stay in the sequence of the XML?
May 23, 2014 at 3:07 am
Because you have not specified an ORDER in your SELECT.
Without an ORDER BY clause, you are NEVER guaranteed any specific ordering.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 23, 2014 at 4:05 am
The order is important and must be respected in the XML but in the result set it is not. Therefore, use the row_number function to assign the "order" when shredding it to retain it in the result set.
😎
declare @Products xml = N'<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>';
SELECT
B.X.value('@Id', 'INT') ProductId
FROM @Products.nodes('/BS') AS T(I)
CROSS APPLY T.I.nodes('B') B(X);
May 23, 2014 at 9:53 am
The row_number function here is not deterministic, meaning the result could change even if it runs against the same data, because the ORDER BY clause is meaningless (ORDER BY NULL).
You could try ordering by the node you are extracting but it is a non-documented feature.
declare @Products xml = N'<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>';
SELECT
ROW_NUMBER() OVER (ORDER BY T.I) AS PR_RID
,I.value('@Id', 'INT') ProductId
FROM @Products.nodes('/BS/B') AS T(I);
GO
May 23, 2014 at 10:31 am
hunchback (5/23/2014)
The row_number function here is not deterministic, meaning the result could change even if it runs against the same data, because the ORDER BY clause is meaningless (ORDER BY NULL).You could try ordering by the node you are extracting but it is a non-documented feature.
[/code]
The parse-order or in other words, the actual position of the elements within the document should be respected by any standard compliant parser. This controls the order of appearance which then dictates the row number assignment. No order should be specified in the over clause as that alters the parse-order and hence, changes the properties of the elements (position).
This can easily be tested using the position() XQuery function.
😎
May 23, 2014 at 11:07 am
You know there is always the Tally table is you want to get an ordering identifier out of the XML in the first instance...
declare @Products xml = N'<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>';
;with N1(x) as (select 0 from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))a(x))
, N2(x) as (select 0 from N1 a, N1 b)
, Tally(N) as (select row_number() over(order by (select null)) from N2)
select N as PR_RID
, I.value('@Id', 'INT') ProductId
into #xml
from Tally
cross apply @Products.nodes('/BS/B[position()=sql:column("Tally.N")]') as T(I);
select PR_RID, ProductId
from #xml
order by PR_RID;
This will ensure that the PR_RID column gives you a guaranteed ordering column to put into your table, which can then be used in any subsequent SELECT/ORDER BY.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 23, 2014 at 12:48 pm
I don't doubt about the parser order but whatever goes beyond that does not have to be in accordance with the parser. A SELECT statement without ORDER BY does not guarantee the order of the output.
It would be handy if we can get the value from the function position() as part of the output.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply