mca blog

i work with XML - *alot.* and i need a relational database that makes XML work as painless as possible. and that's why i really like the way MS SQL Server supports streaming XML. here is a simple (but elegant) example:

given these two tables:

CREATE TABLE [dbo].[CBGames](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [userid] [nvarchar](50) NOT NULL,
  [datecreated] [datetime] NOT NULL CONSTRAINT [DF_CBGames_datecreated]  DEFAULT (getdate()),
  [maxattempts] [int] NOT NULL CONSTRAINT [DF_CBGames_maxattempts]  DEFAULT ((10)),
  [status] [nvarchar](50) NOT NULL CONSTRAINT [DF_CBGames_status]  DEFAULT (N'In-Progress'),
  [place1] [nvarchar](50) NOT NULL,
  [place2] [nvarchar](50) NOT NULL,
  [place3] [nvarchar](50) NOT NULL,
  [place4] [nvarchar](50) NOT NULL
)
  
CREATE TABLE [dbo].[CBAttempts](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [gameid] [int] NOT NULL,
  [place1] [nvarchar](50) NOT NULL,
  [place2] [nvarchar](50) NOT NULL,
  [place3] [nvarchar](50) NOT NULL,
  [place4] [nvarchar](50) NOT NULL
)

i can get this XML output:

<games>
  <game id="1">
    <date-created>2008-03-08T17:09:12.563</date-created>
    <max-attempts>10</max-attempts>
    <status>In-Progress</status>
    <code>
      <place1>A</place1>
      <place2>B</place2>
      <place3>C</place3>
      <place4>D</place4>
    </code>
    <attempts>
      <attempt>
        <place1>D</place1>
        <place2>E</place2>
        <place3>F</place3>
        <place4>A</place4>
      </attempt>
      <attempt>
        <place1>C</place1>
        <place2>D</place2>
        <place3>E</place3>
        <place4>F</place4>
      </attempt>
      <attempt>
        <place1>A</place1>
        <place2>B</place2>
        <place3>C</place3>
        <place4>D</place4>
      </attempt>
    </attempts>
  </game>
</games>

with this SELECT query:

CREATE PROCEDURE [dbo].[cbgames_list]
  @userid nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON;

  select 
    cbg.id as '@id',
    cbg.datecreated as 'date-created',
    cbg.maxattempts as 'max-attempts',
    cbg.status as 'status',
    cbg.place1 as 'code/place1',
    cbg.place2 as 'code/place2',
    cbg.place3 as 'code/place3',
    cbg.place4 as 'code/place4',
    (select 
      cba.place1,
      cba.place2,
      cba.place3,
      cba.place4 
    from  cbattempts cba 
    where cbg.id=cba.gameid 
    order by cba.id
    for xml path('attempt'),  type) as 'attempts'
  from cbgames cbg
  where userid=@userid
  for xml path('game'), root('games')
END