SQL 'FOR XML' query

[brendangallaghe]

Hello All

I am trying to write a SQL 'FOR XML' query that produces an XML block in a specific xml format. The query I have so far is close but I am having problems getting it produce the exact xml format that I need. I hoping someone on here can help me.

Using the following SQL, I populate the table against which, the SQL FOR XML query is run:

 
 
 
CREATE TABLE PerfTable
    (
 
    ID            INT  NOT NULL,
    Name            VARCHAR(500) NOT NULL,
    P_Performance1        NUMERIC(10,2),
    B_Performance1            NUMERIC(10,2),
    P_Performance2        NUMERIC(10,2),
    B_Performance2            NUMERIC(10,2),
    P_Performance3        NUMERIC(10,2),
    B_Performance3            NUMERIC(10,2)
); 
 
 
 
 
 
 
 
insert PerfTable (id,Name,P_Performance1,B_Performance1,P_Performance2,B_Performance2,P_Performance3,B_Performance3)
values (111,'Item1', -0.111,-0.112,            -0.121,-0.122,        -0.131,-0.132)
 
insert PerfTable (id,Name,P_Performance1,B_Performance1,P_Performance2,B_Performance2,P_Performance3,B_Performance3)
values (222,'Item2', -0.211,-0.212,            -0.221,-0.222,        -0.231,-0.232)
 
 
insert PerfTable (id,Name,P_Performance1,B_Performance1,P_Performance2,B_Performance2,P_Performance3,B_Performance3)
values (333,'Item3', -0.311,-0.312,            -0.321,-0.322,        -0.331,-0.332)
 
 
 
 
 
 
SELECT    TOP 9 id, Name,
        period as "Period_Performance/@Period",
        F_Perf as "Period_Performance/F_Perf",
        B_Perf as "Period_Performance/B_Perf"
FROM 
(
    SELECT         
                pt.id,
                pt.Name,
                pt.P_Performance1 ,
                pt.B_Performance1,
                'WTD' as Period1,
                pt.P_Performance2 ,
                pt.B_Performance2,
                'MTD' as Period3,
                pt.P_Performance3 ,
                pt.B_Performance3,
                'YTD' as Period2
    FROM PerfTable pt
)a
UNPIVOT
    (F_Perf FOR F IN 
        (P_Performance1,P_Performance2,P_Performance3)
)AS Fund_unpvt
UNPIVOT
    (B_Perf FOR B IN 
        (B_Performance1,B_Performance2,B_Performance3)
)AS bmk_unpvt
UNPIVOT
    (period FOR periods IN 
        (Period1,Period2, Period3)
)AS period_unpvt        
WHERE (RIGHT(F,1) =  RIGHT(B,1)) and (RIGHT(F,1) =  RIGHT(periods,1))
FOR XML PATH('Performance')
 
 
 

Then I run the following query:

 
 
 
SELECT    id, Name,
        period as "Period_Performance/@Period",
        F_Perf as "Period_Performance/F_Perf",
        B_Perf as "Period_Performance/B_Perf"
FROM 
(
    SELECT         
                pt.id,
                pt.Name,
                pt.P_Performance1 ,
                pt.B_Performance1,
                'WTD' as Period1,
                pt.P_Performance2 ,
                pt.B_Performance2,
                'MTD' as Period3,
                pt.P_Performance3 ,
                pt.B_Performance3,
                'YTD' as Period2
    FROM PerfTable pt
)a
UNPIVOT
    (F_Perf FOR F IN 
        (P_Performance1,P_Performance2,P_Performance3)
)AS Fund_unpvt
UNPIVOT
    (B_Perf FOR B IN 
        (B_Performance1,B_Performance2,B_Performance3)
)AS bmk_unpvt
UNPIVOT
    (period FOR periods IN 
        (Period1,Period2, Period3)
)AS period_unpvt        
WHERE (RIGHT(F,1) =  RIGHT(B,1)) and (RIGHT(F,1) =  RIGHT(periods,1))
FOR XML PATH('Performance')
 
 
 

This query produces the following XML:

<Performance>
  <id>111</id>
  <Name>Item1</Name>
  <Period_Performance Period="WTD">
    <F_Perf>-0.11</F_Perf>
    <B_Perf>-0.11</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>111</id>
  <Name>Item1</Name>
  <Period_Performance Period="YTD">
    <F_Perf>-0.12</F_Perf>
    <B_Perf>-0.12</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>111</id>
  <Name>Item1</Name>
  <Period_Performance Period="MTD">
    <F_Perf>-0.13</F_Perf>
    <B_Perf>-0.13</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>222</id>
  <Name>Item2</Name>
  <Period_Performance Period="WTD">
    <F_Perf>-0.21</F_Perf>
    <B_Perf>-0.21</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>222</id>
  <Name>Item2</Name>
  <Period_Performance Period="YTD">
    <F_Perf>-0.22</F_Perf>
    <B_Perf>-0.22</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>222</id>
  <Name>Item2</Name>
  <Period_Performance Period="MTD">
    <F_Perf>-0.23</F_Perf>
    <B_Perf>-0.23</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>333</id>
  <Name>Item3</Name>
  <Period_Performance Period="WTD">
    <F_Perf>-0.31</F_Perf>
    <B_Perf>-0.31</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>333</id>
  <Name>Item3</Name>
  <Period_Performance Period="YTD">
    <F_Perf>-0.32</F_Perf>
    <B_Perf>-0.32</B_Perf>
  </Period_Performance>
</Performance>
<Performance>
  <id>333</id>
  <Name>Item3</Name>
  <Period_Performance Period="MTD">
    <F_Perf>-0.33</F_Perf>
    <B_Perf>-0.33</B_Perf>
  </Period_Performance>
</Performance>
 
 
 

This XML that I need to produce is below:

 
    <Performance>
      <id>1</id> 
      <Name>Item1</Name> 
      <Period_Performance Period="WTD">
        <F_Perf>-0.11</F_Perf> 
        <B_Perf>-0.11</B_Perf>
      </Period_Performance>
      <Period_Performance Period="YTD">
        <F_Perf>-0.12</F_Perf> 
        <B_Perf>-0.12</B_Perf> 
      </Period_Performance>
      <Period_Performance Period="MTD">
        <F_Perf>-0.13</F_Perf> 
        <B_Perf>-0.13</B_Perf> 
      </Period_Performance>
    </Performance>
 
 

Any help you can give is greatly appreciated.

Thanks

[brendangallaghe]

This issue has been resolved. Thank you.