need to put an order by into a cursor declare that uses xml nodes

Question

So I am trying to insert some xml into my SQL database using a cursor and I would like to put an 'order by' statement into the cursor declaration, otherwise the optimiser decides to put all the nodes into alphabetical order.

My cursor declaration is:

declare cur1 cursor local for

    select
        P.DescriptionId, N.Description
    from (
        select
            T.C.value('.', 'nvarchar(max)') as Description
        from @TheRole.nodes('/descriptions/description') as T(C)


    ) as N
        left outer join PositionsDescriptions as P on  N.Description = P.Description

I tried to insert:

order BY P.$IDENTITY

As requested - here is the entire SP:

@Title                  nvarchar(50),
@Location               nvarchar(50),
@ShortDescription       nvarchar(max),
@MaximumSalary          nvarchar(max) = null,
@StatusId               int,
@DepartmentId           int,
@SubDepartmentId        int = null,
@TheRole                xml = null,
@Essentials             xml = null,
@Desirable              xml = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @PositionId as int

--DESCRIPTION TABLES
DECLARE @TableRole TABLE(Description nvarchar(MAX), OrderNumber int)
--DECLARE @TableRole                TABLE(RowID int not null primary key identity(1,1), Description nvarchar(MAX), OrderNumber int)
DECLARE @TableEssentials        TABLE(RowID int not null primary key identity(1,1), Description nvarchar(MAX), OrderNumber int)
DECLARE @TableDesirable         TABLE(RowID int not null primary key identity(1,1), Description nvarchar(MAX), OrderNumber int)
DECLARE @RowsToProcess          int,
@CurrentRow                     int,
@Description                    nvarchar(MAX),
@OrderNumber            int,


--DESCRIPTIONS
@DescriptionId          int
set @OrderNumber = 1

-- POSITIONS

if @SubDepartmentId = 0
    SET @SubDepartmentId = NULL

INSERT INTO Positions (Title, Location, Description, MaximumSalary, StatusId, SubDepartmentId, DepartmentId, Published, LastUpdatedDate)
VALUES (@Title, @Location, @ShortDescription, @MaximumSalary,@StatusId, @SubDepartmentId, @DepartmentId, GETDATE(), GETDATE())

SET @PositionId = scope_identity()

-- POSITIONSDESCRIPTIONS AND POSITIONS_DESCRIPTIONS
-- TheRole

declare cur1 cursor local for

    select
        P.DescriptionId, N.Description
    from (
        select
            T.C.value('.', 'nvarchar(max)') as Description
        from @TheRole.nodes('/descriptions/description') as T(C)


    ) as N
        left outer join PositionsDescriptions as P on  N.Description = P.Description


open cur1
while 1 = 1
begin
    fetch cur1 into @DescriptionId, @Description
    if @@fetch_status <> 0 break

    if @DescriptionId is null
        begin
            insert into PositionsDescriptions (Description)
            select @Description

            select @DescriptionId = scope_identity()

        end

    insert INTO Positions_Descriptions(PositionId, SectionId, DescriptionId, OrderNumber)
    VALUES (@PositionId, 1, @DescriptionId, @OrderNumber)
    set @OrderNumber = @OrderNumber + 1


end
close cur1
deallocate cur1

-- Essentials
declare cur1 cursor local fast_forward for
    select
        P.DescriptionId, N.Description
    from (
        select
            T.C.value('.', 'nvarchar(max)') as Description
        from @Essentials.nodes('/descriptions/description') as T(C)
    ) as N
        left outer join PositionsDescriptions as P on P.Description = N.Description


open cur1
while 1 = 1
begin
    fetch cur1 into @DescriptionId, @Description
    if @@fetch_status <> 0 break

    if @DescriptionId is null
        begin
            insert into PositionsDescriptions (Description)
            select @Description

            select @DescriptionId = scope_identity()

        end

    insert INTO Positions_Descriptions(PositionId, SectionId, DescriptionId, OrderNumber)
    VALUES (@PositionId, 2, @DescriptionId, @OrderNumber)
    set @OrderNumber = @OrderNumber + 1


end
close cur1
deallocate cur1

-- Desirable
declare cur1 cursor local fast_forward for
    select
        P.DescriptionId, N.Description
    from (
        select
            T.C.value('.', 'nvarchar(max)') as Description
        from @Desirable.nodes('/descriptions/description') as T(C)
    ) as N
        left outer join PositionsDescriptions as P on P.Description = N.Description

