Parsing XML in IDS
We are using IDS 11.50 on AIX and have a table that is defined as follows:
CREATE TABLE xpath_test (
id SMALLINT NOT NULL,
xml_string clob
);
The id column is the PK. The other column contains the xml document.
id 1
xml_string
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="myfile.xsl" ?>
<bookstore specialty="novel">
<book style="autobiography">
<author>
<first-name>Joe</first-name>
<last-name>Bob</last-name>
<award>Trenton Literary Review Honorable Mention</award>
</author>
<price>12</price>
</book>
<book style="textbook">
<author>
<first-name>Mary</first-name>
<last-name>Bob</last-name>
<publication>Selected Short Stories of
<first-name>Mary</first-name>
<last-name>Bob</last-name>
</publication>
</author>
<editor>
<first-name>Britney</first-name>
<last-name>Bob</last-name>
</editor>
<price>55</price>
</book>
<magazine style="glossy" frequency="monthly">
<price>2.50</price>
<subscription price="24" per="year"/>
</magazine>
<book style="novel" id="myfave">
<author>
<first-name>Toni</first-name>
<last-name>Bob</last-name>
<degree from="Trenton U">B.A.</degree>
<degree from="Harvard">Ph.D.</degree>
<award>Pulitzer</award>
<publication>Still in Trenton</publication>
<publication>Trenton Forever</publication>
</author>
<price intl="Canada" exchange="0.7">6.50</price>
<excerpt>
<p>It was a dark and stormy night.</p>
<p>But then all nights in Trenton seem dark and
stormy to someone who has gone through what
<emph>I</emph> have.</p>
<definition-list>
<term>Trenton</term>
<definition>misery</definition>
</definition-list>
</excerpt>
</book>
<my:book xmlns:my="uri:mynamespace" style="leather" price="29.50">
<my:title>Who's Who in Trenton</my:title>
<my:author>Robert Bob</my:author>
</my:book>
</bookstore>
So for example, what we want to be able to do is return all values for a
particular tag/node and trying to use the extractvalue function to return the
value of the XML node, but it does not seem to work.
Has anybody else had luck with doing this? If so, can you provide an example
of what you did tailored to this XML listed above so that I may try to
duplicate it? I currently have a PMR open with IBM for assistance and
clarification but thought I would throw it out on the user group.
Example query - select extractvalue(xml_string,'//author/first-name') from
xpath_test where id=1;
Thanks,
Joe Plugge