How can I found the count of any data from XML artifact.

Question

1.00/5 (2 votes)

See more:

<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!!

[EDIT from CHill60 - OP code from a "solution"]

HTML

<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 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.

Posted 10-Apr-17 2:18am

Member 12965461

Updated 10-Apr-17 5:45am

CHill60

v2

Add a Solution

Comments

CHill60 10-Apr-17 9:10am

That's not valid XML so at the moment there is no easy way to do what you want. If you have a better example of what you are actually putting in the column we might be able to help

Member 12965461 10-Apr-17 10:46am

<myagreement>
<status>n
<au>000100U1
<pkey>2yk0000000056f3a
<hkey>2yk0000000056f3a
<businessmodified>2012-04-06 19:04:51
<myactive>1

<myagreement>
<status>n
<au>000100U1
<pkey>2yk0000000056f3b
<hkey>2yk0000000056f3b
<businessmodified>2012-04-06 19:04:51
<myactive>1

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

CHill60 10-Apr-17 11:14am

That is still not valid XML. What is the root element called?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-04-10T05:45:00

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:

SQL

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>
')

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:

SQL

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

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

SQL

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

There are a number of articles available on querying XML columns in SQL Server, here is one of them: XML Workshop[^]