sql server - How to flatten tree leaves sequence in SQL -

I have a table S that has 2 columns: name For ID and one XML column
This table shows a tree data.

  S ----- + ----- ----------- name. XmlCol ----- + ---------------- A | & Lt; E & gt; & Lt; BID = 'B1' type = 0 / & gt; BTID = 'D' type = 1 / & gt; BTD = 'B2' type = 0 / & gt; & Lt; / E & gt; D | & Lt; E & gt; & Lt; BID = 'B3' type = 0 / & gt; & Lt; BID = 'G' type = 1 / & gt; & Lt; / E & gt; F | & Lt; E & gt; & Lt; BID = 'B4' type = 0 / & gt; & Lt; / E & gt; G. & Lt; E & gt; & Lt; BID = 'B5' type = 0 / & gt; & Lt; / E & gt;   
 In the given order the data appears  Order cases here.  
 
  
 Note XML structure.  
 Type = 0 means that the entry type is the address. 
 Type = 1 means that there is a line with the same  name  in the table, so there is no node and no leaf.  
 5 cards, B1, B2, B3, B4, B5.  
  The sequence I want is to obtain a table of all the leaves in sequence , like this:  
  Leaves -------- B1B3B5B2B4   
 When the start node  'A'   
 This is the XML parsing snippet, but this is just the beginning. [S] Cross applied from [XmlCol  
  SELECT [id] = xTree.b.value ('@ id,' varchar (10) ') ]. Nodes ('/ E / B') xTree (B)   
 Can anyone suggest how to do this in SQL?   
 
 
 Try something like this:  
  Announcement @ source table (Name VARCHAR (10) PRIMAR Y KE, XMLCL XML NOT NULL) DECLARE @Root VARCHAR (10) = 'A' Source value ('A', Convert (XML, '&  gt; & lt; / E & gt; ')), (' D 'N' & lt; E & gt; B = B = "B3" type = "0" /> ; B id = "g" type = "1" /> gt; & lt; / E & gt; ',), (' F ',' h E ',' N ' DECLARE @ Temp1 Table (ID Identification Priority key key, name VARCHAR (10) No, INT not type zero, type zero, Value VARCHAR (10) No Taps) INSERT @ Temp1 SELECT Name, xTree.b.value ('@ type', 'int') Type AS, xTree.b.value ('@ Id', 'varchar (10)' ) @ SourceCrossing the value [XmlCol] Nodes ('/ E / B') ASTE (B) DECLARE @ TEMP2 table (ID int main key, name VARCHAR (10) No, tap, INT not zero, value Enter the VARARAR (10) No, tap space zero) in @ Temp2 SELECT *, ROW_NUMBER (), (As per the name division by name) AS status @ Temp1 DECLARE @ Temposition table (Name VARCHAR (10) No T-null, Type INT is not zero, value VARCHAR (10) No zero, position float zero, level INT no zero, primary key (name, position)) as CTE (select name, type, value, conversion (float, position) As a condition, from the 0AS level @ Temp2 WHERE type = 0 union all Select t1.Name, t2.Type, t2.Value, t1.Position + t2.Position * Power (converter (float, 0.1), 1 + t2. Level), T2. Level @ 1s level @ Temp 2 T1 Inner GTT2T2OnT 2.Name = t1. Type Volume T1 = 1) Select @tpx * Include CTE selection value (selection value, 0 As an additional, tapfone from where the name = @ Root Unius all select value, 1AS additional, @ tempus from the position where the value is not (where the float @ float from the selection price) name, / Code>  
 Some overview:  
 
 
 In XML, feature Value should always be quoted   
 
 It is not very clear in which order you want the value outside the given root tree (in this example, only B 4 is not a part of the beginning of the tree; A; If there were many such values, in many other trees, this is not a desired order, which is not clear)  
 
 Using a table variable is one more Using complex CTE can be avoided, but I think they   
 
 I took up to 10 sub-nodes per level; If there are more sub-nodes, then you can change between 0.1 to 0.01, for example