xQuery grouping question

2023-03-01 15:18 问答作者：

here is the xml:

<?xml version="1.0" encoding="utf-8"?>
<library xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"      xsi:noNamespaceSchemaLocation="library.xsd">
<items>
<book asin="0201100886"
  created="128135928"
  lastLookupTime="128135928">
  <uuid>BA57A934-6CDC-11D9-830B-000393D3DE16</uuid>
  <title>Compilers</title>
  <authors>
    <author>Alfred V. Aho</author>
    <author>Ravi Sethi</author>
    <author>Jeffrey D. Ullman</author>
  </authors>
  <publisher>Addison Wesley</publisher>
  <published>1986-01-01</published>
  <price>102.00</price>
  <purchaseDate>2005-01-22</purchaseDate>
</book>

<book asin="0122513363" created="128135600" lastLookupTime="128136224">
  <uuid>F7468E09-6CDB-11D9-830B-000393D3DE16</uuid>
  <title>Database Driven Web Sites</title>
  <authors>
    <author>Jesse Feiler</author>
  </authors>
  <publisher>Morgan Kaufmann</publisher>
  <published>1998-04-15</published>
  <edition>Paperback</edition>
  <price>50.95</price>
  <purchaseDate>2005-01-22</purchaseDate>
  <currentValue>35.00</currentValue>
  <netRating>1.5</netRating>
  <genres>
    <genre>Computer Bks - Internet</genre>
    <genre>Computer Books: Web Programming</genre>
    <genre>Computer Networks</genre>
    <genre>Computers</genre>
    <genre>Database Management - General</genre>
    <genre>Database management</genre>
    <genre>Design</genre>
    <genre>Distributed Databases</genre>
    <genre>Information Technology</genre>
    <genre>Internet - Web Site Design</genre>
    <genre>Networking - General</genre>
    <genre>Web sites</genre>
    <genre>Computers / Computer Science</genre>
  </genres>
  <upc>608628133638</upc>
</book>

<book asin="0201441241"
  created="128136896"
  lastLookupTime="128136896">
  <uuid>FBC45DF4-6CDE-11D9-830B-000393D3DE16</uuid>
  <title>Introduction to Automata Theory, Languages, and Computation (2nd Edition)</title>
  <authors>
    <author>John E. Hopcroft</author>
    <author>Rajeev Motwani</author>
    <author>Jeffrey D. Ullman</author>
  </authors>
  <publisher>Addison Wesley</publisher>
  <published>2000-11-14</published>
  <price>108.20</price>
  <purchaseDate>2005-01-22</purchaseDate>
</book>


<book asin="0471250600"
  created="128136896"
  lastLookupTime="128136896">
  <uuid>FBC7CA56-6CDE-11D9-830B-000393D3DE16</uuid>
  <title>Operating System Concepts</title>
  <authors>
    <author>Abraham  Silberschatz</author>
    <author>Greg  Gagne</author>
    <author>Peter Baer  Galvin</author>
  </authors>
  <publisher>Wiley</publisher>
  <published>2002-03-08</published>
  <price>107.95</price>
  <purchaseDate>2005-01-22</purchaseDate>
</book>

<book asin="0321193628"
  created="128136896"
  lastLookupTime="128136896">
  <uuid>FBCB3DCF-6CDE-11D9-830B-000393D3DE16</uuid>
  <title>Concepts of Programming Languages, Sixth Edition</title>
  <authors>
    <author>Robert W. Sebesta</author>
  </authors>
  <publisher>Addison Wesley</publisher>
  <published>2003-07-24</published>
  <price>112.40</price>
  <purchaseDate>2005-01-22</purchaseDate>
</book>

