SQL Select to XML - DIFFICULT

Hi,

It's a bit hard to test without some sample data, but try this:

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],
                              
                              (SELECT [Doc Display] AS [DocDIsplay]
					FROM FROM      dbo.[vw_Document Requirements by Assignment for ACS] AS T3
					where T3.classification = T2.classification
					and T3.rule = T2.rule
                                FOR XML PATH PATH(''), TYPE)
                              
                              
                  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 allOpen in new window

Modified the SQL below which ran...there were some double FROM and PATH statements
However...
It's still repeating the Classification so the return is
Blah...Blah...Blah...
 <Classification>
      <classificationName>Certification</classificationName>
      <rule>None</rule>
    </Classification>
    <Classification>
      <classificationName>Certification</classificationName>
      <rule>None</rule>
    </Classification>
    <Classification>
      <classificationName>Certification</classificationName>
      <rule>None</rule>
    </Classification>
    <Classification>
      <classificationName>Certification</classificationName>
      <rule>None</rule>
    </Classification>
    <Classification>
      <classificationName>Certification</classificationName>
      <rule>None</rule>
    </Classification>

Etc.....

I need...
<Classification>
      <classificationName>Certification</classificationName>
      <rule>None</rule>
      <DocDIsplay>RN License</DocDIsplay>
      <DocDIsplay>CPR</DocDIsplay>
      <DocDIsplay>ACLS</DocDIsplay>
    </Classification>





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],
                             
                              (SELECT [Doc Display] AS [DocDIsplay]
                              FROM      dbo.[vw_Document Requirements by Assignment for ACS]
                               AS T3
                              where t3.[Assignment Number] = @AssignmentNumber AND T3.classification = T2.classification
                              and T3.[rule] = T2.[rule]
                                FOR XML PATH (''), TYPE)
                             
                             
                  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')

As I don't have your table structure it's hard to test, but I think the following query will work:

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],
                              (SELECT [Doc Display] FROM myView AS T3 
							WHERE CAST(T1.[Assignment Number] AS VARCHAR(100)) = CAST(T2.[Assignment Number] AS VARCHAR(100)) AND 
							T2.classification = T3.classification AND 
							ISNULL(T2.[rule],'None') = ISNULL(T3.[rule],'None') 
							FOR XML PATH(''), TYPE)
                  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))
                  GROUP BY
						CAST([Assignment Number] AS VARCHAR(100)),
						[Status],
						[Employee First Name],
						[Employee Last Name],
						leads.email,[Client],
						[Start Date],
						[Client State],
						[Classification],
						[rule]
                  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 allOpen in new window

pourfard
Got this message
Msg 164, Level 15, State 1, Line 4
Each GROUP BY expression must contain at least one column that is not an outer reference.

pourfard
Modified for correct syntax and removed some of the group by at SQL insistance

But runs forever... 2+ minutes...with no results


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],
                              (SELECT [Doc Display] DocDispaly FROM dbo.[vw_Document Requirements by Assignment for ACS] AS T3
                                          WHERE CAST(T1.[Assignment Number] AS VARCHAR(100)) = @AssignmentNumber
                                           AND
                                          T2.classification = T3.classification AND
                                          ISNULL(T2.[rule],'None') = ISNULL(T3.[rule],'None')
                                          FOR XML PATH(''), TYPE)
                  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))
                  GROUP BY
                                    CAST([Assignment Number] AS VARCHAR(100)),
                                    [Classification],
                                    [rule]
                  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')

the query seems ok. I have no idea why it doesn't work.
to make it simple I wrote smaller query that it's output is similar to what you are looking for. may be it helps:

Select
	(SELECT fullname, ADDRESS, 
		(SELECT classification, 
		(SELECT ruleSatisfied FROM myView AS T3 WHERE T1.fullname = T2.fullname AND T1.ADDRESS = T2.ADDRESS AND T2.classification = T3.classification  FOR XML PATH(''), type)
		 FROM myView AS T2 WHERE T1.fullname = T2.fullname AND T1.ADDRESS = T2.ADDRESS group by fullname, ADDRESS, classification FOR XML PATH('classification'), type)
	FROM myView AS T1 group by fullname, ADDRESS FOR XML PATH('header'), TYPE)
FOR XML PATH('root')

Select allOpen in new window

Result:
<root>
  <header>
    <fullname>Bill Baily</fullname>
    <ADDRESS>121 Main Street Anytown USA 11111</ADDRESS>
    <classification>
      <classification>Certification</classification>
      <ruleSatisfied>1</ruleSatisfied>
    </classification>
    <classification>
      <classification>Client Doc</classification>
      <ruleSatisfied>01</ruleSatisfied>
      <ruleSatisfied>01</ruleSatisfied>
      <ruleSatisfied>01</ruleSatisfied>
      <ruleSatisfied>11</ruleSatisfied>
    </classification>
  </header>
</root>

pourfard,
This put me on the right track
here is the final Select


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/@name],
                              (SELECT ISNULL([Doc Display],'') [rule/DocDisplay],
                                                ISNULL([Rule Satisfactory],0) AS [rule/RuleSatisfactory],
                                                ISNULL([Requirement Satisfactory],0) AS [rule/ReqSatisfactory]
                                                FROM dbo.[vw_Document Requirements by Assignment for ACS] AS T3
                                                WHERE CAST(T3.[Assignment Number] AS VARCHAR(100)) = '11327'
                                                AND T3.Classification = T2.Classification
                                                AND ISNULL(T3.[rule],'None') = ISNULL(T2.[rule],'None') FOR XML PATH(''), TYPE)
                  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))
                  GROUP BY
                                    CAST([Assignment Number] AS VARCHAR(100)),
                                    [Classification],
                                    [rule]
                  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')

To pourfard,

Thanks for sticking with me along the way.