The following Select will produce the following XML
That LAST part of this is what I need to be rendered
SELECT STATEMENT
DECLARE @AssignmentNumber VARCHAR(100)
SET @AssignmentNumber = '11327'
Select
(SELECT CAST([Assignment Number] AS VARCHAR(100)) [AssignmentNumber],
[Status],
[Employee First Name] AS FirstName,
[Employee Last Name] AS LastName,
leads.email,
[Client],
[Start Date] StartDate,
[Client State] ST,
(SELECT classification AS [classificationName],
ISNULL([rule],'None') AS [rule],
[Doc Display] AS [DocDIsplay]
FROM dbo.[vw_Document Requirements by Assignment for ACS] AS T2
WHERE CAST(T1.[Assignment Number] AS VARCHAR(100)) = CAST(T2.[Assignment Number] AS VARCHAR(100))
ORDER BY classification FOR XML PATH('Classification'), TYPE)
FROM dbo.[vw_Document Requirements by Assignment for ACS] AS T1
LEFT JOIN leads ON T1.[Lead ID] = dbo.leads.lead_id
WHERE CAST(T1 .[Assignment Number] AS VARCHAR(100)) = @AssignmentNumber
group by
CAST([Assignment Number] AS VARCHAR(100)),
[Status],
[Employee First Name],
[Employee Last Name],
leads.email,[Client],
[Start Date],
[Client State]
FOR XML PATH('header'), TYPE)
FOR XML PATH('Document')
SELECT RESULTS
<Document>
<header>
<AssignmentNumber>11327</AssignmentNumber>
<Status>Working</Status>
<FirstName>Myra</FirstName>
<LastName>LastName</LastName>
<email>theemail11@aol.com</email>
<Client>St. Clare''s Hospital - Denville</Client>
<StartDate>2012-04-30T00:00:00</StartDate>
<ST>NJ</ST>
<Classification>
<classificationName>Certification</classificationName>
<rule>None</rule>
<DocDIsplay>RN License</DocDIsplay>
</Classification>
<Classification>
<classificationName>Certification</classificationName>
<rule>None</rule>
<DocDIsplay>CPR</DocDIsplay>
</Classification>
<Classification>
<classificationName>Certification</classificationName>
<rule>None</rule>
<DocDIsplay>ACLS</DocDIsplay>
</Classification>
</Document>
DESIRED RESULTS
<Document>
<header>
<AssignmentNumber>11327</AssignmentNumber>
<Status>Working</Status>
<FirstName>Myra</FirstName>
<LastName>LastName</LastName>
<email>theEmail1@aol.com</email>
<Client>St. Clare''s Hospital - Denville</Client>
<StartDate>2012-04-30T00:00:00</StartDate>
<ST>NJ</ST>
<Classification>
<classificationName>Certification</classificationName>
<rule>None</rule>
<DocDIsplay>RN License</DocDIsplay>
<DocDIsplay>CPR</DocDIsplay>
<DocDIsplay>ACLS</DocDIsplay>
</Classification>
</Document>
It's a bit hard to test without some sample data, but try this:
Open in new window