28152

TSQL query for tree view control and remove empty elements from an xml file

Question:

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:

Query:

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

Recommend

  • SQL select: two-dimensional select with variable column count
  • How to get rid of the suffix on object names added by EF?
  • Get Distinct rows from a result of JOIN in SQL Server
  • How do I conditionally select a field from one of two tables?
  • R matching more than 2 conditions and return the response value
  • pyspark substring and aggregation
  • How to get the index of element in the List in c#
  • command line of process by name
  • Counting problem C#
  • SQL: Getting the physical size of a subset of a table
  • Primefaces lazy datascroller calling load twice
  • How do I remove all but some records based on a threshold?
  • How do I get the list of bad records that didn't load in Bigquery?
  • KnockoutObservableArray with typed elements in TypeScript
  • Basic many-to-many left join query
  • R Split data.frame using a column that represents and on/off switch
  • Floated image with variable width and heading with background image
  • Sequential (transactional) API calls in angular 4 with state management
  • Remove final comma from string in vb.net
  • how to display data from 1st point on words on y axis for line chart in d3.js
  • Blackberry - Custom EditField Cursor
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Combining SpatialPolygonsDataFrame of two neighbour countries
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Django: Count of Group Elements
  • Javascript Callbacks with Object constructor
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • How to get next/previous record number?
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Proper folder structure for lots of source files
  • python regex in pyparsing
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Linking SubReports Without LinkChild/LinkMaster
  • How can I remove ASP.NET Designer.cs files?
  • Is there any way to bind data to data.frame by some index?
  • How can I use `wmic` in a Windows PE script?
  • java string with new operator and a literal