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