对粉碎的 xml 进行排名
给定以下 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
Jacob Sebastian 在他的博客文章中还提出了一个有趣的解决方案:
XQuery Lab 23 - 检索元素的值和位置
根据 Jacob 的建议,我可以将您的查询重写为:
我得到了所需的输出:
不幸的是,像
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:
and I get the desired output:
Unfortunately, none of the more obvious solutions like the
position()
orfn:id()
functions seem to a) work in SQL Server or b) be supported in SQL Server at all :-(Hope this helps
Marc
也许是这样的:你获得每个类别的第一个元素并将其用作 id。
这:
返回:
然后只是
它返回:
但它仍然领先一步。
Maybe like this: you get first element of each category and use it as an id.
This:
Returns:
And then just
It however returns:
But it is still step ahead.
您不能使用
position()
生成输出(为什么?),但您可以将其用作 XPath 过滤器:您可以使用实数表来获得更高的排名。对于单个文档中的大量类别,效率不高,但对于中等数量(数十、数百)则效果很好。
You cannot use
position()
to produce output (why??), but you can use it as XPath filters: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.
与卢卡斯的回答类似,我能够通过以下方式达到预期的结果:
它返回:
Similar to Lukasz answer, I was able achieve the desired result with:
It returns: