Insert XML nodes in order defined by the XML structure

Question

I have a table variable with the folling columns:

[ID] TINYINT
[XML] XML

The XML structure looks like:

 <conditions>

    <condition type='expresion'>
        ...
    </condition>
    <condition type='operand'>
        ...
    </condition>
    <condition type='expresion'>
        ...
    </condition>
    <condition type='operand'>
        ...
    </condition>
    <condition type='expresion'>
        ...
    </condition>

</conditions>

I need to extract the information above and populate a table with the following sturcuture:

[ID]
[ConditionID]
[ConditionXML]

where ConditionID will be numeric column and ConditonXML will be extrated from the original XML.

What is very important to me is to generate ConditionID values in way to represent the order in the original XML structure.

I have read that this could be done using ROW_NUMBER like this:

SELECT [ID]
        ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY T.c) AS T
        ,T.c.query('.')
FROM @Test
CROSS APPLY [CondtionsXML].nodes('conditions/condition') T(c)
ORDER BY [ID], T

but in the official documentation is said, that:

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true:

1. Values of the partitioned column are unique.
2. Values of the ORDER BY columns are unique.
3. Combinations of values of the partition column and ORDER BY columns are unique.

and since my operand nodes can be the same I am worried I may have some issues. Also, if I remove the PARTITON BY clause, the things goes wrong.

Other tenique I could use is to create a separete tale with INDENTITY column and insert values into it like this:

DECLARE @TEST1 TABLE
(
     [ID] SMALLINT IDENTITY(1,1)
    ,[ParentID] TINYINT
    ,[XML] XML
)

INSERT INTO @TEST1 ([ParentID], [XML])
SELECT [ID]
      ,T.c.query('.')
FROM @Test
CROSS APPLY [CondtionsXML].nodes('conditions/condition') T(c)

Could anyone say if this is the way to insert XML nodes in a table in the order they are represented in the XML structurer.

Answer 1

I think the documentation is wrong and I can not show you some other documentation that backs me up on this.

This is what I think it should be. I emphasized the change.

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be numbered exactly the same with each execution unless one of the following conditions are true:

1. Values of the partitioned column are unique.

If this condition is true then row_number() will return 1 for all rows. It will always do that and nothing can change that behaviour so this condition alone guarantees that the result is the same for every execution.

2. Values of the ORDER BY columns are unique.

If values in the order by are unique and you don't have a partition by clause then every value returned by row_number() will be unique in the result set and it will always be returned exactly the same for each and every execution of the query.

3. Combinations of values of the partition column and ORDER BY columns are unique.

This case is where you have a partition by clause and a order by clause. row_number() is starting from one for each partition and if the value used in the order by clause is unique within a partition the numbering will be the same for every execution.

Answer 2

You can use (select 1) construction. It says to sort the result set as is.

select [ID]
    , row_number() over (partition by [ID] order by (select 1) ) as T
    , T.c.query('.')
from @Test
cross apply [XML].nodes ('/conditions[1]/condition')T(c)