TSQL查询树视图控件并从xml文件中删除空元素

Question

查询树视图控件，有时包含空元素，由于控件的数据绑定而引发异常（当元素中没有属性时）。文本值设置为"GrandChildOfFirstRow"

我确实通过xquery在我的查询中消除了它们，但有没有其他方法可以做到这一点，或者有更好的更聪明的方法来消除那些空元素（我需要左边的外部联接来为这个查询提供正确的记录），或者有没有可能将xquery代码组合成更短的代码：

查询：

        declare @x as xml
     set @x =
    (
    SELECT distinct  
    Table1.AssetObjID, Table1.Asset_ID , Table1.FromLR, Table1.AssetType + ', ' + Table1.StreetName + ', ' +  Table1.FromMunicNo   as FirstRow,
    Table2.ACIObjID ,Table2.PAssetObjID, Table2.Feature_ID + ', ' + Table2.FeatureName   AS ChildOfFirstRow,
    Table3.ITMObjID  ,Table3.Item_ID + ',' + Table3.[DESC] as GrandChildOfFirstRow
    FROM  Table1 left outer join 
    Table2 ON Table1.AssetObjID = Table2.PAssetObjID left outer join 
    Table3 ON Table1.AssetObjID = Table3.AssetObjID AND Table2.ACIObjID = Table3.ACIObjID
    where Table1.AssetType ='xxxx' 
    for xml auto,root('xml')
    )    
--what it does is it only grabs one empty element and deletes only occurrences of that           
--specific element for the whole file 
--so If I have 2 or more elements which are empty in an xml file 
--I will have to repeat that code each time
    SET @x.modify('delete //*[not(node()) and not(./@*)]')
    SET @x.modify('delete //*[not(node()) and not(./@*)]')

Answer 1

您可以使用for xml path()并使用相关的子查询构建嵌套级别。

select Table1.AssetObjID as "@AssetObjID",
       Table1.Asset_ID as "@Asset_ID",
       Table1.FromLR as "@FromLR",
       Table1.AssetType + ', ' + Table1.StreetName + ', ' +  Table1.FromMunicNo   as "@FirstRow",
       (
       select Table2.ACIObjID as "@ACIObjID",
              Table2.PAssetObjID as "@PAssetObjID",
              Table2.Feature_ID + ', ' + Table2.FeatureName   AS "@ChildOfFirstRow",
              (
              select Table3.ITMObjID as "@ITMObjID",
                     Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"
              from Table3
              where Table1.AssetObjID = Table3.AssetObjID and 
                    Table2.ACIObjID = Table3.ACIObjID
              for xml path('Table3'), type
              )
       from Table2
       where Table1.AssetObjID = Table2.PAssetObjID
       for xml path('Table2'), type
       )
from Table1
where Table1.AssetType = 'xxxx'
for xml path('Table1'), root('xml')