Recently I had task to generate XML which is supposed to look something like this:
<NamesTree>
<TypeOfName Name="Test" Kind="TestNames">
<Value Name="FirstName" Type="nvarchar(50)" Value="John" />
<Value Name="LastName" Type="nvarchar(50)" Value="Doe" />
<Value Name="Type" Type="int" Value="1" />
</TypeOfName>
<TypeOfName Name="Test" Kind="TestNames">
<Value Name="FirstName" Type="nvarchar(50)" Value="Jane" />
<Value Name="LastName" Type="nvarchar(50)" Value="Doe" />
<Value Name="Type" Type="int" Value="2" />
</TypeOfName>
</NamesTree>
This is how my table look like:
CREATE TABLE [dbo].[Test](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Type] [int] NULL
) ON [PRIMARY]
First I am gonna seed my table:
insert into test values ('John', 'Doe', 1)
insert into test values ('Jane', 'Doe', 2)
This is how SQL looks like:
select
'Test' '@Name'
, 'TestNames' '@Kind'
, 'FirstName' [Value/@Name]
, 'nvarchar(50)' 'Value/@Type'
, FirstName 'Value/@Value'
, null
, 'LastName' [Value/@Name]
, 'nvarchar(50)' 'Value/@Type'
, LastName 'Value/@Value'
, null
, 'Type' [Value/@Name]
, 'int' 'Value/@Type'
, Type 'Value/@Value'
, null
from test
for XML path('TypeOfName'), root('NamesTree')
