对粉碎的 xml 进行排名

发布于 2024-08-06 19:08:26 字数 791 浏览 3 评论 0原文

给定以下 XML 示例以及将 xml 分解为关系的 select 语句,我需要的是 select 的第二列作为以下的序数类别(在本例中,1 表示方向,2 表示颜色)。

注意:选择中的文字值“rank()”保留为占位符。我正在尝试使用rank,但没有成功。

declare @x xml
set @x = '
    <root>
        <category>
            <item value="north"/>
            <item value="south"/>
            <item value="east"/>
            <item value="west"/>
        </category>
        <category>
            <item value="red"/>
            <item value="green"/>
            <item value="blue"/>
        </category>
    </root>'

select c.value('./@value', 'varchar(10)') as "ItemValue", 
       'rank()' as "CategoryNumber"
from @x.nodes('//item') as t(c)

Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case).

Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with using the rank, but with no success.

declare @x xml
set @x = '
    <root>
        <category>
            <item value="north"/>
            <item value="south"/>
            <item value="east"/>
            <item value="west"/>
        </category>
        <category>
            <item value="red"/>
            <item value="green"/>
            <item value="blue"/>
        </category>
    </root>'

select c.value('./@value', 'varchar(10)') as "ItemValue", 
       'rank()' as "CategoryNumber"
from @x.nodes('//item') as t(c)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

因为看清所以看轻 2024-08-13 19:08:26

Jacob Sebastian 在他的博客文章中还提出了一个有趣的解决方案:

XQuery Lab 23 - 检索元素的值和位置

根据 Jacob 的建议,我可以将您的查询重写为:

SELECT
    x.value('@value','VARCHAR(10)') AS 'ItemValue',        
    p.number as 'CategoryNumber'
FROM
    master..spt_values p
CROSS APPLY 
    @x.nodes('/root/category[position()=sql:column("number")]/item') n(x) 
WHERE
    p.type = 'p'

我得到了所需的输出:

ItemValue   CategoryNumber
---------   --------------
north           1
south           1
east            1
west            1
red             2
green           2
blue            2

不幸的是,像 position()fn:id() 函数这样更明显的解决方案似乎都 a) 在 SQL Server 中工作或 b) 在 SQL Server 中根本不受支持:-(

希望这对

马克有帮助

Jacob Sebastian also has an interesting solution presented in his blog post:

XQuery Lab 23 - Retrieving values and position of elements

With Jacob's suggestion, I can rewrite your query to be:

SELECT
    x.value('@value','VARCHAR(10)') AS 'ItemValue',        
    p.number as 'CategoryNumber'
FROM
    master..spt_values p
CROSS APPLY 
    @x.nodes('/root/category[position()=sql:column("number")]/item') n(x) 
WHERE
    p.type = 'p'

and I get the desired output:

ItemValue   CategoryNumber
---------   --------------
north           1
south           1
east            1
west            1
red             2
green           2
blue            2

Unfortunately, none of the more obvious solutions like the position() or fn:id() functions seem to a) work in SQL Server or b) be supported in SQL Server at all :-(

Hope this helps

Marc

入怼 2024-08-13 19:08:26

也许是这样的:你获得每个类别的第一个元素并将其用作 id。

这:

select c.value('./@value', 'varchar(10)') as "ItemValue", 
    c.value('../item[1]/@value', 'varchar(10)') as "CategoryNumber"
from @x.nodes('//item') as t(c)

返回:

Item Value | CategoryNumber
---------------------------
north      | north
south      | north
east       | north
west       | north
red        | red
green      | red
blue       | red

然后只是

select c.value('./@value', 'varchar(10)') as "ItemValue", 
   RANK() OVER (ORDER BY c.value('../item[1]/@value', 'varchar(10)')) as "CategoryNumber"
from @x.nodes('//item') as t(c)

它返回:

Item Value | CategoryNumber
---------------------------
north      | 1
south      | 1
east       | 1
west       | 1
red        | 5
green      | 5
blue       | 5

但它仍然领先一步。

Maybe like this: you get first element of each category and use it as an id.

This:

select c.value('./@value', 'varchar(10)') as "ItemValue", 
    c.value('../item[1]/@value', 'varchar(10)') as "CategoryNumber"
from @x.nodes('//item') as t(c)

Returns:

Item Value | CategoryNumber
---------------------------
north      | north
south      | north
east       | north
west       | north
red        | red
green      | red
blue       | red

And then just

select c.value('./@value', 'varchar(10)') as "ItemValue", 
   RANK() OVER (ORDER BY c.value('../item[1]/@value', 'varchar(10)')) as "CategoryNumber"
from @x.nodes('//item') as t(c)

It however returns:

Item Value | CategoryNumber
---------------------------
north      | 1
south      | 1
east       | 1
west       | 1
red        | 5
green      | 5
blue       | 5

But it is still step ahead.

來不及說愛妳 2024-08-13 19:08:26

您不能使用 position() 生成输出(为什么?),但您可以将其用作 XPath 过滤器:

 with numbers (n) as (
  select 1
  union all select 2
  union all select 3
  union all select 4
  union all select 5)
 select i.x.value('@value', 'varchar(10)') as [ItemValue],
    n.n as [rank]
  from numbers n
  cross apply @x.nodes('/root/category[position()=sql:column("n.n")]') as c(x)
  cross apply c.x.nodes('item') as i(x);

您可以使用实数表来获得更高的排名。对于单个文档中的大量类别,效率不高,但对于中等数量(数十、数百)则效果很好。

You cannot use position() to produce output (why??), but you can use it as XPath filters:

 with numbers (n) as (
  select 1
  union all select 2
  union all select 3
  union all select 4
  union all select 5)
 select i.x.value('@value', 'varchar(10)') as [ItemValue],
    n.n as [rank]
  from numbers n
  cross apply @x.nodes('/root/category[position()=sql:column("n.n")]') as c(x)
  cross apply c.x.nodes('item') as i(x);

You can use a real numbers table for higher ranks. For very large number of categories in a single document is not going to be efficient, but for moderate numbers (tens, hundreds) will work just fine.

一花一树开 2024-08-13 19:08:26

与卢卡斯的回答类似,我能够通过以下方式达到预期的结果:

SELECT
  I.Item_Instance.value('@value','VARCHAR(10)') AS Item_Value,
  DENSE_RANK() OVER (ORDER BY C.Category_Instance) AS Category_Ordinal  
FROM @x.nodes('/root') AS R(Root_Instance)
CROSS APPLY R.Root_Instance.nodes('category') AS C(Category_Instance)
CROSS APPLY C.Category_Instance.nodes('item') AS I(Item_Instance);

它返回:

Item_Value Category_Ordinal
---------- --------------------
north      1
south      1
east       1
west       1
red        2
green      2
blue       2

Similar to Lukasz answer, I was able achieve the desired result with:

SELECT
  I.Item_Instance.value('@value','VARCHAR(10)') AS Item_Value,
  DENSE_RANK() OVER (ORDER BY C.Category_Instance) AS Category_Ordinal  
FROM @x.nodes('/root') AS R(Root_Instance)
CROSS APPLY R.Root_Instance.nodes('category') AS C(Category_Instance)
CROSS APPLY C.Category_Instance.nodes('item') AS I(Item_Instance);

It returns:

Item_Value Category_Ordinal
---------- --------------------
north      1
south      1
east       1
west       1
red        2
green      2
blue       2
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文