Jump to content
  • Advertisement
Sign in to follow this  
Alpha_ProgDes

[web] FOR XML not working with Sql Server 2008

This topic is 3336 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I used the FOR XML query to concatenate different values under the same column. Now when I used the query (shown below), it's works fine. But when I use the query with an inner join the result does not show. Is there something extra I need to do? I'm using SQL Server 2008 Std.
select IWQ.[QN] as col2, (select distinct [PLID] as col1 FROM Reports_IWQ
WHERE Reports_IWQ.[QN] = IWQ.[QN] for xml raw, elements, type).query('
                                              for $col1 in (row/col1)
                                              return concat($col1, " ")') as PLID
from Reports_IWQ as IWQ 
INNER JOIN Reports_I AS I ON IWQ.[IID] = I.[IID]
INNER JOIN Reports_Q AS Q ON Q.[QID] = I.[Q#] 
WHERE      (Q.[QS] = 'In process') AND (I.[Action] = 'true') AND (I.[P/A] = 1)
group by IWQ.[QN]

Here's where it breaks:
SELECT     Q.[QN], Q.Owner, Q.[Entered Date], Q.[BD], Q.[QT1], 
                      Q.[CN], G.[PLID], G.TGP, Q.[AOC], Q.Comm, 
                      Q.[SE], Q.[DSE]
FROM	   Reports_Q as Q INNER JOIN
					  (select IWQ.[QN], 
					          SUM(IWQ.[EP]) AS TGP, 
					          (select distinct [PLID] 
							   from Reports_IWQ
							   where Reports_IWQ.[QN] = IWQ.[QN] 
							   for xml raw, elements, type).query('for $col1 in (row/col1)
																   return concat($col1, " ")') as PLID
						from Reports_IWQ as IWQ 
						INNER JOIN Reports_I AS I ON IWQ.[IID] = I.[IID]
						INNER JOIN Reports_Q AS Q ON Q.[QID] = I.[Q#] 
						where (Q.[QS] = 'In process') AND (I.[Action= 'true') AND (I.[P/A] = 1)
						group by IWQ.[QN]) AS G ON Q.[QN] = G.[QN]

Share this post


Link to post
Share on other sites
Advertisement
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!