Solved

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

Posted on 2004-08-19
7
242 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mongodb hierarchy 2 49
Changing Sitemap Frequency on XML File 3 66
VB.NET and XML parsing 6 67
XML Parsing Classic ASP 5 51
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now