Solved

Using an XPATH query how can I extract one node of many that are ordered arbitrarily?

Posted on 2004-08-19
7
244 Views
Last Modified: 2012-06-27
Hello.  I would like to get to the FirstName of the first contact where Type = "Reporter".  The order of the contacts may or may not be in order by item number.  

Given this first example XML, I would like to extract "Bill" because it is Type = "Reporter" and has the smallest Item value.  I can extract with this Oracle PL/SQL code: //Contacts/Contact[1]/FirstName/text().  Unfortunately, i can not count on the Item = 1 always being first nor can i count on the first Reporter node being the lowest numbered item.

<Contacts>
<Contact>
  <Item>1</Item><Type>Reporter</Type><FirstName>Bill</FirstName>
</Contact>
<Contact>
  <Item>2</Item><Type>Contact</Type><FirstName>Sharon</FirstName>
</Contact>
<Contact>
  <Item>3</Item><Type>Reporter</Type><FirstName>Kurt</FirstName>
</Contact>
</Contacts>


Given this next example XML, I would like to extract "Kurt" as it is Type = "Reporter" and has the smallest Item value:

<Contacts>
<Contact>
  <Item>1</Item><Type>Contact</Type><FirstName>Sharon</FirstName>
</Contact>
<Contact>
  <Item>3</Item><Type>Reporter</Type><FirstName>Bill</FirstName>
</Contact>
<Contact>
  <Item>2</Item><Type>Reporter</Type><FirstName>Kurt</FirstName>
</Contact>
</Contacts>

Given this next example XML, I would like to extract "Bill" as it is Type = "Reporter" and has the smallest Item value:

<Contacts>
<Contact>
  <Item>2</Item><Type>Contact</Type><FirstName>Sharon</FirstName>
</Contact>
<Contact>
  <Item>1</Item><Type>Reporter</Type><FirstName>Bill</FirstName>
</Contact>
<Contact>
  <Item>3</Item><Type>Reporter</Type><FirstName>Kurt</FirstName>
</Contact>
</Contacts>

Also, if you could recommend a book or website on XPath that would be appreciated too.  Thanks in advance for your help.

Bil
0
Comment
Question by:bilpar
  • 4
  • 3
7 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 11843178
Try this:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

      <xsl:output method="html" encoding="ISO-8859-1" />

      <xsl:template match="Contacts">
            <xsl:for-each select="Contact[Type='Reporter']">
                  <xsl:sort select="Item" order="ascending" data-type="number" />
                  <xsl:if test="position()=1">
                        <xsl:value-of select="FirstName" />
                  </xsl:if>
            </xsl:for-each>
      </xsl:template>

</xsl:stylesheet>

Hope this helps.
0
 

Author Comment

by:bilpar
ID: 11843329
Hi Carl,

Thanks for your help.  Right now, I am hoping to get away with not creating an XSLT document for this problem.  If at all possible i would like to do this in one line of code.  The good news is that I can use your example for another problem that I have... just have not gotten around to that one yet.

What i have right now, which is in PL/SQL, looks like this:

    --** convert clob into sys.xmltype... so we can extract...
    select sys.xmltype.createXml(p_xmldoc) into l_xmldoc from dual;
                                                               
    --** extract information from xmldoc required for rcpt_t.ins_                                                                
    select
      l_xmldoc.extract('//Contacts/Contact[1]/FirstName/text()').getstringval(),
    into
      p_rcpt_data.rptr_first_name,
    from
      dual;


Thanks,

Bill
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11843539
Ok, then how about this:

l_xmldoc.extract('//Contact[Type="Reporter"][child::Item < preceding-sibling::Contact/Item or child::Item < following-sibling::Contact/Item]/FirstName')

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:bilpar
ID: 11843976
Hi Carl,

Cool.  This is way advanced for me... and unfortunately, I was not able to get it to work.  Does this required that the contacts be ordered by item number?  I am going to work on solving this problem for another hour or so... but, i think that i have come up with an acceptable alternative.  If I go the alternate route I plan to accept your first answer... I can use that later.

Thanks again,

Bil
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844218
Do you know which XML parser you are using ?  It works OK with the Microsoft parser, but unfortunatley not all parsers work the same.
0
 

Author Comment

by:bilpar
ID: 11844522
That's an interesting question.  I am guessing that Oracle does not use the Microsoft parser... Since I am testing this using .NET prior to putting it into my Oracle stored procedure i will have to keep that in mind.  

Just so you know, it does work sometimes... but, depending on the order of the contacts i may get an incorrect result...

I do appreciate your help... but, i am going to let this problem go... i have decided to attack this from another direction.

Thanks,

Bil
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844570
Fair enough, shame we couldn't get it working the way you wanted though :o)

Good luck
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question