Microsoft SQL Server 2008 R2 Unleashed- P190:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1884 CHAPTER 47 Using XML in SQL Server 2008 ScrapReason 12 --Comment Name Thermoform temperature too high-- ModDatePI 1998-06-01T00 00 00 WorkOrders WorkOrderIds 72370 72273 70875 69474 69173 68573 65970 60472 56975 56875 55275 53771 50370 47670 45773 42071 41975 39372 36673 36671 32872 32775 32770 31073 29370 27771 24174 22673 22670 17674 16073 13073 10274 9071 7771 4972 2573 ScrapReason ScrappedWorkOrders Let s review the selected columns in Listing the first is aliased with the asterisk character. This character tells SQL Server to inline-generate the data for that column as text . Using the text node test would do the same in this case. Next the comment node test is specified for Name telling the XML generator to output its value in a comment. For clarity s sake we added a little syntactic sugar in this statement by prepending the text Comment Name 1 to the value produced inside the comment. Next the processing-instruction node test is specified to output each value of ModifiedDate to a new processing instruction called ModDatePI. Finally the fourth column is produced as a list of WorkOrderld values using the magical data keyword in a nested FOR XML PATH statement. data tells SQL Server to generate a space-delimited list of atomic column values one value for each row in the result set. Note that the nested query is merely used to generate a list of WorkOrderld values. The empty string is given for the PATH keyword telling the XML engine not to generate a default element at all so no XML is generated whatsoever You can extract and test the statement to see this in action. The nested query applies the same WHERE clause as its parent to filter WorkOrderld values where the value of ScrapReasonld is 12. This ensures the relevancy of the nested data to the outer query. The resulting list of values is grafted to the XML of the outer statement using the column alias WorkOrders @WorkOrderIds . FOR XML and the xml Data Type By default the results of any FOR XML .
