从XML列中提取特定值
从XML列中提取特定值
提问于 2012-11-28 15:59:26
我有以下sql查询：
select
I.N.value('@DRN', 'varchar(50)') as DRN,
I.N.value('@CR_GUID', 'varchar(50)') AS CR,
I.N.value('@DR_GUID', 'varchar(50)') AS DR,
I.N.value('@AMOUNT', 'money') as Amount,
I.N.value('@DATE', 'Date') as ProDate
from Table as T
cross apply T.column.nodes('/ITEMS/ITEM') as I(N)目前，它将带回所有内容，例如：
DRN CR DR Amount Prodate
12 2p1rf 684pb 4686.23 2012-11-14
12 586io fdc25 4686.23 2012-11-16
13 hnv7p 19i6f 1800.00 2012-11-20
14 cd7k0 9s2vc 570.50 2012-11-20
15 pm78s qw3d5 8500.00 2012-11-23如何从结果中排除以下内容，这些内容来自xml列的同一行？
DRN CR DR Amount Prodate
*12* 2p1rf 684pb 4686.23 2012-11-14
*12* 586io fdc25 4686.23 2012-11-16我只想让查询返回xml列中只有一项的行，而不是多个值。
谢谢
然后，我如何开始只返回一行中的第一个具有多个项目的项目：
DRN CR DR Amount Prodate
*12* 2p1rf 684pb 4686.23 2012-11-14
*12* 586io fdc25 4686.23 2012-11-16所以我只想查看日期为2012-11-14的项目，而不是与该行相关的所有内容？我希望这是有意义的？
回答 1
Stack Overflow用户
发布于 2012-11-28 16:03:55
您应该能够进行内联聚合并对其进行过滤
SELECT
X.DRN, X.CR, X.DR, X.Amount, X.ProDate
FROM
(
SELECT
COUNT(*) OVER (PARTITION BY I.N.value('@DRN', 'varchar(50)')) AS CountPerDRN,
I.N.value('@DRN', 'varchar(50)') as DRN,
I.N.value('@CR_GUID', 'varchar(50)') AS CR,
I.N.value('@DR_GUID', 'varchar(50)') AS DR,
I.N.value('@AMOUNT', 'money') as Amount,
I.N.value('@DATE', 'Date') as ProDate
from
MyTable as T
cross apply
T.Mycolumn.nodes('/ITEMS/ITEM') as I(N)
) X
WHERE
X.CountPerDRN= 1编辑，添加问题后
只需将内联聚合更改为窗口函数
在本例中，我选择了基于最低ProDate的行
SELECT
X.DRN, X.CR, X.DR, X.Amount, X.ProDate
FROM
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY I.N.value('@DRN', 'varchar(50)')
ORDER BY I.N.value('@DATE', 'Date')
) AS rnPerDRN,
I.N.value('@DRN', 'varchar(50)') as DRN,
I.N.value('@CR_GUID', 'varchar(50)') AS CR,
I.N.value('@DR_GUID', 'varchar(50)') AS DR,
I.N.value('@AMOUNT', 'money') as Amount,
I.N.value('@DATE', 'Date') as ProDate
from
MyTable as T
cross apply
T.Mycolumn.nodes('/ITEMS/ITEM') as I(N)
) X
WHERE
X.rnPerDRN = 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13600410
复制相关文章
相似问题
腾讯云开发者
