I have the following XML code and I'm trying to generate the xml shown after the code - tried several approaches but no success.
There are 2 tables: [meiForecasting].[dbo].george_mdx has the data shown below and the data for the other follows. I'm trying to join the tables so I can get the xml such that for each UnitPrice in george_mdx that falls between each group of priceband (pricedfrom - pricedto), I'll be able to generate the xml format that follows.
See my code below.
The inner xml works perfectly, but the outer elements (priceband and pricebands) are the problem.
(Select
(select
pb.pricebandID '@Id',
pb.[pricedfrom] '@start',
pb.[PricedTo] '@end',
(
SELECT cast(yearnumber as int) AS '@number'
,(convert(varchar(12), cast(yearstart as date), 126)) AS '@yearstart'
,(convert(varchar(12), cast(yearend as date), 126)) AS '@yearend'
,(
SELECT cast(X.WeekNumber as int) AS '@number'
,cast(x.Quantity as int) AS '@units'
FROM [meiForecasting].[dbo].george_mdx AS X
WHERE cast(X.YearNumber as int)=cast(tbl.YearNumber as int)
FOR XML PATH('week'),TYPE
)
FROM [meiForecasting].[dbo].george_mdx AS tbl inner join meiforecasting.PLANNING.PriceBand as pb
ON (tbl.unitprice <= pb.PricedTo AND tbl.unitprice >= pb.PricedFrom)
GROUP BY YearNumber,YearStart,YearEnd, pb.GroupID
FOR XML PATH('year'),ROOT('years')
)
)
FOR XML PATH ('Priceband'),ROOT ('PriceBands')
)
)
table [meiForecasting].[dbo].george_mdx
+------------+------------+----------+-----------+
| yearnumber | weeknumber | quantity | unitprice |
+------------+------------+----------+-----------+
| 1 | 44 | 3807 | 45662.55 |
| 1 | 45 | 4048 | 45295.00 |
| 1 | 46 | 3862 | 45047.68 |
| 1 | 47 | 3677 | 41013.00 |
| 1 | 48 | 3695 | 39387.43 |
| 1 | 49 | 3871 | 43199.02 |
| 1 | 50 | 3686 | 39131.82 |
| 2 | 1 | 4287 | 51086.02 |
| 2 | 2 | 4751 | 60404.40 |
| 2 | 3 | 5536 | 67468.90 |
| 2 | 4 | 3712 | 45776.32 |
| 2 | 5 | 3367 | 44288.19 |
| 2 | 6 | 3598 | 45307.49 |
| 2 | 7 | 3461 | 47757.59 |
| 2 | 8 | 3334 | 43771.33 |
| 2 | 9 | 3325 | 41656.88 |
| 2 | 10 | 3313 | 44738.39 |
| 2 | 32 | 3303 | 40144.35 |
| 2 | 33 | 3229 | 45892.27 |
| 2 | 34 | 3313 | 44570.07 |
| 2 | 35 | 3338 | 44978.12 |
| 2 | 36 | 3432 | 46760.30 |
| 2 | 37 | 3340 | 48341.05 |
| 2 | 38 | 3597 | 48673.97 |
| 2 | 39 | 3628 | 48636.17 |
| 3 | 29 | 3179 | 43610.97 |
+------------+------------+----------+-----------+
+--------------------+---------+----------+
| Planning. |priceband| |
+--------------------+---------+----------+
| |
| 43 | 0.00 | 1000.00 |
| 45 | 0.00 | 1000.00 |
| 49 | 0.00 | 999.99 |
| 50 | 1000.00 | 1999.99 |
| 51 | 2000.00 | 9999.00 |
| 52 | 0.00 | 15.00 |
| 53 | 15.01 | 300.00 |
| 62 | 0.00 | 15.00 |
| 63 | 15.01 | 200.00 |
| 69 | 100.00 | 500.99 |
| 70 | 501.00 | 1000.00 |
| 78 | 0.00 | 299.99 |
| 79 | 300.00 | 399.99 |
| 80 | 400.00 | 499.99 |
| 81 | 500.00 | 599.99 |
| 82 | 600.00 | 699.99 |
| 83 | 700.00 | 999.99 |
| 84 | 1000.00 | 10000.00 |
| 125 | 50.00 | 99.99 |
| 126 | 100.00 | 199.99 |
| 127 | 200.00 | 299.99 |
| 128 | 300.00 | 399.99 |
| 129 | 400.00 | 9999.00 |
+--------------------+---------+----------+
expected XML (SAMPLE ONLY-Figures may not be correct)
<priceBands> <priceBand id="1" end="99.99" start="0.00">
<years> <year number="1">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> -<year number="2">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="3">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> -<year number="4">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="5">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> </years> </priceBand>
<priceBand id="2" end="499.99" start="100.00">
<years> <year number="1">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="2">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="3">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="4">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> -<year number="5">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
.
.
.
<week number="52" units="41"/>
</year> </years> </priceBand>
<priceBand id="3" end="999.99" start="500.00"> <years> <year number="1">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="2">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="3">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> -<year number="4">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> <year number="5">
<week number="1" units="41"/>
<week number="2" units="41"/>
<week number="3" units="41"/>
<week number="4" units="41"/>
<week number="5" units="41"/>
<week number="6" units="41"/>
<week number="7" units="41"/>
<week number="8" units="41"/>
<week number="9" units="41"/>
<week number="10" units="41"/>
<week number="11" units="41"/>
<week number="12" units="41"/>
<week number="13" units="41"/>
<week number="14" units="41"/>
<week number="15" units="41"/>
<week number="16" units="41"/>
<week number="17" units="41"/>
<week number="18" units="41"/>
<week number="19" units="41"/>
<week number="20" units="41"/>
<week number="21" units="41"/>
<week number="22" units="41"/>
<week number="23" units="41"/>
<week number="24" units="41"/>
<week number="25" units="41"/>
<week number="26" units="41"/>
<week number="27" units="41"/>
<week number="28" units="41"/>
<week number="29" units="41"/>
<week number="30" units="41"/>
<week number="31" units="41"/>
<week number="32" units="41"/>
<week number="33" units="41"/>
<week number="34" units="41"/>
<week number="35" units="41"/>
<week number="36" units="41"/>
<week number="37" units="41"/>
<week number="38" units="41"/>
<week number="39" units="41"/>
<week number="40" units="41"/>
<week number="41" units="41"/>
<week number="42" units="41"/>
<week number="43" units="41"/>
<week number="44" units="41"/>
<week number="45" units="41"/>
<week number="46" units="41"/>
<week number="47" units="41"/>
<week number="48" units="41"/>
<week number="49" units="41"/>
<week number="50" units="41"/>
<week number="41" units="41"/>
<week number="52" units="41"/>
</year> </years>
</priceBand> </priceBands>
My magic glass bulb tells me, that you might be looking for something like this:
But (see my comments below your question) there's a lot not clear...
SELECT pricebandID AS [@id]
,pricedTo AS [@end]
,pricedFrom AS [@start]
,(
SELECT cast(yearnumber as int) AS '@number'
,(convert(varchar(12), cast(yearstart as date), 126)) AS '@yearstart'
,(convert(varchar(12), cast(yearend as date), 126)) AS '@yearend'
,(
SELECT cast(X.WeekNumber as int) AS '@number'
,cast(x.Quantity as int) AS '@units'
FROM @george_mdx AS X
WHERE cast(X.YearNumber as int)=cast(tbl.YearNumber as int)
AND X.unitprice BETWEEN pl.pricedFrom AND pl.pricedTo
FOR XML PATH('week'),TYPE
)
FROM @george_mdx AS tbl
WHERE tbl.unitprice BETWEEN pl.pricedFrom AND pl.pricedTo
GROUP BY YearNumber,YearStart,YearEnd
FOR XML PATH('year'),ROOT('years')
)
FROM @Planning AS pl
FOR XML PATH('priceBand'),ROOT('priceBands')