3

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.

  • 2
    It's not exactly clear from your question what you need, but consider the function XMLTABLE(). – mustaccio Sep 25 '15 at 20:39
  • Agreed with mustaccio. Did you check out IBM Knowledge Center? – Chris Aldrich Sep 29 '15 at 13:02
  • To the person wishing to have this question re-opened; please explain why. Thanks! – Max Vernon Nov 20 '15 at 19:04
  • 1
    It was me I have clarifications and self answer. Let me edit the question. – Justin Dearing Nov 20 '15 at 19:12
  • @MaxVernon updated with example – Justin Dearing Nov 20 '15 at 20:24
1

The answer is just to use:

QTEMP.XMLSAMPLE x,
    XMLTABLE (
        '$library/catalog/book[1]' passing x.XMLVAL as "library"
  • In 9.5 and later you can use the simpler syntax '$XMLVAL/catalog/book[1]' and skip the passing stuff – Hogan Jul 5 '17 at 21:16
  • 9.5? I think your talking about DB/2 LUW. I was asking about DB2 for the IBMi (AS/400). There are some distict syntax and feature differences, and XML is one of the places where that is true. – Justin Dearing Jul 6 '17 at 14:38
  • Yeah, This could be true -- the doc I read just said 9.5 but could be i is different. – Hogan Jul 6 '17 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.