Declare @DesirablesOrderNumber int = 1
open cur1
while 1 = 1
begin
    fetch cur1 into @DescriptionId, @Description
    if @@fetch_status <> 0 break

    if @DescriptionId is null
        begin
            insert into PositionsDescriptions (Description)
            select @Description

            select @DescriptionId = scope_identity()

        end

    insert INTO Positions_Descriptions(PositionId, SectionId, DescriptionId, OrderNumber)
    VALUES (@PositionId, 3, @DescriptionId, @OrderNumber)
    set @OrderNumber = @OrderNumber + 1

end
close cur1
deallocate cur1

END

If there is a better way than a cursor, I'd love to know! This is someone else's code I'm trying to fix and I'm new to SQL so apologies if I'm a little slow.

For more information, the tables mentioned above are PositionsDescriptions and Positions_Descriptions.

PositionsDescriptions that would look something like this:

DescriptionId Description

1                    test

2                    abc

And this data is related to a job (or position) in a table called Positions_Descriptions, that would look something like this:

PositionId  SectionId  DescriptionId OrderNumber

1               1               1              1

1               1               2              2

The problem is that if you have (for example) something like this:

@TheRole = "<descriptions><description>test</description><description>abc</description></descriptions>"

then the information is not stored in that order. But stored so that the descriptions come back in alphabetical order.

In other words you get this

PositionId  SectionId  DescriptionId OrderNumber

1               1               2              1

1               1               1              2

And not this:

PositionId  SectionId  DescriptionId OrderNumber

1               1               1              1

1               1               2              2

Why is the Order of Insertion important? Also, why do you think that you need a Cursor? — RBarryYoung, Nov 27 '13 at 17:47
It's important because at the moment the nodes are being saved in alphabetical order - I don't want this. I just want them to be saved in the order they are given. I need a cursor because I have to check each description to make sure it's not already in a table in the database. — Gemma Church, Nov 27 '13 at 17:53
Neither of these make sense. Again, *why* do you need them in that order? Are you aware that SQL server does not preserve nor return the rows in any order on its own? Also, preventing duplicate insertion is readily do-able without resorting to cursors. Show us the rest of the proc and we'll show you how. — RBarryYoung, Nov 27 '13 at 17:56
OK - I've edited the question with the entire SP and some more information. Hopefully that makes more sense? — Gemma Church, Nov 27 '13 at 18:03
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Nov 27 '13 at 18:11
OK, reading through it now. Which is the column that you need to preserve the order, `@DescriptionID` from `PositionsDescriptions` identity column, or `Positions_Descriptions.OrderNumber`? — RBarryYoung, Nov 27 '13 at 18:39

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Without an order by clause your rows can be returned in any way the optimizer sees fit.

In your case that means that the column OrderNumber may not match the order of the elements in the XML.

There is a trick to order XML elements extracted by nodes() using row_number(). See Does the nodes() method keep the document order? for more info.

Using this cursor definition your rows will be processed in the order they are stored in the XML.

declare cur1 cursor local for
    select
        P.DescriptionId, N.Description
    from (
        select
            T.C.value('.', 'nvarchar(max)') as Description,
            row_number() over(order by T.C) as rn
        from @TheRole.nodes('/descriptions/description') as T(C)
    ) as N
        left outer join PositionsDescriptions as P on  N.Description = P.Description
    order by N.rn

SQL Fiddle

score 1 · Answer 2 · answered Nov 27 '13 at 18:52

Mikael Eriksson has already covered the ordering issue. The way that you can INSERT into a table, excluding any duplicates, without having to use a Cusror is to add a WHERE EXISTS clause to the INSERT..SELECT that filters out those that already exist.

Like so:

INSERT INTO PositionsDescriptions( Description )
SELECT  T.C.value('.', 'nvarchar(max)')
FROM    @TheRole.nodes('/descriptions/description') as T(C)
WHERE   NOT EXISTS 
        (
            SELECT * From PositionsDescriptions N
            WHERE T.C.value('.', 'nvarchar(max)') = N.Description
        )

If you need to protect against duplicates within the same source, you can add a DISTINCT to the outer SELECT.

need to put an order by into a cursor declare that uses xml nodes

2 Answers2