主页 / dba / 问题 / 116146
Accepted
Justin Dearing
Asked: 2015-09-26 06:44:42 +0800 CST

如何在 DB2 for i 上对 XML 列执行 XPath 查询？

  • 772

我在表中有一个 XML 列。我只想从 XML 文档的每一行中检索第一个子元素（即 XPath 表达式的结果/root/someVal[1]），并在我的查询中将其子元素作为 DB2 for IBM i 中的列返回。在 Microsoft SQL Server 中，我可以通过xmlColumn.value('some xpath', 'someType').

鉴于此示例数据：

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>');

我不想做以下事情：

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

因为这将返回：

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

我只希望它返回以下内容：

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

每个 XML 列一行。

db2 iseries

1 个回答

  1. Best Answer

    答案就是使用：

    QTEMP.XMLSAMPLE x,
    XMLTABLE (
        '$library/catalog/book[1]' passing x.XMLVAL as "library"
    • 1

相关问题