Avatar of Larry Brister
Larry Brister
Flag for United States of America asked on

SQL Select to XML - DIFFICULT

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>
Microsoft SQL Server 2005XMLMicrosoft SQL Server

Avatar of undefined
Last Comment
Larry Brister

8/22/2022 - Mon
Adam

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')

Open in new window

Larry Brister

ASKER
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')
Habib Pourfard

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')

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Larry Brister

ASKER
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.
ASKER CERTIFIED SOLUTION
Habib Pourfard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Larry Brister

ASKER
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')
Habib Pourfard

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')

Open 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>
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Larry Brister

ASKER
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')
Larry Brister

ASKER
To pourfard,

Thanks for sticking with me along the way.