Write a SQL XML Field association query once

Demand:

A table is an app table with the following structure:

　　

App_category The Category Id,xml field type to which the game belongs

Another table is the category table, which corresponds to the name of the ID, which is not.

There is also a table for each game download record, the structure is as follows:

　　

Downlogs_appid for the corresponding game

Then the demand comes, to query the game's download record, the query field to have the game's category name, multiple separated by commas

The query results should be as follows:

The final execution statement is as follows:

SelectAPP_ID,app_name, Left(App_category,Len(app_category)-1) asApp_category,downlogs_imei,downlogs_addtime from (SelectAPP_ID,app_name,(        SelectCategory_title+','  fromTbl_categorywherecategory_idinch(SelectT.c.value ('.','nvarchar (5)') asSs fromTbl_app asApp CrossApply App. App_category.nodes ('/id') asT (C)whereapp_id=TBL_DOWNLOGS.APP_ID) forXML Path ("')) asapp_category, Downlogs_imei,downlogs_addtime from (    SelectAPP_ID,app_name, App_category,downlogs_imei,downlogs_addtime fromTbl_app Right JoinTbl_download_logs onTbl_app. app_id=tbl_download_logs.downlogs_appid) astbl_downlogs) asT

The following step-by-step decomposition

The

Write a SQL XML Field association query once