Writing relational SQL data as XML

I have attempted the SQL Server part of the solution. You can put the query in a stored procedure and then invoke the stored procedure using C# code. This will allow you to get the XML directly in your program. This is SQL Server 2008 R2.

CREATE TABLE parent 
  ( 
     parent_id   INT IDENTITY PRIMARY KEY, 
     parent_name NVARCHAR(100) 
  ); 

CREATE TABLE child 
  ( 
     child_id   INT IDENTITY PRIMARY KEY, 
     parent_id  INT REFERENCES parent(parent_id), 
     child_name NVARCHAR(100) 
  ); 

INSERT INTO parent 
            (parent_name) 
VALUES     ('JOHN'); 

INSERT INTO parent 
            (parent_name) 
VALUES     ('TOM'); 

INSERT INTO parent 
            (parent_name) 
VALUES     ('STACY'); 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (1, 
            'Emily') 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (1, 
            'Ryan') 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (2, 
            'Krusna') 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (2, 
            'Uma') 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (2, 
            'Kali') 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (3, 
            'Jimbo') 

INSERT INTO child 
            (parent_id, 
             child_name) 
VALUES     (3, 
            'Howard') 

SELECT parent.parent_id   AS "@parentId", 
       parent.parent_name AS "@parentname", 
       (SELECT child_id   AS "@childId", 
               child_name AS "@childName" 
        FROM   child 
        WHERE  child.parent_id = parent.parent_id 
        FOR xml path ('Child'), type) 
FROM   parent 
       INNER JOIN child 
               ON ( parent.parent_id = child.parent_id ) 
FOR xml path ('Parent'), root ('Container'), type