Quering from XML Type
-
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') ProductIdINTO #ProductListFROM @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?
-
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>';SELECTROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS PR_RID,I.value('@Id', 'INT') ProductIdFROM @Products.nodes('/BS/B') AS T(I);Results
PR_RID ProductId-------- ----------1 52 33 44 65 156 7 -
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?
-
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); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
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);
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
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.
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);