SQL Query - Group column as XML

Question

I have a table that looks like this in MS SQL Server.

I want to query this table so that the TagId and Value columns are grouped together by EntityId and displayed as XML.

Sample query result

The XML structure is not fixed. Any type of XML structure will do. Is it possible?

Almost everything is possible in SQL (it is [Turing complete language](https://stackoverflow.com/questions/7284/what-is-turing-complete)). If you post sample data https://dbfiddle.uk instead of pictures I could prepare solution(simple FOR XML) — Lukasz Szozda, May 25 '18 at 09:54

sticky bit · Accepted Answer · 2018-05-25T11:21:09.843

You could use:

SELECT o.EntityName,
       o.EntityId,
       (SELECT (SELECT i.TagId "Tag/@id",
                       i.Value "Tag/@value"
                       FROM elbat i
                       WHERE i.EntityId = o.EntityId
                       FOR XML PATH(''),
                               TYPE)
               FOR XML PATH('Tags')) Value
       FROM elbat o
       GROUP BY o.EntityName,
                o.EntityId;

SQL Fiddle

score 0 · Answer 2 · answered May 25 '18 at 10:44

This not seems like a better method. But it gives solution:

DECLARE @Table TABLE(id int, TagId int, Entityname Varchar(100),EntityId INt, value VARCHAR(50))

INSERT INTO @Table VALUES(1,1,'TravelerProfile',856,'Finanace')
INSERT INTO @Table VALUES(2,2,'TravelerProfile',856,'A')
INSERT INTO @Table VALUES(3,3,'TravelerProfile',856,'pune')

 select
   distinct Entityname, EntityId,
    stuff((
        select ',' + 'OpenTag'+ CAST(u.id AS VARCHAR)+'MidTag'+ u.value+'EndTag'
        from @Table u
        where u.EntityId = EntityId
        order by u.EntityId
        for xml path('')
    ),1,1,'') as Value
INTO #temp
from @Table
group by Entityname,EntityId

SELECT Entityname, EntityId
    ,REPLACE(REPLACE(REPLACE(REPLACE(VALUE,'OpenTag','<Tags><TagId = "'),'MidTag','" value="'),'EndTag','"/></Tags>'),',','') Value
FROM #temp

DROP TABLE #temp

Output:

Entityname      EntityId    Value
TravelerProfile 856         <Tags><TagId = "1" value="Finanace"/></Tags><Tags><TagId = "2" value="A"/></Tags><Tags><TagId = "3" value="pune"/></Tags>

SQL Query - Group column as XML

2 Answers2