group by xml in for xml clause

Question

I am trying to create a xml based on my query. The table structure that I have is:

   ReportDate   Rating  Currency    Spreads
    8/8/2014    B        EUR 1.0    280
    9/8/2014    A        USD 2.0    220
    10/8/2014   A        USD 2.0    330
    9/8/2014    B        EUR 1.0    170

Script to create a table :

CREATE TABLE [dbo].[SampleTable](
    [ReportDate] datetime,
    [Rating] [nchar](2) NOT NULL,
    [Currency] [varchar](50) NOT NULL,
    [Spreads] [varchar](50) NOT NULL
)

GO

INSERT INTO Person VALUES (8/8/2014,'B','EUR 1.0','280');
INSERT INTO Person VALUES (9/8/2014,'A','USD 2.0',220);
INSERT INTO Person VALUES (10/8/2014,'A','USD 2.0','330');
INSERT INTO Person VALUES (9/8/2014,'B','EUR 2.0',170);

GO The xml structure that I am trying to output is :

<Spread_Series>
    <Spreads  Region="EURO 1.0" OrigRating="B">
        <Spread Period="201408" AvgValue="280" />
        <Spread Period="201209" AvgValue="170" />
    </Spreads>
    <Spreads  Region="USD 2.0" OrigRating="A">
        <Spread Period="201409" AvgValue="220" />
        <Spread Period="201210" AvgValue="330" />
    </Spreads>
</Spread_Series>

The query that I am using is :

SELECT 
        (SELECT distinct reportdate AS "@Period" ,spreads AS "@AvgValue"
        FROM [cs].[spreads_clo]
        for xml path('Spread'), TYPE)
    FROM sampletable
    FOR XML PATH('Spread_Series'), ROOT('Reponse')

I am not able to get this section of the xml in the output:

<Spreads  Region="EURO 1.0" OrigRating="B">

The output from my query is:

<Spread_Series>
    <Spreads>
        <Spread Period="201408" AvgValue="280" />
        <Spread Period="201209" AvgValue="170" />
    </Spreads>
    <Spreads>
        <Spread Period="201409" AvgValue="220" />
        <Spread Period="201210" AvgValue="330" />
    </Spreads>
</Spread_Series>

How can I group by the query to get the xml structure?

Answer 1

Query -

DECLARE @t TABLE (
    ReportDate DATETIME,
    Rating NCHAR(2) NOT NULL,
    Currency VARCHAR(50) NOT NULL,
    Spreads VARCHAR(50) NOT NULL
)

INSERT INTO @t
VALUES
      ('20140808', 'B', 'EUR 1.0', '280')
    , ('20140809', 'A', 'USD 2.0', '220')
    , ('20140810', 'A', 'USD 2.0', '330')
    , ('20140809', 'B', 'EUR 1.0', '170')

SELECT [@Region] = t1.Currency, [@OrigRating] = t1.Rating, (
    SELECT DISTINCT [@Period] = ReportDate,
                    [@AvgValue] = Spreads
    FROM @t t2
    WHERE t1.Rating = t1.Rating
        AND t2.Currency = t1.Currency
    FOR XML PATH ('Spread'), TYPE
)
FROM (
    SELECT DISTINCT Rating, Currency
    FROM @t
) t1
FOR XML PATH ('Spread_Series')

Output -

<Spread_Series Region="USD 2.0" OrigRating="A ">
  <Spread Period="2014-08-09T00:00:00" AvgValue="220" />
  <Spread Period="2014-08-10T00:00:00" AvgValue="330" />
</Spread_Series>
<Spread_Series Region="EUR 1.0" OrigRating="B ">
  <Spread Period="2014-08-08T00:00:00" AvgValue="280" />
  <Spread Period="2014-08-09T00:00:00" AvgValue="170" />
</Spread_Series>