I have an XML with data as below:
<row>
<c1>Name1></c1>
<c2>Name2</c2>
<c6>Addressline</c6>
<c6 m=2>Addressline 2 </c6>
<c6 m=3>Addressline 3 </c6>
</row>.
I want to see data as
c1 c2 c6
Name1 Name2 [Addressline, Addressline 2, Addressline 3.]
My query is returning only one value at a time, while I want concatenated outcome along with single valued columns data.
Select Single.*
from dbo.CUSTOMER
A, XMLTABLE('/row' passing xmlrecord
COLUMNS ID INTEGER PATH '@id' ,
C1 PATH '/row/c1',
C2 PATH '/row/c2'
C6 varchar(100) PATH '/row/c6[position()SIngle
Please help