Generate XML adding a custom value on a table's column

Question

I have this table:

CREATE TABLE [dbo].[INCREMENTAL_TABLE](
    [GTIN] [bigint] NOT NULL,
    [PRESENTATION] [tinyint] NOT NULL,
    [LEVEL] [bigint] NOT NULL,
    [INCREMENTAL] [bigint] NOT NULL DEFAULT(0)
 CONSTRAINT [PK_INCREMENTAL_TABLE_1] PRIMARY KEY CLUSTERED 
(
    [GTIN] ASC,
    [PRESENTATION] ASC,
    [LEVEL] ASC
) ON [PRIMARY]

And now, I'm creating a stored procedure:

CREATE PROCEDURE MyProc
    @gint bigint,   
    @pres tinyint,
    @level bigint,
    @quantity smallint
AS

    DECLARE @current_incremental bigint
    DECLARE @counter bigint

    -- Get current incremental.
    set @current_incremental = 
        (SELECT INCREMENTAL
            FROM INCREMENTAL_TABLE
            WHERE GTIN = @gint AND
              PRESENTATION = @pres AND
              LEVEL = @level)

    -- 
    SET @counter = @current_incremental
    WHILE ((@counter - @current_incremental) <= @quantity)
        BEGIN

            SET @counter = @counter + 1
        END


GO

Inside this stored procedure I have to create a XML with @quantity nodes. Imagine I have this call:

EXEC MyProc @gint = 1 @pres = 2 @level = 3 @quantity = 100

And, I have this initial value:

@current_incremental = 10

With these data, I have to return a xml with these values:

GTIN | PRESENTATION | LEVEL | INCREMENTAL
-----+--------------+-------+------------
  1  |       2      |   3   |    10
-----+--------------+-------+------------
  1  |       2      |   3   |    11
-----+--------------+-------+------------
  1  |       2      |   3   |    12

[ ... ]
-----+--------------+-------+------------
  1  |       2      |   3   |    109

But I'm not going to insert it into the table.

How can I get a XML with these data if I can't do it with a select to that table?

Mikael Eriksson · Accepted Answer · 2014-04-04 13:02:05Z

1

You can use a numbers table or some other table with enough rows in it.

declare @gint int = 1;
declare @pres int = 2;
declare @level int = 3;
declare @quantity int = 100;
declare @current_incremental int = 10;

with Numbers as
(
  select row_number() over(order by 1/0) as N
  from sys.all_objects as o1 cross join
       sys.all_objects as o2
)
select @gint as GINT,
       @pres as PRESENTATION,
       @level as LEVEL,
       N as INCREMENTAL
from Numbers
where N >= @current_incremental and
      N < @current_incremental + @quantity
for xml path('row'), root('root'), type

Result:

<root>
  <row>
    <GINT>1</GINT>
    <PRESENTATION>2</PRESENTATION>
    <LEVEL>3</LEVEL>
    <INCREMENTAL>10</INCREMENTAL>
  </row>
  <row>
    <GINT>1</GINT>
    <PRESENTATION>2</PRESENTATION>
    <LEVEL>3</LEVEL>
    <INCREMENTAL>11</INCREMENTAL>
  </row>
  .
  .
  .
  .
  <row>
    <GINT>1</GINT>
    <PRESENTATION>2</PRESENTATION>
    <LEVEL>3</LEVEL>
    <INCREMENTAL>108</INCREMENTAL>
  </row>
  <row>
    <GINT>1</GINT>
    <PRESENTATION>2</PRESENTATION>
    <LEVEL>3</LEVEL>
    <INCREMENTAL>109</INCREMENTAL>
  </row>
</root>

edited Apr 4, 2014 at 13:02

answered Apr 4, 2014 at 12:53

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

VansFannel Over a year ago

Thanks for your answer. I have one question: What is Numbers? Is it the number table?

Mikael Eriksson Over a year ago

@VansFannel Numbers in my answer is a CTE that is instead of a numbers table. The CTE uses a cross join between two fairly large tables to give enough rows. If you don't want to use the CTE you can create a numbers table of your own that only has one column numbered from 1 (or 0) to however many rows you need.

Mikael Eriksson Over a year ago

@VansFannel More info on a numbers table here

VansFannel Over a year ago

If I use declare @current_incremental bigint = 2000000; it doesn't work. @quantity is the number of rows that I need. And the INCREMENTAL will have these values: from 2000000 to 2000099.

Mikael Eriksson Over a year ago

@VansFennel I see you have figures it out. You just have to treat the numbers a bit differently.

Community · Accepted Answer · 2017-05-23 11:57:07Z

0

I have modified Mikael Eriksson's answer to fit my needs:

declare @gint bigint = 1;
declare @pres tinyint = 2;
declare @level bigint = 3;
declare @quantity smallint = 100;
declare @current_incremental bigint = 20000000;

with Numbers as
(
  select row_number() over(order by 1/0) as N
  from sys.all_objects as o1 cross join
       sys.all_objects as o2
)
select @gint as GINT,
       @pres as PRESENTATION,
       @level as LEVEL,
       N + @current_incremental as INCREMENTAL
from Numbers
where N < @quantity
for xml path('row'), root('root'), type

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Apr 4, 2014 at 13:50

VansFannel

46.2k119 gold badges382 silver badges657 bronze badges

Collectives™ on Stack Overflow

Generate XML adding a custom value on a table's column

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related