How can I count XML nodes that start with a certain string using XQuery?

Question

I have the following XML:

<prescriptions>
  <prescription id="1" amka="1">
    <status>closed</status>
    <doctor_info>
      <amka>111111</amka>
      <doc_code> 123</doc_code>
      <name> chris</name>
      <surname>st</surname>
      <specialization>path</specialization>
      <hospital>401</hospital>
    </doctor_info>
    <patient_info>
      <amka>1</amka>
      <genre>man</genre>
      <name>elton </name>
      <surname>kev</surname>
      <age>28</age>
      <adress>aaa123</adress>
      <home_phone>210</home_phone>
      <cell_phone>69</cell_phone>
      <email>[email protected]</email>
      <insurance>diom</insurance>
    </patient_info>
    <main_prescription>
      <type>eee</type>
      <epanalipseis>2</epanalipseis>
      <date>21/1/2012</date>
      <comms>blablabla</comms>
      <diagnosh>kkkk</diagnosh>
      <drug1>
        <onoma>prezza</onoma>
        <drastikh_ousia>llll</drastikh_ousia>
        <posotita>12</posotita>
        <dosologia>
          <arithmos_hapion>5</arithmos_hapion>
          <ores>2</ores>
          <hmeres>21</hmeres>
        </dosologia>
        <simetoxi>25%</simetoxi>
        <tropos_xorigisis>ass</tropos_xorigisis>
        <cost>100</cost>
      </drug1>
      <drug2>
        <onoma>prezza</onoma>
        <drastikh_ousia>llll</drastikh_ousia>
        <posotita>12</posotita>
        <dosologia>
          <arithmos_hapion>5</arithmos_hapion>
          <ores>2</ores>
          <hmeres>21</hmeres>
        </dosologia>
        <simetoxi>25%</simetoxi>
        <tropos_xorigisis>ass</tropos_xorigisis>
        <cost>100</cost>
      </drug2>
      <drug3>
        <onoma>prezza</onoma>
        <drastikh_ousia>llll</drastikh_ousia>
        <posotita>12</posotita>
        <dosologia>
          <arithmos_hapion>5</arithmos_hapion>
          <ores>2</ores>
          <hmeres>21</hmeres>
        </dosologia>
        <simetoxi>25%</simetoxi>
        <tropos_xorigisis>ass</tropos_xorigisis>
        <cost>100</cost>
      </drug3>
      <total_cost>100</total_cost>
      <prospou>nosokomeio</prospou>
      <drug_comms>ablablabla</drug_comms>
    </main_prescription>
  </prescription>
</prescriptions>

In <main_prescription> node I have 3 nodes for drugs: <drug1> , <drug2> and <drug3>.

Is there a way to count how many nodes name <drug...> are there using XQuery?

I am using the XQuery below but it is wrong:

select prescriptions.query('count(/prescriptions/prescription/main_prescription/drug[*])') as one1 from prescription

Dimitre Novatchev · Accepted Answer · 2012-01-25 13:38:03Z

2

The wanted nodes can be selected even with an XPath 1.0 expression:

/*/*/main_prescription
      /*[starts-with(name(), 'drug')
       and
          substring-after(name(), 'drug')
         =
          floor(substring-after(name(), 'drug'))
        ]

This selects all elements that:

Are children of a main_prescription element that is a grand-child of the top element of the document and:
Have a name starting with the string "drug" and the remaining string after "drug" is an integer.

Explanation:

Proper use of the functions starts-with(), name() and substring-after().
Use of the fact that the expression floor($x) = $x evaluates to true() exactly when the string value of $x represents an integer.

answered Jan 25, 2012 at 13:38

Dimitre Novatchev

244k27 gold badges308 silver badges438 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jayy Over a year ago

Hi, Will floor function accepts string as argument. What happens if it encounters string as an argument.

Dimitre Novatchev Over a year ago

@KaipaMSarma: In XPath 2.0, which is strongly typed, passing a non-numeric argument to floor() must result in error. In XPath 1.0, which is very weakly typed, floor('3.5') returns the number 3. floor('Hi') returns NaN. That is, the argument is converted to a number and then the function is applied.

Jayy Over a year ago

ok.. got it now. I was wondering why it is throwing error for me.

Dimitre Novatchev Over a year ago

@KaipaMSarma: in XPath 2.0 you must use explicit xs:integer() to convert the substring into an integer.

Jayy · Accepted Answer · 2012-01-25 14:08:50Z

0

Try this.

'count(/prescriptions/prescription/main_prescription/*[matches(name(), ''^drug[0-9]+$'')])

Here we are selecting all nodes which are having node names as 'drug' and ending with an integer.

You can explore more on xpath functions on the below link. http://www.w3schools.com/Xpath/xpath_functions.asp

edited Jan 25, 2012 at 14:08

answered Jan 25, 2012 at 9:47

Jayy

2,4464 gold badges25 silver badges35 bronze badges

3 Comments

stampoltaschris Over a year ago

Seems that SQL Server 2008 does not agree with the syntax giving the result: Incorrect syntax near '0-9'.

Jayy Over a year ago

you have to use escape character. I have modified the answer

Dimitre Novatchev Over a year ago

@KaipaMSarma: I think that this expression doesn't select elements such as drug99. A correct Regex is: ^drug[0-9]+$

Collectives™ on Stack Overflow

How can I count XML nodes that start with a certain string using XQuery?

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related