How to do OrderBY on XML column in SQL SERVER 2008

Question

I am creating a comma separated value of columns specified in the dbName attribute of the XML below. Now I want to concatenate those columns on the basis of the Position attribute.

DECLARE @varXML AS XML  = 
         '<gridFormat>                   
              <column property="FacilityInternalID" dbName="Pname" HeaderText="TAT Health" IsVisible="1" Position="1" />
              <column property="FacilityInternalID" dbName="Priority" HeaderText="Priority" IsVisible="1" Position="2" />
              <column property="FacilityInternalID" dbName="JobID" HeaderText="Job Number" IsVisible="1" Position="3" />
              <column property="FacilityInternalID" dbName="Status" HeaderText="Status" IsVisible="1" Position="6" />
              <column property="FacilityInternalID" dbName="name" HeaderText="Customer" IsVisible="1" Position="4" />
              <column property="FacilityInternalID" dbName="sname" HeaderText="Facility " IsVisible="1" Position="5" />                  
         </gridFormat>'
        PRINT @varXML

This is the query by which I am generating CSV of columns. I have to use it as a select list.

        SELECT  @ColumnsToDisplay = LEFT(MyCsvList, LEN(MyCsvList) - 1)
        FROM    ( SELECT    ( SELECT    row.value('@property',
                                                  'varchar(200)') + ', ' AS [text()]
                              FROM      @varXML.nodes('gridFormat/column')
                                        AS d ( row )
                            FOR
                              XML PATH('')
                            ) AS MyCsvList
                ) AS MyCsvListFinal
        SET @SQL = 'SELECT ' + @ColumnsToDisplay
            + ' FROM JobListingDetails'

The result should be

select Pname,Priority,JobID,name,sname,status FROM JobListingDetails.

Please help.

Answer 1

See this article for a cool way to do string concatenation without using a cursor.

It's under the "Replacing the cursor with a SET based approach" section.

Answer 2

This should work:

Note that you need to read the dbName attribute.

[EDIT] Updated to include order by. Basicaly you also read the Postion attribute from xml, then use that in the ORDER BY:

declare @ColumnsToDisplay varchar(max)

DECLARE @varXML AS XML
set @varxml  = 
         '<gridFormat>                   
              <column property="FacilityInternalID" dbName="Pname" HeaderText="TAT Health" IsVisible="1" Position="1" />
              <column property="FacilityInternalID" dbName="Priority" HeaderText="Priority" IsVisible="1" Position="2" />
              <column property="FacilityInternalID" dbName="JobID" HeaderText="Job Number" IsVisible="1" Position="3" />
              <column property="FacilityInternalID" dbName="Status" HeaderText="Status" IsVisible="1" Position="6" />
              <column property="FacilityInternalID" dbName="name" HeaderText="Customer" IsVisible="1" Position="4" />
              <column property="FacilityInternalID" dbName="sname" HeaderText="Facility " IsVisible="1" Position="5" />                  
         </gridFormat>'

SELECT  @ColumnsToDisplay = COALESCE(@ColumnsToDisplay + ',', '') + dbName
FROM
(
    SELECT    row.value('@dbName','varchar(200)')  AS dbName, row.value('@Position','int') as pos
    FROM      @varXML.nodes('gridFormat/column')
    AS d ( row )
) csv
order by pos
select 'SELECT ' + @ColumnsToDisplay + ' FROM JobListingDetails'