Transformation of XML tree to flat text using SQL Server 2008 XQuery

Question

I have some XML data representing a mathematical expression tree and want to convert this to a flat math formula. Sounds simple, but the XQuery restrictions in SQL Server currently stopped me from succeeding (no recursive functions, problems with "heterogeneous" results etc.).

The expression can be of an arbitrary nesting depth. Here's a sample (the data is in a xml column of a table later on, but that's good enough for testing here):

DECLARE @expr xml;
SET @expr = '<expression aggregator="+">
  <indicator>122F277B-A241-7944-BC38-3BB5E8B213AF</indicator>
  <indicator>7DD46849-2193-EB41-8BAB-CE0C45255249</indicator>
  <expression aggregator="*">
    <expression aggregator="/">
      <indicator>122F277B-A241-7944-BC38-3BB5E8B213AF</indicator>
      <indicator>27F3156D-FDA7-1E44-B545-7F27A48D9838</indicator>
    </expression>
    <indicator>ADFCEF34-9877-DE4E-8A00-13576437D82B</indicator>
    <value>12</value>
  </expression>
  <expression aggregator="-">
    <indicator>ADFCEF34-9877-DE4E-8A00-13576437D82B</indicator>
    <indicator>75896474-C197-1C44-8EAA-8FE9D0AB2663</indicator>
  </expression>
  <indicator>27F3156D-FDA7-1E44-B545-7F27A48D9838</indicator>
</expression>';

The required result would be (whitespace is insignificant):

(
  [122F277B-A241-7944-BC38-3BB5E8B213AF] +
  [7DD46849-2193-EB41-8BAB-CE0C45255249] +
  (
    (
      [122F277B-A241-7944-BC38-3BB5E8B213AF] /
      [27F3156D-FDA7-1E44-B545-7F27A48D9838]
    ) *
    [ADFCEF34-9877-DE4E-8A00-13576437D82B] *
    12
  ) +
  (
    [ADFCEF34-9877-DE4E-8A00-13576437D82B] -
    [75896474-C197-1C44-8EAA-8FE9D0AB2663]
  ) +
  [27F3156D-FDA7-1E44-B545-7F27A48D9838]
)

Does someone master XQuery in SQL Server 2008 (R2) well enough to perform this transformation?

Answer 1

Not pretty but it seems to work. A recursive UDF.

create function GetExpression(@expr xml) returns varchar(max)
as
begin
  declare @max int
  declare @i int = 1
  declare @nodetype varchar(50)
  declare @aggregator char(1)
  declare @res varchar(max) = '('
  declare @value varchar(36)
  declare @SubExpr xml

  select @max=count(*)
  from @expr.nodes('/expression/*') as n(e)

  select @aggregator = n.e.value('@aggregator', 'char(1)')
  from @expr.nodes('expression') as n(e)

  while @i <= @max
  begin
    select
      @nodetype = x.value('local-name(.)[1]', 'varchar(36)'),
      @value = x.value('.', 'varchar(36)'),
      @SubExpr = x.query('.')
    from @expr.nodes('/expression/*[position()=sql:variable("@i")]') e(x)

    if @nodetype = 'indicator'
      set @res = @res + '[' + @value + ']'
    else
    if @nodetype = 'expression'
      set @res = @res + dbo.GetExpression(@SubExpr)
    else  
    if @nodetype = 'value'
      set @res = @res + @value

    if @i < @max
      set @res = @res + @aggregator

    set @i = @i + 1        
  end

  set @res = @res + ')'

 return @res
end