SQL 'FOR XML' query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 'FOR XML' query

Author

Topic

BGallagher1976
Starting Member

2 Posts

Posted - 2011-03-06 : 13:28:22

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>
<Performance>
<id>2</id>
<Name>Item2</Name>
<Period_Performance Period="WTD">
<F_Perf>-0.21</F_Perf>
<B_Perf>-0.21</B_Perf>
</Period_Performance>
<Period_Performance Period="YTD">
<F_Perf>-0.22</F_Perf>
<B_Perf>-0.22</B_Perf>
</Period_Performance>
<Period_Performance Period="MTD">
<F_Perf>-0.23</F_Perf>
<B_Perf>-0.23</B_Perf>
</Period_Performance>
</Performance>
<Performance>
<id>3</id>
<Name>Item3</Name>
<Period_Performance Period="WTD">
<F_Perf>-0.31</F_Perf>
<B_Perf>-0.31</B_Perf>
</Period_Performance>
<Period_Performance Period="YTD">
<F_Perf>-0.32</F_Perf>
<B_Perf>-0.32</B_Perf>
</Period_Performance>
<Period_Performance Period="MTD">
<F_Perf>-0.33</F_Perf>
<B_Perf>-0.33</B_Perf>
</Period_Performance>
</Performance>

Any help to create the desired XML you can give, is greatly appreciated.

Thanks

BGallagher1976
Starting Member

2 Posts

Posted - 2011-03-07 : 05:49:59

Resolved. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources