I have an XML column in a table. I would like to retrieve only the first child element from the XML document in each row (i.e. the result of an XPath expression /root/someVal[1]) and return in my query its child elements as a columns in DB2 for IBM i. In Microsoft SQL Server I can do that via xmlColumn.value('some xpath', 'someType').
Given this sample data:
DECLARE GLOBAL TEMPORARY TABLE XMLSAMPLE(
ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY,
-- CONSTRAINT PK_XMLTABLE PRIMARY KEY(ID),
XMLVAL XML NOT NULL
) WITH REPLACE;
INSERT INTO QTEMP.XMLSAMPLE(XMLVAL) VALUES('<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer''s Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications
with XML.</description>
</book>
<book id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
<description>A former architect battles corporate zombies,
an evil sorceress, and her own childhood to become queen
of the world.</description>
</book>
<book id="bk103">
<author>Corets, Eva</author>
<title>Maeve Ascendant</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-11-17</publish_date>
<description>After the collapse of a nanotechnology
society in England, the young survivors lay the
foundation for a new society.</description>
</book>
<book id="bk104">
<author>Corets, Eva</author>
<title>Oberon''s Legacy</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-03-10</publish_date>
<description>In post-apocalypse England, the mysterious
agent known only as Oberon helps to create a new life
for the inhabitants of London. Sequel to Maeve
Ascendant.</description>
</book>
<book id="bk105">
<author>Corets, Eva</author>
<title>The Sundered Grail</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-09-10</publish_date>
<description>The two daughters of Maeve, half-sisters,
battle one another for control of England. Sequel to
Oberon''s Legacy.</description>
</book>
<book id="bk106">
<author>Randall, Cynthia</author>
<title>Lover Birds</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-09-02</publish_date>
<description>When Carla meets Paul at an ornithology
conference, tempers fly as feathers get ruffled.</description>
</book>
</catalog>'),
('<?xml version="1.0"?>
<catalog>
<book id="bk107">
<author>Thurman, Paula</author>
<title>Splish Splash</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-11-02</publish_date>
<description>A deep sea diver finds true love twenty
thousand leagues beneath the sea.</description>
</book>
<book id="bk108">
<author>Knorr, Stefan</author>
<title>Creepy Crawlies</title>
<genre>Horror</genre>
<price>4.95</price>
<publish_date>2000-12-06</publish_date>
<description>An anthology of horror stories about roaches,
centipedes, scorpions and other insects.</description>
</book>
<book id="bk109">
<author>Kress, Peter</author>
<title>Paradox Lost</title>
<genre>Science Fiction</genre>
<price>6.95</price>
<publish_date>2000-11-02</publish_date>
<description>After an inadvertant trip through a Heisenberg
Uncertainty Device, James Salway discovers the problems
of being quantum.</description>
</book>
<book id="bk110">
<author>O''Brien, Tim</author>
<title>Microsoft .NET: The Programming Bible</title>
<genre>Computer</genre>
<price>36.95</price>
<publish_date>2000-12-09</publish_date>
<description>Microsoft''s .NET initiative is explored in
detail in this deep programmer''s reference.</description>
</book>
<book id="bk111">
<author>O''Brien, Tim</author>
<title>MSXML3: A Comprehensive Guide</title>
<genre>Computer</genre>
<price>36.95</price>
<publish_date>2000-12-01</publish_date>
<description>The Microsoft MSXML3 parser is covered in
detail, with attention to XML DOM interfaces, XSLT processing,
SAX and more.</description>
</book>
<book id="bk112">
<author>Galos, Mike</author>
<title>Visual Studio 7: A Comprehensive Guide</title>
<genre>Computer</genre>
<price>49.95</price>
<publish_date>2001-04-16</publish_date>
<description>Microsoft Visual Studio 7 is explored in depth,
looking at how Visual Basic, Visual C++, C#, and ASP+ are
integrated into a comprehensive development
environment.</description>
</book>
</catalog>');
I don't want to do the following:
SELECT id as catalog_id, books.* FROM
QTEMP.XMLSAMPLE x,
XMLTABLE (
'$library/catalog/book' passing x.XMLVAL as "library"
COLUMNS
BOOKNO FOR ORDINALITY,
BOOK_ID CHAR(5) PATH '@id',
AUTHOR VARCHAR(100) PATH 'author',
TITLE VARCHAR(100) PATH 'title',
GENRE VARCHAR(25) PATH 'genre',
PRICE DECIMAL(7,2) PATH 'price',
PUBLISH_DATE DATE PATH 'publish_date',
DESCRIPTION VARCHAR(1000) PATH 'description'
) books
Because that will return this:
1 1 bk101 Gambardella, Matthew XML Developer's Guide Computer 44.95 2000-10-01
1 2 bk102 Ralls, Kim Midnight Rain Fantasy 5.95 2000-12-16
1 3 bk103 Corets, Eva Maeve Ascendant Fantasy 5.95 2000-11-17
1 4 bk104 Corets, Eva Oberon's Legacy Fantasy 5.95 2001-03-10
1 5 bk105 Corets, Eva The Sundered Grail Fantasy 5.95 2001-09-10
1 6 bk106 Randall, Cynthia Lover Birds Romance 4.95 2000-09-02
2 1 bk107 Thurman, Paula Splish Splash Romance 4.95 2000-11-02
2 2 bk108 Knorr, Stefan Creepy Crawlies Horror 4.95 2000-12-06
2 3 bk109 Kress, Peter Paradox Lost Science Fiction 6.95 2000-11-02
2 4 bk110 O'Brien, Tim Microsoft .NET: The Programming Bible Computer 36.95 2000-12-09
2 5 bk111 O'Brien, Tim MSXML3: A Comprehensive Guide Computer 36.95 2000-12-01
2 6 bk112 Galos, Mike Visual Studio 7: A Comprehensive Guide Computer 49.95 2001-04-16
I just want it to return the following:
1 1 bk101 Gambardella, Matthew XML Developer's Guide Computer 44.95 2000-10-01
2 1 bk107 Thurman, Paula Splish Splash Romance 4.95 2000-11-02
One row per XML column.
Best Answer
The answer is just to use: