带有动态参数的 SQL Server XML [英] SQL Server XML with dynamic arguments
本文介绍了带有动态参数的 SQL Server XML的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张名为mdx"的表格
I have the following table called 'mdx'
yearnumber yearstart yearend weeknumber quantity
1 11-22-15 11-19-16 1 1826
1 11-22-15 11-19-16 2 1225
1 11-22-15 11-19-16 3 452
1 11-22-15 11-19-16 4 276
1 11-22-15 11-19-16 5 673
1 11-22-15 11-19-16 6 1986
1 11-22-15 11-19-16 7 3806
1 11-22-15 11-19-16 8 3608
1 11-22-15 11-19-16 9 3841
1 11-22-15 11-19-16 10 3356
1 11-22-15 11-19-16 11 3436
1 11-22-15 11-19-16 12 3437
1 11-22-15 11-19-16 13 3611
1 11-22-15 11-19-16 14 3354
1 11-22-15 11-19-16 15 3743
1 11-22-15 11-19-16 16 3508
1 11-22-15 11-19-16 17 3440
1 11-22-15 11-19-16 18 3467
1 11-22-15 11-19-16 19 2816
1 11-22-15 11-19-16 20 2998
1 11-22-15 11-19-16 21 2796
1 11-22-15 11-19-16 22 2624
1 11-22-15 11-19-16 23 2458
1 11-22-15 11-19-16 24 1924
1 11-22-15 11-19-16 25 1816
1 11-22-15 11-19-16 26 1671
1 11-22-15 11-19-16 27 1111
1 11-22-15 11-19-16 28 1036
1 11-22-15 11-19-16 29 746
1 11-22-15 11-19-16 30 590
1 11-22-15 11-19-16 31 665
1 11-22-15 11-19-16 32 393
1 11-22-15 11-19-16 33 442
1 11-22-15 11-19-16 34 2042
1 11-22-15 11-19-16 35 387
1 11-22-15 11-19-16 36 24
1 11-22-15 11-19-16 37 30
1 11-22-15 11-19-16 38 44
1 11-22-15 11-19-16 39 113
1 11-22-15 11-19-16 40 23
1 11-22-15 11-19-16 41 18
1 11-22-15 11-19-16 42 1624
1 11-22-15 11-19-16 43 3760
1 11-22-15 11-19-16 44 3645
1 11-22-15 11-19-16 45 3964
1 11-22-15 11-19-16 46 3807
1 11-22-15 11-19-16 47 4048
1 11-22-15 11-19-16 48 3862
1 11-22-15 11-19-16 49 3677
1 11-22-15 11-19-16 50 3695
1 11-22-15 11-19-16 51 3871
1 11-22-15 11-19-16 52 3686
2 11-23-14 11-21-15 1 3694
2 11-23-14 11-21-15 2 4240
2 11-23-14 11-21-15 3 4287
2 11-23-14 11-21-15 4 4751
2 11-23-14 11-21-15 5 5536
2 11-23-14 11-21-15 6 3712
2 11-23-14 11-21-15 7 3367
2 11-23-14 11-21-15 8 3598
2 11-23-14 11-21-15 9 3461
2 11-23-14 11-21-15 10 3334
2 11-23-14 11-21-15 11 3325
2 11-23-14 11-21-15 12 3313
2 11-23-14 11-21-15 13 3417
2 11-23-14 11-21-15 14 3396
2 11-23-14 11-21-15 15 3326
2 11-23-14 11-21-15 16 3274
2 11-23-14 11-21-15 17 3473
2 11-23-14 11-21-15 18 3442
2 11-23-14 11-21-15 19 3293
2 11-23-14 11-21-15 20 3114
2 11-23-14 11-21-15 21 3116
2 11-23-14 11-21-15 22 3664
2 11-23-14 11-21-15 23 3165
2 11-23-14 11-21-15 24 2896
2 11-23-14 11-21-15 25 3113
2 11-23-14 11-21-15 26 3183
2 11-23-14 11-21-15 27 2912
2 11-23-14 11-21-15 28 3043
2 11-23-14 11-21-15 29 3046
2 11-23-14 11-21-15 30 3033
2 11-23-14 11-21-15 31 3212
2 11-23-14 11-21-15 32 3294
2 11-23-14 11-21-15 33 3210
2 11-23-14 11-21-15 34 3303
2 11-23-14 11-21-15 35 3229
2 11-23-14 11-21-15 36 3313
2 11-23-14 11-21-15 37 3338
2 11-23-14 11-21-15 38 3432
2 11-23-14 11-21-15 39 3340
2 11-23-14 11-21-15 40 3597
2 11-23-14 11-21-15 41 3628
2 11-23-14 11-21-15 42 3412
2 11-23-14 11-21-15 43 3332
2 11-23-14 11-21-15 44 3313
2 11-23-14 11-21-15 45 3637
2 11-23-14 11-21-15 46 3498
2 11-23-14 11-21-15 47 3230
2 11-23-14 11-21-15 48 2591
2 11-23-14 11-21-15 49 2937
2 11-23-14 11-21-15 50 2964
2 11-23-14 11-21-15 51 2740
2 11-23-14 11-21-15 52 1982
3 11-24-13 11-22-14 1 4607
3 11-24-13 11-22-14 2 5018
3 11-24-13 11-22-14 3 5054
3 11-24-13 11-22-14 4 5834
3 11-24-13 11-22-14 5 5944
3 11-24-13 11-22-14 6 3992
3 11-24-13 11-22-14 7 4298
3 11-24-13 11-22-14 8 4307
3 11-24-13 11-22-14 9 4187
3 11-24-13 11-22-14 10 4269
3 11-24-13 11-22-14 11 4012
3 11-24-13 11-22-14 12 4392
3 11-24-13 11-22-14 13 4536
3 11-24-13 11-22-14 14 4173
3 11-24-13 11-22-14 15 4053
3 11-24-13 11-22-14 16 3882
3 11-24-13 11-22-14 17 4053
3 11-24-13 11-22-14 18 3990
3 11-24-13 11-22-14 19 3992
3 11-24-13 11-22-14 20 3489
3 11-24-13 11-22-14 21 3797
3 11-24-13 11-22-14 22 3355
3 11-24-13 11-22-14 23 3438
3 11-24-13 11-22-14 24 3275
3 11-24-13 11-22-14 25 3403
3 11-24-13 11-22-14 26 3345
3 11-24-13 11-22-14 27 3282
3 11-24-13 11-22-14 28 3378
3 11-24-13 11-22-14 29 3286
3 11-24-13 11-22-14 30 3304
3 11-24-13 11-22-14 31 3179
3 11-24-13 11-22-14 32 3248
3 11-24-13 11-22-14 33 3469
3 11-24-13 11-22-14 34 3497
3 11-24-13 11-22-14 35 3360
3 11-24-13 11-22-14 36 3217
3 11-24-13 11-22-14 37 3170
3 11-24-13 11-22-14 38 3462
3 11-24-13 11-22-14 39 3528
3 11-24-13 11-22-14 40 3466
3 11-24-13 11-22-14 41 3546
3 11-24-13 11-22-14 42 3549
3 11-24-13 11-22-14 43 3350
3 11-24-13 11-22-14 44 3286
3 11-24-13 11-22-14 45 3598
3 11-24-13 11-22-14 46 3725
3 11-24-13 11-22-14 47 3573
3 11-24-13 11-22-14 48 3681
3 11-24-13 11-22-14 49 3378
3 11-24-13 11-22-14 50 3481
3 11-24-13 11-22-14 51 3477
3 11-24-13 11-22-14 52 3673
我正在尝试在 sql server 中生成像这样的 xml
I'm trying to generate xml in sql server that will loo like this
<years>
<year yearnumber="1" yearstart="11-22-15" yearend="11-19-16">
<week weeknumber="1" quantity="10127" />
<week weeknumber="10" quantity="10959" />
<week weeknumber="11" quantity="10773" />
<week weeknumber="12" quantity="11142" />
<week weeknumber="13" quantity="11564" />
<week weeknumber="14" quantity="10923" />
<week weeknumber="15" quantity="11122" />
<week weeknumber="16" quantity="10664" />
<week weeknumber="17" quantity="10966" />
<week weeknumber="18" quantity="10899" />
<week weeknumber="19" quantity="10101" />
<week weeknumber="2" quantity="10483" />
<week weeknumber="20" quantity="9601" />
<week weeknumber="21" quantity="9709" />
<week weeknumber="22" quantity="9643" />
<week weeknumber="23" quantity="9061" />
<week weeknumber="24" quantity="8095" />
<week weeknumber="25" quantity="8332" />
<week weeknumber="26" quantity="8199" />
<week weeknumber="27" quantity="7305" />
<week weeknumber="28" quantity="7457" />
<week weeknumber="29" quantity="7078" />
<week weeknumber="3" quantity="9793" />
<week weeknumber="30" quantity="6927" />
<week weeknumber="31" quantity="7056" />
<week weeknumber="32" quantity="6935" />
<week weeknumber="33" quantity="7121" />
<week weeknumber="34" quantity="8842" />
<week weeknumber="35" quantity="6976" />
<week weeknumber="36" quantity="6554" />
<week weeknumber="37" quantity="6538" />
<week weeknumber="38" quantity="6938" />
<week weeknumber="39" quantity="6981" />
<week weeknumber="4" quantity="10861" />
<week weeknumber="40" quantity="7086" />
<week weeknumber="41" quantity="7192" />
<week weeknumber="42" quantity="8585" />
<week weeknumber="43" quantity="10442" />
<week weeknumber="44" quantity="10244" />
<week weeknumber="45" quantity="11199" />
<week weeknumber="46" quantity="11030" />
<week weeknumber="47" quantity="10851" />
<week weeknumber="48" quantity="10134" />
<week weeknumber="49" quantity="9992" />
<week weeknumber="5" quantity="12153" />
<week weeknumber="50" quantity="10140" />
<week weeknumber="51" quantity="10088" />
<week weeknumber="52" quantity="9341" />
<week weeknumber="6" quantity="9690" />
<week weeknumber="7" quantity="11471" />
<week weeknumber="8" quantity="11513" />
<week weeknumber="9" quantity="11489" />
</year>
<year yearnumber="2" yearstart="11-23-14" yearend="11-21-15">
<week weeknumber="1" quantity="10127" />
<week weeknumber="10" quantity="10959" />
<week weeknumber="11" quantity="10773" />
<week weeknumber="12" quantity="11142" />
<week weeknumber="13" quantity="11564" />
<week weeknumber="14" quantity="10923" />
<week weeknumber="15" quantity="11122" />
<week weeknumber="16" quantity="10664" />
<week weeknumber="17" quantity="10966" />
<week weeknumber="18" quantity="10899" />
<week weeknumber="19" quantity="10101" />
<week weeknumber="2" quantity="10483" />
<week weeknumber="20" quantity="9601" />
<week weeknumber="21" quantity="9709" />
<week weeknumber="22" quantity="9643" />
<week weeknumber="23" quantity="9061" />
<week weeknumber="24" quantity="8095" />
<week weeknumber="25" quantity="8332" />
<week weeknumber="26" quantity="8199" />
<week weeknumber="27" quantity="7305" />
<week weeknumber="28" quantity="7457" />
<week weeknumber="29" quantity="7078" />
<week weeknumber="3" quantity="9793" />
<week weeknumber="30" quantity="6927" />
<week weeknumber="31" quantity="7056" />
<week weeknumber="32" quantity="6935" />
<week weeknumber="33" quantity="7121" />
<week weeknumber="34" quantity="8842" />
<week weeknumber="35" quantity="6976" />
<week weeknumber="36" quantity="6554" />
<week weeknumber="37" quantity="6538" />
<week weeknumber="38" quantity="6938" />
<week weeknumber="39" quantity="6981" />
<week weeknumber="4" quantity="10861" />
<week weeknumber="40" quantity="7086" />
<week weeknumber="41" quantity="7192" />
<week weeknumber="42" quantity="8585" />
<week weeknumber="43" quantity="10442" />
<week weeknumber="44" quantity="10244" />
<week weeknumber="45" quantity="11199" />
<week weeknumber="46" quantity="11030" />
<week weeknumber="47" quantity="10851" />
<week weeknumber="48" quantity="10134" />
<week weeknumber="49" quantity="9992" />
<week weeknumber="5" quantity="12153" />
<week weeknumber="50" quantity="10140" />
<week weeknumber="51" quantity="10088" />
<week weeknumber="52" quantity="9341" />
<week weeknumber="6" quantity="9690" />
<week weeknumber="7" quantity="11471" />
<week weeknumber="8" quantity="11513" />
<week weeknumber="9" quantity="11489" />
</year>
<year yearnumber="3" yearstart="11-24-13" yearend="11-22-14">
<week weeknumber="1" quantity="10127" />
<week weeknumber="10" quantity="10959" />
<week weeknumber="11" quantity="10773" />
<week weeknumber="12" quantity="11142" />
<week weeknumber="13" quantity="11564" />
<week weeknumber="14" quantity="10923" />
<week weeknumber="15" quantity="11122" />
<week weeknumber="16" quantity="10664" />
<week weeknumber="17" quantity="10966" />
<week weeknumber="18" quantity="10899" />
<week weeknumber="19" quantity="10101" />
<week weeknumber="2" quantity="10483" />
<week weeknumber="20" quantity="9601" />
<week weeknumber="21" quantity="9709" />
<week weeknumber="22" quantity="9643" />
<week weeknumber="23" quantity="9061" />
<week weeknumber="24" quantity="8095" />
<week weeknumber="25" quantity="8332" />
<week weeknumber="26" quantity="8199" />
<week weeknumber="27" quantity="7305" />
<week weeknumber="28" quantity="7457" />
<week weeknumber="29" quantity="7078" />
<week weeknumber="3" quantity="9793" />
<week weeknumber="30" quantity="6927" />
<week weeknumber="31" quantity="7056" />
<week weeknumber="32" quantity="6935" />
<week weeknumber="33" quantity="7121" />
<week weeknumber="34" quantity="8842" />
<week weeknumber="35" quantity="6976" />
<week weeknumber="36" quantity="6554" />
<week weeknumber="37" quantity="6538" />
<week weeknumber="38" quantity="6938" />
<week weeknumber="39" quantity="6981" />
<week weeknumber="4" quantity="10861" />
<week weeknumber="40" quantity="7086" />
<week weeknumber="41" quantity="7192" />
<week weeknumber="42" quantity="8585" />
<week weeknumber="43" quantity="10442" />
<week weeknumber="44" quantity="10244" />
<week weeknumber="45" quantity="11199" />
<week weeknumber="46" quantity="11030" />
<week weeknumber="47" quantity="10851" />
<week weeknumber="48" quantity="10134" />
<week weeknumber="49" quantity="9992" />
<week weeknumber="5" quantity="12153" />
<week weeknumber="50" quantity="10140" />
<week weeknumber="51" quantity="10088" />
<week weeknumber="52" quantity="9341" />
<week weeknumber="6" quantity="9690" />
<week weeknumber="7" quantity="11471" />
<week weeknumber="8" quantity="11513" />
<week weeknumber="9" quantity="11489" />
</year>
</years>
如您所见，所有年份的数量总和都相同，但事实并非如此，它应该是每年唯一的，我该如何适当地格式化.
As you can see the quantity sum is the same for all the years, which shouldn't be the case, it should be unique for each year, how do i format it appropriately.
OP 的代码(摘自评论):
OP's code (taken from comment):
select (CAST(yearnumber as int)) as '@yearnumber'
, min(convert(varchar(10)
, cast(yearstart as date), 10)) as '@yearstart'
, max(convert(varchar(10)
, cast(yearend as date), 10)) as '@yearend'
, ( select cast(weeknumber as int) as '@weeknumber'
, SUM(isnull(cast(quantity as int),0)) as '@quantity'
FROM [meiForecasting].[dbo].george_mdx
group by weeknumber
FOR XML PATH('week'), TYPE
)
from [meiForecasting].[dbo].george_mdx
group by yearnumber for xml path('year'), root('years')
推荐答案
您没有向我们展示您是如何生成此 XML 的，但是 - 非常肯定 - 您正在以某种字符串连接方式执行此操作，或者您有您的日期作为字符串值.两者都很糟糕...
You did not show us how you are generating this XML, but - quite sure - you are doing this in some kind of string concatenation, or you have your dates as string values. Both is quite bad...
您的 XML 有一个大弱点:yearstart="11-24-13"
There is one big weakness in your XML: yearstart="11-24-13"
您应该始终避免特定于文化的日期/时间格式.更糟糕的是特定于文化的两位数年份.最糟糕的是具有语言特定部分的格式，例如1 Dec 2016.想象一个像 02-03-04 这样的日期.根据阅读系统的不同，您会从中获得许多不同的值.
You should always avoid culture specific date/time formats. Even worse is culture specific with a two digit year. The worst are formats with language specific parts like 1 Dec 2016. Imagine a date like 02-03-04. Depending on the reading system you'd get numerous different values from this.
XML 中的正确格式是 ISO8601.使用 FOR XML 会隐式地为你做这件事.
The proper format within XML is ISO8601. Using FOR XML will do this for you implicitly.
检查这个.如果它有效，您只需将 @tbl 替换为您的实际表名:
Check this. If it works, you just have to replace the @tbl with your actual table's name:
首先我创建一个模拟测试场景(你以后不需要这个)
First I create a mock-up test scenario (you don't need this later)
DECLARE @tbl TABLE(YearNumber INT,YearStart DATE,YearEnd DATE,WeekNumber INT,Quantity INT);
INSERT INTO @tbl VALUES
(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),1,1826)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),2,1225)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),3,452)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),4,276)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),5,673)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),6,1986)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),7,3806)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),8,3608)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),9,3841)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),10,3356)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),11,3436)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),12,3437)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),13,3611)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),14,3354)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),15,3743)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),16,3508)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),17,3440)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),18,3467)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),19,2816)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),20,2998)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),21,2796)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),22,2624)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),23,2458)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),24,1924)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),25,1816)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),26,1671)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),27,1111)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),28,1036)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),29,746)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),30,590)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),31,665)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),32,393)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),33,442)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),34,2042)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),35,387)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),36,24)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),37,30)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),38,44)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),39,113)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),40,23)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),41,18)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),42,1624)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),43,3760)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),44,3645)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),45,3964)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),46,3807)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),47,4048)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),48,3862)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),49,3677)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),50,3695)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),51,3871)
,(1,CONVERT(DATETIME,'11-22-15',1),CONVERT(DATETIME,'11-19-16',1),52,3686)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),1,3694)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),2,4240)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),3,4287)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),4,4751)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),5,5536)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),6,3712)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),7,3367)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),8,3598)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),9,3461)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),10,3334)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),11,3325)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),12,3313)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),13,3417)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),14,3396)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),15,3326)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),16,3274)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),17,3473)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),18,3442)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),19,3293)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),20,3114)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),21,3116)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),22,3664)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),23,3165)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),24,2896)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),25,3113)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),26,3183)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),27,2912)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),28,3043)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),29,3046)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),30,3033)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),31,3212)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),32,3294)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),33,3210)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),34,3303)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),35,3229)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),36,3313)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),37,3338)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),38,3432)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),39,3340)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),40,3597)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),41,3628)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),42,3412)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),43,3332)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),44,3313)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),45,3637)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),46,3498)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),47,3230)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),48,2591)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),49,2937)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),50,2964)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),51,2740)
,(2,CONVERT(DATETIME,'11-23-14',1),CONVERT(DATETIME,'11-21-15',1),52,1982)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),1,4607)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),2,5018)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),3,5054)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),4,5834)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),5,5944)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),6,3992)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),7,4298)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),8,4307)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),9,4187)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),10,4269)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),11,4012)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),12,4392)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),13,4536)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),14,4173)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),15,4053)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),16,3882)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),17,4053)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),18,3990)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),19,3992)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),20,3489)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),21,3797)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),22,3355)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),23,3438)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),24,3275)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),25,3403)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),26,3345)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),27,3282)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),28,3378)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),29,3286)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),30,3304)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),31,3179)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),32,3248)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),33,3469)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),34,3497)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),35,3360)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),36,3217)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),37,3170)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),38,3462)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),39,3528)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),40,3466)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),41,3546)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),42,3549)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),43,3350)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),44,3286)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),45,3598)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),46,3725)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),47,3573)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),48,3681)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),49,3378)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),50,3481)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),51,3477)
,(3,CONVERT(DATETIME,'11-24-13',1),CONVERT(DATETIME,'11-22-14',1),52,3673);
--这是实际的查询:
SELECT YearNumber AS [@yearnumber]
,YearStart AS [@yearstart]
,YearEnd AS [@yearend]
,(
SELECT x.WeekNumber AS [@weeknumber]
,x.Quantity AS [@quantity]
FROM @tbl AS X
WHERE X.YearNumber=tbl.YearNumber
FOR XML PATH('week'),TYPE
)
FROM @tbl AS tbl
GROUP BY YearNumber,YearStart,YearEnd
FOR XML PATH('year'),ROOT('years')
这就是结果
<years>
<year yearnumber="1" yearstart="2015-11-22" yearend="2016-11-19">
<week weeknumber="1" quantity="1826" />
<week weeknumber="2" quantity="1225" />
<week weeknumber="3" quantity="452" />
<week weeknumber="4" quantity="276" />
<week weeknumber="5" quantity="673" />
<week weeknumber="6" quantity="1986" />
<week weeknumber="7" quantity="3806" />
<week weeknumber="8" quantity="3608" />
<week weeknumber="9" quantity="3841" />
<week weeknumber="10" quantity="3356" />
<week weeknumber="11" quantity="3436" />
<week weeknumber="12" quantity="3437" />
<week weeknumber="13" quantity="3611" />
<week weeknumber="14" quantity="3354" />
<week weeknumber="15" quantity="3743" />
<week weeknumber="16" quantity="3508" />
<week weeknumber="17" quantity="3440" />
<week weeknumber="18" quantity="3467" />
<week weeknumber="19" quantity="2816" />
<week weeknumber="20" quantity="2998" />
<week weeknumber="21" quantity="2796" />
<week weeknumber="22" quantity="2624" />
<week weeknumber="23" quantity="2458" />
<week weeknumber="24" quantity="1924" />
<week weeknumber="25" quantity="1816" />
<week weeknumber="26" quantity="1671" />
<week weeknumber="27" quantity="1111" />
<week weeknumber="28" quantity="1036" />
<week weeknumber="29" quantity="746" />
<week weeknumber="30" quantity="590" />
<week weeknumber="31" quantity="665" />
<week weeknumber="32" quantity="393" />
<week weeknumber="33" quantity="442" />
<week weeknumber="34" quantity="2042" />
<week weeknumber="35" quantity="387" />
<week weeknumber="36" quantity="24" />
<week weeknumber="37" quantity="30" />
<week weeknumber="38" quantity="44" />
<week weeknumber="39" quantity="113" />
<week weeknumber="40" quantity="23" />
<week weeknumber="41" quantity="18" />
<week weeknumber="42" quantity="1624" />
<week weeknumber="43" quantity="3760" />
<week weeknumber="44" quantity="3645" />
<week weeknumber="45" quantity="3964" />
<week weeknumber="46" quantity="3807" />
<week weeknumber="47" quantity="4048" />
<week weeknumber="48" quantity="3862" />
<week weeknumber="49" quantity="3677" />
<week weeknumber="50" quantity="3695" />
<week weeknumber="51" quantity="3871" />
<week weeknumber="52" quantity="3686" />
</year>
<year yearnumber="2" yearstart="2014-11-23" yearend="2015-11-21">
<week weeknumber="1" quantity="3694" />
<week weeknumber="2" quantity="4240" />
<week weeknumber="3" quantity="4287" />
<week weeknumber="4" quantity="4751" />
<week weeknumber="5" quantity="5536" />
<week weeknumber="6" quantity="3712" />
<week weeknumber="7" quantity="3367" />
<week weeknumber="8" quantity="3598" />
<week weeknumber="9" quantity="3461" />
<week weeknumber="10" quantity="3334" />
<week weeknumber="11" quantity="3325" />
<week weeknumber="12" quantity="3313" />
<week weeknumber="13" quantity="3417" />
<week weeknumber="14" quantity="3396" />
<week weeknumber="15" quantity="3326" />
<week weeknumber="16" quantity="3274" />
<week weeknumber="17" quantity="3473" />
<week weeknumber="18" quantity="3442" />
<week weeknumber="19" quantity="3293" />
<week weeknumber="20" quantity="3114" />
<week weeknumber="21" quantity="3116" />
<week weeknumber="22" quantity="3664" />
<week weeknumber="23" quantity="3165" />
<week weeknumber="24" quantity="2896" />
<week weeknumber="25" quantity="3113" />
<week weeknumber="26" quantity="3183" />
<week weeknumber="27" quantity="2912" />
<week weeknumber="28" quantity="3043" />
<week weeknumber="29" quantity="3046" />
<week weeknumber="30" quantity="3033" />
<week weeknumber="31" quantity="3212" />
<week weeknumber="32" quantity="3294" />
<week weeknumber="33" quantity="3210" />
<week weeknumber="34" quantity="3303" />
<week weeknumber="35" quantity="3229" />
<week weeknumber="36" quantity="3313" />
<week weeknumber="37" quantity="3338" />
<week weeknumber="38" quantity="3432" />
<week weeknumber="39" quantity="3340" />
<week weeknumber="40" quantity="3597" />
<week weeknumber="41" quantity="3628" />
<week weeknumber="42" quantity="3412" />
<week weeknumber="43" quantity="3332" />
<week weeknumber="44" quantity="3313" />
<week weeknumber="45" quantity="3637" />
<week weeknumber="46" quantity="3498" />
<week weeknumber="47" quantity="3230" />
<week weeknumber="48" quantity="2591" />
<week weeknumber="49" quantity="2937" />
<week weeknumber="50" quantity="2964" />
<week weeknumber="51" quantity="2740" />
<week weeknumber="52" quantity="1982" />
</year>
<year yearnumber="3" yearstart="2013-11-24" yearend="2014-11-22">
<week weeknumber="1" quantity="4607" />
<week weeknumber="2" quantity="5018" />
<week weeknumber="3" quantity="5054" />
<week weeknumber="4" quantity="5834" />
<week weeknumber="5" quantity="5944" />
<week weeknumber="6" quantity="3992" />
<week weeknumber="7" quantity="4298" />
<week weeknumber="8" quantity="4307" />
<week weeknumber="9" quantity="4187" />
<week weeknumber="10" quantity="4269" />
<week weeknumber="11" quantity="4012" />
<week weeknumber="12" quantity="4392" />
<week weeknumber="13" quantity="4536" />
<week weeknumber="14" quantity="4173" />
<week weeknumber="15" quantity="4053" />
<week weeknumber="16" quantity="3882" />
<week weeknumber="17" quantity="4053" />
<week weeknumber="18" quantity="3990" />
<week weeknumber="19" quantity="3992" />
<week weeknumber="20" quantity="3489" />
<week weeknumber="21" quantity="3797" />
<week weeknumber="22" quantity="3355" />
<week weeknumber="23" quantity="3438" />
<week weeknumber="24" quantity="3275" />
<week weeknumber="25" quantity="3403" />
<week weeknumber="26" quantity="3345" />
<week weeknumber="27" quantity="3282" />
<week weeknumber="28" quantity="3378" />
<week weeknumber="29" quantity="3286" />
<week weeknumber="30" quantity="3304" />
<week weeknumber="31" quantity="3179" />
<week weeknumber="32" quantity="3248" />
<week weeknumber="33" quantity="3469" />
<week weeknumber="34" quantity="3497" />
<week weeknumber="35" quantity="3360" />
<week weeknumber="36" quantity="3217" />
<week weeknumber="37" quantity="3170" />
<week weeknumber="38" quantity="3462" />
<week weeknumber="39" quantity="3528" />
<week weeknumber="40" quantity="3466" />
<week weeknumber="41" quantity="3546" />
<week weeknumber="42" quantity="3549" />
<week weeknumber="43" quantity="3350" />
<week weeknumber="44" quantity="3286" />
<week weeknumber="45" quantity="3598" />
<week weeknumber="46" quantity="3725" />
<week weeknumber="47" quantity="3573" />
<week weeknumber="48" quantity="3681" />
<week weeknumber="49" quantity="3378" />
<week weeknumber="50" quantity="3481" />
<week weeknumber="51" quantity="3477" />
<week weeknumber="52" quantity="3673" />
</year>
</years>
这篇关于带有动态参数的 SQL Server XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
