Select all the way from Xml that has content similar to graphics

Question

I have an XML column with this element:

<Root> <Word Type="pre1" Value="A" /> <Word Type="pre1" Value="D" /> <Word Type="base" Value="B" /> <Word Type="post1" Value="C" /> <Word Type="post1" Value="E" /> <Word Type="post1" Value="F" /> </Root> 

sort of:

and you want to select all possible paths using XQuery in MSSQL to get something like this result:

ABC ABE ABF DBC DBE DBF

Or something like:

 <Root> <Word Type="pre1" Value="A" /> <Word Type="pre1" Value="D" /> <Word Type="pre2" Value="G" /> <Word Type="pre2" Value="H" /> <Word Type="base" Value="B" /> <Word Type="post1" Value="C" /> <Word Type="post1" Value="E" /> <Word Type="post1" Value="F" /> </Root> 

with this result:

AHBC AHBE AHBF DHBC DHBE DHBF AGBC AGBE AGBF DGBC DGBE DGBF

Answer 1

You can use CTE to create a unique list of types, and then use it in a recursive CTE to build strings. Finally, you select the rows created in the last iteration.

 with Types as ( select row_number() over(order by TN) as ID, TNvalue('.', 'varchar(10)') as Type from (select @XML.query('for $t in distinct-values(/Root/Word/@Type) return <T>{$t}</T>') ) as X(T) cross apply XTnodes('/T') as T(N) ), Recu as ( select T.Type, T.ID, XNvalue('@Value', 'varchar(max)') as Value from Types as T cross apply @XML.nodes('/Root/Word[@Type=sql:column("T.Type")]') as X(N) where T.ID = 1 union all select T.Type, T.ID, R.Value+XNvalue('@Value', 'varchar(max)') as Value from Types as T inner join Recu as R on T.ID = R.ID + 1 cross apply @XML.nodes('/Root/Word[@Type=sql:column("T.Type")]') as X(N) ) select R.Value from Recu as R where R.ID = (select max(T.ID) from Types as T) order by R.Value 

SQL Fiddle

Update

Here is the version that has the best performance. It splits the XML into two temporary tables. One for each type and one for all words. Recursive CTE is still needed, but tables are used instead of XML. There is also one index for each of the temporary tables that are used by joins in the CTE.

 -- Table to hold all values create table #Values ( Type varchar(10), Value varchar(10) ); -- Clustered index on Type is used in the CTE create clustered index IX_#Values_Type on #Values(Type) insert into #Values(Type, Value) select TNvalue('@Type', 'varchar(10)'), TNvalue('@Value', 'varchar(10)') from @XML.nodes('/Root/Word') as T(N); -- Table that holds one row for each Type create table #Types ( ID int identity, Type varchar(10), primary key (ID) ); -- Add types by document order -- Table-Valued Function Showplan Operator for nodes guarantees document order insert into #Types(Type) select T.Type from ( select row_number() over(order by TN) as rn, TNvalue('@Type', 'varchar(10)') as Type from @XML.nodes('/Root/Word') as T(N) ) as T group by T.Type order by min(T.rn); -- Last level of types declare @MaxID int; set @MaxID = (select max(ID) from #Types); -- Recursive CTE that builds the strings with C as ( select T.ID, T.Type, cast(V.Value as varchar(max)) as Value from #Types as T inner join #Values as V on T.Type = V.Type where T.ID = 1 union all select T.ID, T.Type, C.Value + V.Value from #Types as T inner join C on T.ID = C.ID + 1 inner join #Values as V on T.Type = V.Type ) select C.Value from C where C.ID = @MaxID order by C.Value; -- Cleanup drop table #Types; drop table #Values; 

SQL Fiddle

Answer 2

You need a cross product of these three sets of elements, so basically write a join without conditions:

 for $pre in //Word[@Type="pre1"] for $base in //Word[@Type="base"] for $post in //Word[@Type="post1"] return concat($pre/@Value, $base/@Value, $post/@Value) 

In the advanced version, I used two helper functions that extract all attributes and then merge the results recursively.

MSSQL does not seem to allow XQuery functions to be customized. This code is valid for compatible XQuery 1.0 (and newer) processors.

 declare function local:call($prefix as xs:string) as xs:string* { local:recursion('', for $value in distinct-values(//Word/@Type[starts-with(., $prefix)]) order by $value return $value ) }; declare function local:recursion($strings as xs:string*, $attributes as xs:string*) as xs:string* { if (empty($attributes)) then $strings else for $string in $strings for $append in //Word[@Type=$attributes[1]] return local:recursion(concat($string, $append/@Value), $attributes[position() != 1]) }; for $pre in local:call('pre') for $base in local:call('base') for $post in local:call('post') return concat($pre, $base, $post) 

Answer 3

If I understand your XML correctly, all your graphs are sequences of steps in which no step can be omitted, and each step can have several alternatives. (Thus, the many paths through the graph are essentially the Cartesian product of the various multitude of alternatives.) If this is not the case, then the following will not be what you want.

The easiest way to get the Cartesian product here is to use the XQuery FLWOR expression with one for clause for each factor in the Cartesian product, as shown in the original answer by Jens Erath.

If you don’t know in advance how many factors will be (because you don’t know what sequence of type values ​​may appear on the chart) and do not want to re-formulate the request each time, then the simplest task is to write a recursive function that takes the sequence of values ​​of “Type” as one argument and the "Root" element that you are working on as another argument, and processes one measure at a time.

This function performs this task to enter your sample:

 declare function local:cartesian-product( $doc as element(), $types as xs:string* ) as xs:string* { (: If we have no $types left, we are done. Return the empty string. :) if (empty($types)) then '' (: Otherwise, take the first value off the sequence of types and return the Cartesian product of all Words with that type and the Cartesian product of all the remaining types. :) else let $t := $types[1], $rest := $types[position() > 1] for $val in $doc/Word[@Type = $t]/@Value for $suffix in local:cartesian-product($doc,$rest) return concat($val, $suffix) }; 

The only remaining problem is the slightly difficult task of obtaining a sequence of different "Type" values ​​in document order. We could just call distinct-values($doc//Word/@Type) to get the values, but there is no guarantee that they will be in the order of the document.

Borrowing from the solution of Dimitre Novatchev for a related problem , we can calculate the corresponding sequence of values ​​of the type: thus:

 let $doc := <Root> <Word Type="pre1" Value="A" /> <Word Type="pre1" Value="D" /> <Word Type="pre2" Value="G" /> <Word Type="pre2" Value="H" /> <Word Type="base" Value="B" /> <Word Type="post1" Value="C" /> <Word Type="post1" Value="E" /> <Word Type="post1" Value="F" /> </Root> let $types0 := ($doc/Word/@Type), $types := $types0[index-of($types0,.)[1]] 

This returns individual values ​​in document order.

Now we are ready to calculate the desired result:

 return local:cartesian-product($doc, $types) 

Results are returned in an order that is slightly different from the order you give; I assume that you do not care about the sequence of results:

AGBC AGBE AGBF AHBC AHBE AHBF DGBC DGBE DGBF DHBC DHBE DHBF