<book asin="0138613370" created="128136944" lastLookupTime="128136944">
  <uuid>19E5E602-6CDF-11D9-830B-000393D3DE16</uuid>
  <title>First Course in Database Systems, A</title>
  <authors>
    <author>Jeffrey D. Ullman</author>
    <author>Jennifer Widom</author>
  </authors>
  <publisher>Prentice Hall</publisher>
  <published>1997-04-02</published>
  <edition>Hardcover</edition>
  <price>67.00</price>
  <purchaseDate>2005-01-22</purchaseDate>
  <netRating>3.2</netRating>
  <genres>
    <genre>Computer Books: Database</genre>
    <genre>Computers</genre>
    <genre>Database Engineering</genre>
    <genre>Database Management - General</genre>
    <genre>Database management</genre>
  </genres>
  <recommendations>
    <book asin="0130402648"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C60074A-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Database System Implementation</title>
      <authors>
        <author>Hector Garcia-Molina</author>
        <author>Jeffrey D. Ullman</author>
        <author>Jennifer D. Widom</author>
      </authors>
      <publisher>Prentice Hall</publisher>
      <published>1999-06-11</published>
      <price>89.00</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0130319953"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C635DB0-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Database Systems: The Complete Book</title>
      <authors>
        <author>Hector Garcia-Molina</author>
        <author>Jeffrey D. Ullman</author>
        <author>Jennifer D. Widom</author>
      </authors>
      <publisher>Prentice Hall</publisher>
      <published>2001-10-02</published>
      <price>98.00</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0201976994"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C66B7B4-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Computer Networking: A Top-Down Approach Featuring the Internet</title>
      <authors>
        <author>James F. Kurose</author>
        <autho开发者_C百科r>Keith W. Ross</author>
        <author>James Kurose</author>
        <author>Keith Ross</author>
      </authors>
      <publisher>Addison Wesley</publisher>
      <published>2002-07-17</published>
      <price>100.00</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0131433512"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C6AC88C-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Computer Networks and Internets, Fourth Edition</title>
      <authors>
        <author>Douglas E Comer</author>
        <author>Ralph E. Droms</author>
      </authors>
      <publisher>Prentice Hall</publisher>
      <published>2003-07-28</published>
      <price>100.00</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0262062178"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C6E712C-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Essentials of Programming Languages - 2nd Edition</title>
      <authors>
        <author>Daniel P. Friedman</author>
        <author>Mitchell Wand</author>
        <author>Christopher T. Haynes</author>
      </authors>
      <publisher>The MIT Press</publisher>
      <published>2001-01-29</published>
      <price>62.00</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0471250600"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C71B23E-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Operating System Concepts</title>
      <authors>
        <author>Abraham  Silberschatz</author>
        <author>Greg  Gagne</author>
        <author>Peter Baer  Galvin</author>
      </authors>
      <publisher>Wiley</publisher>
      <published>2002-03-08</published>
      <price>107.95</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0137903952"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C764AD4-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Artificial Intelligence: A Modern Approach (2nd Edition)</title>
      <authors>
        <author>Stuart J. Russell</author>
        <author>Peter Norvig</author>
      </authors>
      <publisher>Prentice Hall</publisher>
      <published>2002-12-20</published>
      <price>93.33</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="155860832X"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C898640-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Computer Networks: A Systems Approach, 3rd Edition</title>
      <authors>
        <author>Larry L. Peterson</author>
        <author>Bruce S. Davie</author>
      </authors>
      <publisher>Morgan Kaufmann</publisher>
      <published>2003-05-22</published>
      <price>89.95</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0130669474"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C8DD37A-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>SQL Fundamentals (2nd Edition)</title>
      <authors>
        <author>John J. Patrick</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2002-05-07</published>
      <price>54.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0321122267"
          created="128136952"
          lastLookupTime="128136952">
      <uuid>1C91D772-6CDF-11D9-830B-000393D3DE16</uuid>
      <title>Fundamentals of Database Systems, Fourth Edition</title>
      <authors>
        <author>Ramez Elmasri</author>
        <author>Shamkant B. Navathe</author>
      </authors>
      <publisher>Addison Wesley</publisher>
      <published>2003-07-23</published>
      <price>104.20</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
  </recommendations>
</book>

