Using TSQL how can I return XML from a table while grouping by a field?

Question

It's hard to explain, but I'll provide an example. Lets say I have the following table:

PolicyNo    ClientNo    Name
--------    --------    ----
  123          1        John         
  123          2        Sally
  ABC          3        Alice
  ABC          4        Bob

And I'd like to use TSQL and "FOR XML" to group information under similar Policy Nos, like the following:

<root>
    <Policy>
        <PolicyNo> 123 </PolicyNo>
        <ClientInfo>
            <ClientNo> 1 </ClientNo>
            <Name> John </Name>
        </ClientInfo>
        <ClientInfo>
            <ClientNo> 2 </ClientNo>
            <Name> Sally </Name>
        </ClientInfo>
    </Policy>
    <Policy>
        <PolicyNo> ABC </PolicyNo>
        <ClientInfo>
            <ClientNo> 3 </ClientNo>
            <Name> Alice </Name>
        </ClientInfo>
        <ClientInfo>
            <ClientNo> 4 </ClientNo>
            <Name> Bob </Name>
        </ClientInfo>
    </Policy>
</root>

Gottfried Lesigang · Accepted Answer · 2017-06-21 20:15:13Z

1

This is just a supplement to your own, great solution:

SELECT 
    T1.PolicyNo AS "PolicyNo",
    (SELECT
        T2.ClientNo AS "ClientNo",
        T2.Name AS "Name"
    FROM @T T2
    WHERE T1.PolicyNo=T2.PolicyNo
    FOR XML PATH ('ClientInfo'),TYPE)
FROM @T T1
GROUP BY T1.PolicyNo
FOR XML PATH ('Policy'), ROOT ('root')

There's no need to use a CAST(... AS XML). Just use ,TYPE to force the sub-select to be handled as native XML.

I do not know at the moment, whether this would impact performance (read as string an re-cast to XML) or if the engine is smart enough to encounter, that this cast is not needed actually...

answered Jun 21, 2017 at 20:15

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3457834 · Accepted Answer · 2017-06-21 20:06:39Z

1

I actually figure out the answer to my own question after playing around for a little bit. Here's my solution:

declare @T table
(
  PolicyNo varchar(3),
  ClientNo int,
  Name varchar(10)
)

insert into @T values
('123', 1, 'John'),
('123', 2, 'Sally'),
('ABC', 3, 'Alice'),
('ABC', 4, 'Bob')

SELECT 
    T1.PolicyNo AS "PolicyNo",
    cast((SELECT
        T2.ClientNo AS "ClientNo",
        T2.Name AS "Name"
    FROM @T T2
    WHERE T1.PolicyNo=T2.PolicyNo
    FOR XML PATH ('ClientInfo')) as XML)
FROM @T T1
GROUP BY T1.PolicyNo
FOR XML PATH ('Policy'), ROOT ('root')

answered Jun 21, 2017 at 20:06

user3457834

3243 silver badges13 bronze badges

Collectives™ on Stack Overflow

Using TSQL how can I return XML from a table while grouping by a field?

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