xxxxxxxxxx
-- Assume we have a table called "my_table" with columns "id" and "name"
-- We want to select all rows from the table as XML in a specific format
SELECT
(
SELECT id, name -- columns you want to include in the XML
FROM my_table
FOR XML PATH('row'), ROOT('data'), TYPE
).query('/data/row') AS xml_data;
xxxxxxxxxx
--Selecting it from same table or have different Tables just remeber
--to make sure the inner select where clause contains the correct condition
--STUFF is to remove comma added in the begining
SELECT Id, STUFF(
(SELECT ',' + name
FROM table1 t1
WHERE t1.Id = t2.Id
FOR XML PATH (''))
, 1, 1, '') AS ValuesGrouped
FROM table1 t2
GROUP BY Id;
xxxxxxxxxx
SELECT TOP(1) issue_code,
abc =
STUFF (
(SELECT TOP(100)
',' +issue_code
FROM rrf_tbl
FOR XML PATH('')), 1, 1, ''
)
FROM rrf_tbl GROUP by issue_code