<book asin="1558604820" created="128136024" lastLookupTime="128136024">
  <uuid>F3C7B24F-6CDC-11D9-830B-000393D3DE16</uuid>
  <title>A Complete Guide to DB2 Universal Database</title>
  <authors>
    <author>D. D. Chamberlin</author>
    <author>Don Chamberlin</author>
  </authors>
  <publisher>Morgan Kaufmann</publisher>
  <published>1998-08-15</published>
  <edition>Paperback</edition>
  <price>62.95</price>
  <purchaseDate>2005-01-22</purchaseDate>
  <netRating>4.4</netRating>
  <genres>
    <genre>Computer Bks - Data Base Management</genre>
    <genre>Computer Books: Database</genre>
    <genre>Computers</genre>
    <genre>Database Management - General</genre>
    <genre>General</genre>
    <genre>IBM Database 2</genre>
    <genre>Information Storage &amp; Retrieval</genre>
    <genre>Relational Databases</genre>
    <genre>Computers / Information Storage &amp; Retrieval</genre>
  </genres>
  <recommendations>
    <book asin="0072133449"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6B35F21-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2: The Complete Reference (Complete Reference Series)</title>
      <authors>
        <author>Roman B. Melnyk</author>
        <author>Paul C. Zikopoulos</author>
      </authors>
      <publisher>McGraw-Hill Companies</publisher>
      <published>2001-10-01</published>
      <price>59.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0130661112"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6B97E54-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 UDB v8 Handbook for Windows and UNIX/Linux</title>
      <authors>
        <author>Philip K. Gunning</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2003-08-06</published>
      <price>59.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0131007726"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6BCBB88-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 SQL Procedural Language for Linux, Unix and Windows</title>
      <authors>
        <author>Paul Yip</author>
        <author>Drew Bradstock</author>
        <author>Hana Curtis</author>
        <author>Michael Gao</author>
        <author>Zamil Janmohamed</author>
        <author>Clara Liu</author>
        <author>Fraser McArthur</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2002-12-24</published>
      <price>59.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0131424653"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6C0A296-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 UDB V8.1 Certification Exam 700 Study Guide</title>
      <authors>
        <author>Roger E. Sanders</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2003-09-17</published>
      <price>49.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0764508415"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6C4058C-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 Fundamentals Certification for Dummies</title>
      <authors>
        <author>Paul C.  Zikopoulos</author>
        <author>Jennifer  Gibbs</author>
        <author>Roman B.  Melnyk</author>
      </authors>
      <publisher>For Dummies</publisher>
      <published>2001-08-01</published>
      <price>34.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0130463612"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6D9A3D8-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 Universal Database V8 for Linux, UNIX, and Windows Database Administration Certification Guide (5th Edition)</title>
      <authors>
        <author>George Baklarz</author>
        <author>Bill Wong</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2003-02-10</published>
      <price>59.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0130463884"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6DDBAB9-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>Advanced DBA Certification Guide and Reference for DB2 UDB v8 for Linux, Unix and Windows</title>
      <authors>
        <author>Dwaine R. Snow</author>
        <author>Thomas Xuan Phan</author>
        <author>Dwaine Snow</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2003-07-07</published>
      <price>59.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="155860443X"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6E1063D-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>Advanced Database Systems (The Morgan Kaufmann Series in Data Management Systems)</title>
      <authors>
        <author>Carlo Zaniolo</author>
        <author>Stefano Ceri</author>
        <author>Christos Faloutsos</author>
        <author>Richard T. Snodgrass</author>
        <author>V. S. Subrahmanian</author>
        <author>Roberto Zicari</author>
      </authors>
      <publisher>Morgan Kaufmann</publisher>
      <published>1997-05-01</published>
      <price>88.95</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0131840487"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6E441CE-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 UDB V8.1 Certification Exams 701 and 706 Study Guide</title>
      <authors>
        <author>Roger E. Sanders</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2003-12-12</published>
      <price>49.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
    <book asin="0132037955"
          created="128136024"
          lastLookupTime="128136024">
      <uuid>F6E77C2C-6CDC-11D9-830B-000393D3DE16</uuid>
      <title>DB2 High Performance Design and Tuning</title>
      <authors>
        <author>Richard Yevich</author>
        <author>Susan Lawson</author>
        <author>Richard A. Yevich</author>
      </authors>
      <publisher>Prentice Hall PTR</publisher>
      <published>2000-08-24</published>
      <price>54.99</price>
      <purchaseDate>2005-01-22</purchaseDate>
    </book>
  </recommendations>
</book>

John Doe 555-1212 Mary Jane 555-1213 Bill Jones 555-1312 Anne Marie 555-1314

here is my xQuery statement:

xquery version "1.0";
let $library := doc('library.xml')
for $borrower in $library/library/borrowers/borrower
for $name in distinct-values($borrower/name)
let $asins := data($borrower/borrowed/book/@asin)
let $book :=  $library/library/items/book[@asin = $asins]
      order by $book/published
      return
      <book>
      <name>{$name}</name>
      <title>{data($book/title)}</title>
      <date>{data($book/published)}</date>
      </book>

Here is the output:

<book>
<name> John Doe </name>
<title>Database Driven Web SitesFirst Course in Database Systems, A</title>
<date>1998-04-151997-04-02</date>
</book>
<book>
<name> Mary Jane </name>
<title>CompilersDatabase Driven Web Sites</title>
<date>1986-01-011998-04-15</date>
</book>
<book>
<name> Bill Jones </name>
<title/>
<date/>
</book>
<book>
<name> Anne Marie</name>
<title>CompilersDatabase Driven Web SitesFirst Course in Database Systems, AA           Complete Guide to DB2 Universal Database</title>
<date>1986-01-011998-04-151997-04-021998-08-15</date>
</book>

I need to list all borrowers and the book they borrowed along with the books publication date. In the query it returns each name and all their books and publish date but its not seperate. I need it to be like:

<borrowers>
<nameOfBorrower>
<book>
    <bookTitle />
    <bookPublishedDate />
</book>
  .
  .
  .
 //another book here and so on
</nameOfBorrower>
</borrower>

So i have all the information i just need to iterate over it for each person and then a book at a time instead of all books for that applicant. Please help me out.

Try something like this:

xquery version "1.0";
let $library := doc('library.xml')
for $name in distinct-values($library/library/borrowers/borrower/name)
return
  <borrower>
    <name>{$name}</name>
  {
  for $asin in $library/library/borrowers/borrower[/name = $name]/borrowed/book/@asin
  let $book :=  $library/library/items/book[@asin = $asin]
    order by $book/published
    return
    <book>
      <title>{data($book/title)}</title>
      <date>{data($book/published)}</date>
    </book>
  }
  </borrower>

Not sure if the syntax is correct, I couldnt test it.

继续阅读：xml xquery

xQuery grouping question

更多精彩内容

精彩评论

最新问答

央视是哪个频道？

请问买过的朋友，舒提啦旅行箱实际使用体验如何？？

检查不孕不育需要的费用？

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？