SQL Server Performance Forum – Threads Archive
Dynamic FOR XML EXPLICITI have a large table with hundreds of rows of data. There is an id and parent column. The rows need to be be formatted in an hierarchical XML structure and I thought the best method would be to use a dynamic FOR XML EXPLICIT statement to output the hierarchy XML. Would this be the best method? Does anyone have experience in creating such a statement to output hierarchy XML from SQL Server 2005? James
Are you looking for help in putting together a "FOR XML EXPLICIT" command in general, or are you looking for help in putting together a dynamic query? Is the real issue that there are several layers of parents, or is it just a single parent-child hierarchy?
I ended up using a recursive function in a stored procedure that finds all child and parent levels and also adds the tags. I based my stored procedure off an example at this page: http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm