Equivalent to Informix genxml() function

Question

I need to convert a number of Informix views to run on SQL Server.
These views use Informix function genxml() which retrieves and concatenates data from two tables where the second table parses XML output using REPLACE to strip XML tags and keep descriptions.

TABLE A (Request)

Request
1
2
3

TABLE B (Description)

Request,line,description
1,1,'this is a'
1,2,' request description'
2,1,'this is another'
2,2,' one'
3,1,'...and a third one'

The output should look line this:

1,'this is a request description'
2,'this is another one'
3,'...and a third one'

Informix code

create view "dbo".mySample_view (fld_A,fld_B,fld_C) as
select x0.fld_A ,x0.fld_B ,
(select TRIM ( BOTH ' ' FROM REPLACE (REPLACE (REPLACE (informix.genxml(row(TRIM ( BOTH ' ' FROM x1.fld_C ) ),'-' ) ,'<- expression=' ,' ' ),'/>' ,'' ),'"' ,'' ))
from description x1 where ((x1.fld_A = x0.fld_A ) AND (x1.fld_B = x0.fld_B ) ) )
from request x0 ;

Wondering if there is a similar function in SQL server that can produce the same result. I tried to use RAW FOR

Answer 1

Hi @Santos, Luis ,

What is your version of SQL Server?

Please refer below and check whethe it is helpful to you.

DDL:

create table Request  
(Request int)  
  
insert into Request VALUES  
(1),  
(2),  
(3)  
  
create table Description  
(Request int,  
Line int,  
description varchar(100))  
  
insert into Description VALUES  
(1,1,'this is a'),  
(1,2,' request description'),  
(2,1,'this is another'),  
(2,2,' one'),  
(3,1,'...and a third one')  

SQL Server 2017 and later:

select a.Request,string_agg(b.description,'') within group (order by b.line) description  
from Request a   
inner join Description b   
on a.Request=b.Request  
group by a.Request  

SQL Server 2016 and before:

SELECT a.Request, STUFF((  
            SELECT ' ' + b.description  
            from  Description b   
            where b.Request=a.Request  
            order by b.Line  
            FOR XML PATH('')  
            ), 1, 1, '') description  
FROM  Request a   

Output:

Request description  
1 this is a request description  
2 this is another one  
3 ...and a third one  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Check an example:

declare @tableA table ( Request int )  
insert @tableA values  
( 1 ),   
( 2 ),   
( 3 )   
  
declare @tableB table ( Request int, line int, description varchar(max) )  
insert @tableB values  
( 1, 1, 'this is a'            ),  
( 1, 2, ' request description' ),  
( 2, 1, 'this is another'      ),  
( 2, 2, ' one'                 ),  
( 3, 1, '...and a third one'   )  
  
  
select a.Request,  
    (  
        select string_agg(description, '') within group ( order by line )  
        from @tableB  
        where Request = a.Request  
        group by Request  
    )  
from @tableA a  

The view can be based on this approach.

Answer 3

That's exactly what I was after.
Google searches and trial and error were pointing in the right direction but your simple and clear answer confirmed the correct approach.

Thank you so much.