如何从XML工件中找到任何数据的计数。 [英] How can I found the count of any data from XML artifact.

问题描述

< myagreement>

< status> n

< pkey> 2yk0000000056f3b

< hkey> 2yk0000000056f3b

< businessmodified> 2017-04-06 09:04:51

< myactive> 1





上面提到的是XML工件的一部分。这个工件中有很多MyAgreement pkeys

是否有任何sql函数或任何方法来获取总计数的计数使用SQL查询的MyAgreement的pkeys。

请尽快回复。

提前致谢!!

<myagreement>
<status>n
<pkey>2yk0000000056f3b
<hkey>2yk0000000056f3b
<businessmodified>2017-04-06 09:04:51
<myactive>1


The above mentioned is a part of an XML artifact.There are so many MyAgreement pkeys in this artifact
Is there any sql function or any way to get the count of the total count of pkeys of MyAgreement using SQL Query.
Please reply ASAP.
Thanks in advance!!

<MyAgreement>
          <Status>n</Status>
          <Au>000100U1</Au>
          <PKey>2yk0000000056f3a</PKey>
          <HKey>2yk0000000056f3a</HKey>
          <BusinessModified>2012-04-06 19:04:51</BusinessModified>
          <MyActive>1</MyActive>
       
      </MyAgreement>
      <MyAgreement>
          <Status>n</Status>
          <Au>000100U1</Au>
          <PKey>2yk0000000056f3b</PKey>
          <HKey>2yk0000000056f3b</HKey>
          <BusinessModified>2012-04-06 19:04:51</BusinessModified>
          <MyActive>1</MyActive>
       
      </MyAgreement>

XMl工件就像这样。它包含很多MyAgreement Pkeys。我只是想用sql查询找到这些pkeys的数量。有什么办法吗？



我尝试了什么：



我尝试了substring函数，charindex函数。

XMl artifact is like this.It contain a lot of MyAgreement Pkeys.I just want to find the count of these pkeys using sql query.Is there any way?

What I have tried:

I tried substring function,charindex function.

推荐答案

我将假设您正在向列中插入有效的XML而不是您到目前为止向我们展示的内容。



我创建了一个这样的示例表：

I'm going to assume that you are inserting valid XML into the column and not what you have shown us so far.

I created a sample table like this:

CREATE TABLE test (id int identity(1,1), xmlcol XML)
insert into test values
('<Agreements>
<MyAgreement>
          <Status>n</Status>
          <Au>000100U1</Au>
          <PKey>2yk0000000056f3a</PKey>
          <HKey>2yk0000000056f3a</HKey>
          <BusinessModified>2012-04-06 19:04:51</BusinessModified>
          <MyActive>1</MyActive>
       
      </MyAgreement>
      <MyAgreement>
          <Status>n</Status>
          <Au>000100U1</Au>
          <PKey>2yk0000000056f3b</PKey>
          <HKey>2yk0000000056f3b</HKey>
          <BusinessModified>2012-04-06 19:04:51</BusinessModified>
          <MyActive>1</MyActive>
       
      </MyAgreement>
</Agreements>
'),
('<Agreements>
<MyAgreement>
          <Status>n</Status>
          <Au>000100U1</Au>
          <PKey>4yk0000000056f3a</PKey>
          <HKey>4yk0000000056f3a</HKey>
          <BusinessModified>2012-04-07 19:04:51</BusinessModified>
          <MyActive>1</MyActive>
       
      </MyAgreement>
      <MyAgreement>
          <Status>n</Status>
          <Au>000100U1</Au>
          <PKey>4yk0000000056f3b</PKey>
          <HKey>4yk0000000056f3b</HKey>
          <BusinessModified>2012-04-07 19:04:51</BusinessModified>
          <MyActive>1</MyActive>
       
      </MyAgreement>
</Agreements>
')

请注意，我是一个ssuming根节点到XML 协议 - 在查询中更改它是否错误。



你可以获得此查询列出的所有 PKey 节点：

Note that I'm assuming the root node to the XML is Agreements - change that in the query if it is wrong.

You can get all of the PKey nodes listed with this query:

select id,
n.x.value('PKey[1]', 'VARCHAR(25)') AS [pkey]
FROM test
CROSS APPLY xmlcol.nodes('/Agreements/MyAgreement') n(x)

所以得到 PKey 的节点将其放入子查询中：

So to get the count of PKey nodes put that into a sub-query thus:

select id, count(*) from
(
	select id,
	n.x.value('PKey[1]', 'VARCHAR(25)') AS [pkey]
	FROM test
	CROSS APPLY xmlcol.nodes('/Agreements/MyAgreement') n(x)
) as q
group by id

SQL Server中有许多关于查询XML列的文章，其中之一是： XML Workshop [ ^ ]

这篇关于如何从XML工件中找到任何数据的计数。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！