SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #example
    ([CCP_DETAILS_SID] int, [ACCOUNT_GROWTH] int, [PRODUCT_GROWTH] int, [PROJECTION_SALES] numeric(22,6), [PROJECTION_UNITS] numeric(22,6), [PERIOD_SID] int)
;
INSERT INTO #example
    ([CCP_DETAILS_SID], [ACCOUNT_GROWTH], [PRODUCT_GROWTH], [PROJECTION_SALES], [PROJECTION_UNITS], [PERIOD_SID])
VALUES
    (30001, 0, 0, 1505384.695, 18487.25251, 1801),
    (30001, 0, 0, 1552809.983, 18695.75536, 1802),
    (30001, 0, 0, 1595642.121, 18834.75725, 1803),
    (30002, 0, 0, 10000.32, 18834.75725, 1801),
    (30002, 0, 0, 1659124.98, 18834.75725, 1802),
    (30002, 0, 0, 465859546.6, 18834.75725, 1803)
;
6 rows affected

SELECT  t.CCP_DETAILS_SID,
        (   SELECT  PERIOD_SID AS [@period_sid],
                    x.PROJECTION_SALES,
                    x.PROJECTION_UNITS,
                    x.ACCOUNT_GROWTH,
                    x.PRODUCT_GROWTH
            FROM    #Example AS x
            WHERE   x.CCP_DETAILS_SID = t.CCP_DETAILS_SID
            FOR XML PATH('period_sid'), TYPE, ROOT('period')
        ) AS xml_format_string
FROM    #Example AS t
GROUP BY t.CCP_DETAILS_SID;


CCP_DETAILS_SID
xml_format_string


30001
<period><period_sid period_sid="1801"><PROJECTION_SALES>1505384.695000</PROJECTION_SALES><PROJECTION_UNITS>18487.252510</PROJECTION_UNITS><ACCOUNT_GROWTH>0</ACCOUNT_GROWTH><PRODUCT_GROWTH>0</PRODUCT_GROWTH></period_sid><period_sid period_sid="1802"><PROJECTION_SALES>1552809.983000</PROJECTION_SALES><PROJECTION_UNITS>18695.755360</PROJECTION_UNITS><ACCOUNT_GROWTH>0</ACCOUNT_GROWTH><PRODUCT_GROWTH>0</PRODUCT_GROWTH></period_sid><period_sid period_sid="1803"><PROJECTION_SALES>1595642.121000</PROJECTION_SALES><PROJECTION_UNITS>18834.757250</PROJECTION_UNITS><ACCOUNT_GROWTH>0</ACCOUNT_GROWTH><PRODUCT_GROWTH>0</PRODUCT_GROWTH></period_sid></period>

30002
<period><period_sid period_sid="1801"><PROJECTION_SALES>10000.320000</PROJECTION_SALES><PROJECTION_UNITS>18834.757250</PROJECTION_UNITS><ACCOUNT_GROWTH>0</ACCOUNT_GROWTH><PRODUCT_GROWTH>0</PRODUCT_GROWTH></period_sid><period_sid period_sid="1802"><PROJECTION_SALES>1659124.980000</PROJECTION_SALES><PROJECTION_UNITS>18834.757250</PROJECTION_UNITS><ACCOUNT_GROWTH>0</ACCOUNT_GROWTH><PRODUCT_GROWTH>0</PRODUCT_GROWTH></period_sid><period_sid period_sid="1803"><PROJECTION_SALES>465859546.600000</PROJECTION_SALES><PROJECTION_UNITS>18834.757250</PROJECTION_UNITS><ACCOUNT_GROWTH>0</ACCOUNT_GROWTH><PRODUCT_GROWTH>0</PRODUCT_GROWTH></period_sid></period>