树视图控件的 TSQL 查询并从 xml 文件中删除空元素 [英] TSQL query for tree view control and remove empty elements from an xml file

问题描述

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

Query for treeview control, sometimes contains empty elements which throws an exception (when there are no attributes in an element) due to data-binding of the control the text values is set to "GrandChildOfFirstRow"

我确实通过 xquery 在我的查询中删除了它们，但是有没有其他方法可以做到这一点，或者有更好的更聪明的方法来删除那些空元素，(我需要左外连接以获得此查询的正确记录)或者是否可以将 xquery 代码组合成更短的代码:

I did get rid of them in my query via xquery but is there an alternative way to doing this or a better smarter way to get rid of those empty elements, (I need the left outer join for proper records for this query) or is it possible to combine the xquery code into shorter code:

查询:

        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(./@*)]')

推荐答案

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

You can use for xml path() and build your nested levels with correlated sub-queries.

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')

这篇关于树视图控件的 TSQL 查询并从 xml 文件中删除空元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！