Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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

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
bilpar
Asked:
bilpar
  • 4
  • 3
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
bilparAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bilparAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
bilparAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
Fair enough, shame we couldn't get it working the way you wanted though :o)

Good luck
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now