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.