SQL Server stored procedure - Table to XML object

Question

I need to write a stored procedure that can create the following output from values from a SQL Server table:

<object>
  <ValueList>
    <Value value="1" text="Name" enabled="1" alias="Alias" />
    <Value value="2" text="Name" enabled="1" alias="Alias" />
    <Value value="3" ....
    ...
    ...
  </ValueList>
</object>

I have tried to convert my table into XML using this procedure:

select TXT as [text]
from lit_geography
for XML raw ('value'), root('object')

Then I get the following

<object>
  <value text="Midler tidig havn" />
  <value text="Færgehavnsvej" />
  <value text="Sydhavnsvej" />
  <value text="Ø-Pladsen" />
  <value text="Havnepladsen" />
</object>

TODO:

• I am missing the parent node Valuelist

• I need to append the following:

  value="1", value="2" etc etc counting up for each value  
  enabled="1"
  alias=""
  

Answer 1

For the incremental value, you can use Row_Number()

Example

Select (
         Select [value]   = row_number() over (Order by (select null))
               ,[text]    = txt
               ,[enabled] = 1
               ,[alias]   = 'Alias'
          From  lit_geography
          For XML Raw('Value'),type
        )
 For XML Path('ValueList'),Root('object')

Returns

<object>
  <ValueList>
    <Value value="1" text="Midler tidig havn" enabled="1" alias="Alias" />
    <Value value="2" text="Færgehavnsvej" enabled="1" alias="Alias" />
    <Value value="3" text="Sydhavnsvej" enabled="1" alias="Alias" />
    <Value value="4" text="Ø-Pladsen" enabled="1" alias="Alias" />
    <Value value="5" text="Havnepladsen" enabled="1" alias="Alias" />
  </ValueList>
</object>

EDIT - Requested UPDATE

 Update lit_formfield set [Values] = (
 Select (
         Select [value]   = row_number() over (Order by (select null))
               ,[text]    = txt
               ,[enabled] = 1
               ,[alias]   = 'Alias'
          From  lit_geography
          For XML Raw('Value'),type
        )
 For XML Path('ValueList'),Root('object')
 )
 Where Label = 'listbox'