CODEWITHSUNDEEP
sql-serverxmlt-sqlsql-server-2012
Stigz
Stigz

Reputation: 53

Group XML Column Data

Using SQL Server 2012. I have a table containing a group identifier and small snippets of XML. Here is a sample:

CREATE TABLE temp (
    GroupId [int] NOT NULL,
    RawXml [xml] NULL
)

INSERT INTO temp VALUES (1, '<Item><Criteria Type="State" Values="CA"/><Criteria Type="State" Values="TX"/><Criteria Type="State" Values="FL"/></Item>')
INSERT INTO temp VALUES (1, '<Item><Criteria Type="Manager" Values="Tim"/></Item>')
INSERT INTO temp VALUES (2, '<Item><Criteria Type="Name" Values="Walters"/></Item>')
INSERT INTO temp VALUES (2, '<Item><Criteria Type="Manager" Values="Tim"/></Item>')

What I want to do is group the snippets of XML together by the GroupId to form a larger XML document so the end result is structured like this:

<Parent>
    <Group GroupId="1">
        <Item>
            <Criteria Type="State" Values="CA"/>
            <Criteria Type="State" Values="TX"/>
            <Criteria Type="State" Values="FL"/>
        </Item>
        <Item>
            <Criteria Type="Manager" Values="Tim"/>
        </Item>
    </Group>
    <Group GroupId="2">
        <Item>
            <Criteria Type="Name" Values="Walters"/>
        </Item>
        <Item>
            <Criteria Type="Manager" Values="Tim"/>
        </Item>
    </Group>
</Parent>

I fear the only solution is to concatenate the contents of the XML column first using some type of string concatenation method, and then using a FOR XML select statement, but I feel this will be too slow for my application. Am I missing something and there is an easy way to group xml data in this manner, or is string concatenation my only choice.

Upvotes: 1

Views: 402

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

Of course there is a simpler way:

declare @t table (
    GroupId [int] NOT NULL,
    RawXml [xml] NULL
);

INSERT INTO @t
VALUES (1, '<Item><Criteria Type="State" Values="CA"/><Criteria Type="State" Values="TX"/><Criteria Type="State" Values="FL"/></Item>'),
(1, '<Item><Criteria Type="Manager" Values="Tim"/></Item>'),
(2, '<Item><Criteria Type="Name" Values="Walters"/></Item>'),
(2, '<Item><Criteria Type="Manager" Values="Tim"/></Item>');

select sq.GroupId as [@GroupId],
    (
        select t.RawXml as [node()]
        from @t t
        where t.GroupId = sq.GroupId
        for xml path(''), type
    )
from (select distinct g.GroupId from @t g) sq
for xml path('Group'), type, root('Parent');

(I have replaced your original table with the equivalent TV because, well, I don't like to clean up databases of such tables afterwards. It doesn't introduce any difference with regards to the question.)

Upvotes: 2

Related Questions