We help IT Professionals succeed at work.
Get Started

SQL Select to XML - DIFFICULT

643 Views
Last Modified: 2012-07-09
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>
Comment
Watch Question
Senior Software Engineer
CERTIFIED EXPERT
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE