SQL Server concat Multiple values in XML string and return as single row

Question

SQL Server 2012 Developer edition

TESTMACHINE:

OID MACHINENAME 
---------------
1    TEST A
2    TEST B
3    TEST C

TESTDETAIL:

OID TESTMACHINEOID TESTID   HISTORYID
-------------------------------------
1       1           200         100
2       1           300         250
3       2           1000        2345
4       3           100         150

TestMachineOID is the FK to the TestMachine table

I need to concatenate items into an XML by grouping TestMachineOID. Is it possible to get the below desired output?

TESTMACHINEOID  TESTMACHINENAME     TEXTXML
------------------------------------------------------------------------
1                   TEST A          <REC><TESTID>200</TESTID><HISTORYID>100<HISTORYID><TESTID>300</TESTID><HISTORYID>250<HISTORYID></REC>
2                   TEST B          <REC><TESTID>1000</TESTID><HISTORYID>2345<HISTORYID></REC>
3                   TEST C          <REC><TESTID>100</TESTID><HISTORYID>150<HISTORYID></REC>

Marid · Accepted Answer · 2017-11-11 09:05:07Z

drop table #testmachine
drop table #testdetail

create table #testmachine (oid int , name nvarchar(10))
create table #testdetail (oid int, machineoid int, testid int, historyid int)

insert into #testmachine (oid, name) values (1,'A')
insert into #testmachine (oid, name) values (2,'B')
insert into #testmachine (oid, name) values (3,'C')

insert into #testdetail (oid, machineoid,testid,historyid) values (1,1,10,20)
insert into #testdetail (oid, machineoid,testid,historyid) values (2,1,30,40)
insert into #testdetail (oid, machineoid,testid,historyid) values (3,2,50,60)
insert into #testdetail (oid, machineoid,testid,historyid) values (4,2,70,80)
insert into #testdetail (oid, machineoid,testid,historyid) values (5,3,90,100)


select 
        tm.oid, 
        tm.name,
        (           
            select testid, historyid
            from #testdetail td 
            where td.machineoid = tm.oid
            for xml path('rec') , root('signal')
        ) as XMLvalue

from 
        #testmachine tm

output

1   A   <signal><rec><testid>10</testid><historyid>20</historyid></rec><rec><testid>30</testid><historyid>40</historyid></rec></signal>
2   B   <signal><rec><testid>50</testid><historyid>60</historyid></rec><rec><testid>70</testid><historyid>80</historyid></rec></signal>
3   C   <signal><rec><testid>90</testid><historyid>100</historyid></rec></signal>

Peter Majko · Accepted Answer · 2017-11-11 08:35:21Z

0

What you need is a "for loop" and fill an additional table with it.

Loop through the testmachine table and for each OID select all records in testdetail, then with another nested for loop add them one by one with clause

SET TESTXML = CONCAT(TESTXML, new_value)

answered Nov 11, 2017 at 8:35

Peter Majko

1,33113 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL Server concat Multiple values in XML string and return as single row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related