DECLARE @x XML;
SET @x = -- I copied the XML fragment from your posting
'<Assessments>
<Assessment AssessmentDate="2010-09-23T00:00:00" CaseNumber="000001" AssessmentType="1">
<Questions>
<QuestionNumber="1" Answer="-1"/>
<QuestionNumber="2" Answer="1"/>
<QuestionNumber="3" Answer="1"/>
<QuestionNumber="4" Answer="0"/>
<QuestionNumber="5" Answer="1"/>
</Questions>
</Assessment>
<Assessment AssessmentDate="2010-09-24T00:00:00" CaseNumber="000001" AssessmentType="3">
<Questions>
<QuestionNumber="2" Answer="1"/>
<QuestionNumber="3" Answer="1"/>
</Questions>
</Assessment>
<Assessment AssessmentDate="2010-09-15T00:00:00" CaseNumber="000002" AssessmentType="4">
<Questions>
<QuestionNumber="1" Answer="1"/>
<QuestionNumber="2" Answer="1"/>
<QuestionNumber="3" Answer="1"/>
</Questions>
</Assessments>';
SELECT @x;

quote:

---

Originally posted by sunitabeck

The XML you posted is not well-formed, so SQL XML won't be able to generate it. You can see what I mean if you assign your desired output to an xml variable and try to do a select like this:

DECLARE @x XML;
SET @x = -- I copied the XML fragment from your posting
'<Assessments>
<Assessment AssessmentDate="2010-09-23T00:00:00" CaseNumber="000001" AssessmentType="1">
<Questions>
<QuestionNumber="1" Answer="-1"/>
<QuestionNumber="2" Answer="1"/>
<QuestionNumber="3" Answer="1"/>
<QuestionNumber="4" Answer="0"/>
<QuestionNumber="5" Answer="1"/>
</Questions>
</Assessment>
<Assessment AssessmentDate="2010-09-24T00:00:00" CaseNumber="000001" AssessmentType="3">
<Questions>
<QuestionNumber="2" Answer="1"/>
<QuestionNumber="3" Answer="1"/>
</Questions>
</Assessment>
<Assessment AssessmentDate="2010-09-15T00:00:00" CaseNumber="000002" AssessmentType="4">
<Questions>
<QuestionNumber="1" Answer="1"/>
<QuestionNumber="2" Answer="1"/>
<QuestionNumber="3" Answer="1"/>
</Questions>
</Assessments>';
SELECT @x;

I tried to guess what you might want, but not very good at that, so I miserably failed. If you can post the well-formed XML that you want to generate I am sure someone on the forum would be able to offer suggestions. Some sample data would make that a lot easier on them as well.

---

SELECT
   a.AssessmentDate AS [Assessment/@AssessmentDate],
   a.casenumber  AS [Assessment/@CaseNumber],
   (
      SELECT
         questionnumber  AS [Question/@Number],
         answer  AS [Question/@Answer]
      FROM
         questions q
      WHERE
         q.assessmentid = a.id -- Is this the correct join?
      FOR XML PATH(''),TYPE
   ) AS [Assessment/Questions]
FROM
   Assessments a
FOR XML PATH(''), ROOT('Assessments');

quote:

---

Originally posted by sunitabeck

The query below parses, but I am not sure about couple of things:

1. What is the column that relates the two tables? See comment in code. If that is not the correct join condition, change appropriately.

2. The code parses, but the nesting hierarchy may not be exactly correct. If it does not do the right things, can you post some sample data?

SELECT
   a.AssessmentDate AS [Assessment/@AssessmentDate],
   a.casenumber  AS [Assessment/@CaseNumber],
   (
      SELECT
         questionnumber  AS [Question/@Number],
         answer  AS [Question/@Answer]
      FROM
         questions q
      WHERE
         q.assessmentid = a.id -- Is this the correct join?
      FOR XML PATH(''),TYPE
   ) AS [Assessment/Questions]
FROM
   Assessments a
FOR XML PATH(''), ROOT('Assessments');

---