How to do OrderBY on XML column in SQL SERVER 2008
- by Rohit
I am creating a comma seperated values of columns specified in dbName attribute of below xml. Now i want to concatenate those columns on the basic of 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 is 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'
Result should be
select Pname,Priority,JobID,name,sname,status FROM JobListingDetails.
Please help.
