3

I have the following XML document loaded in MarkLogic database:

<x:books xmlns:x="urn:books">
  <book id="bk001">
    <author>Writer</author>
    <title>The First Book</title>
    <genre>Fiction</genre>
    <price>44.95</price>
    <pub_date>2000-10-01</pub_date>
    <review>An amazing story of nothing.</review>
  </book>
  <book id="bk002">
    <author>Poet</author>
    <title>The Poet's First Poem</title>
    <genre>Poem</genre>
    <price>24.95</price>
    <review>Least poetic poems.</review>
  </book>
</x:books>

I am new to XQuery. How would I retrieve the values from the XML document as I retrieve it from a SQL database?

Output:

BookID | Author | Title | Genre | price | pub_date | review
bk001 | Writer | The First Book | Fiction | 44.95 | 2000-10-01
bk002 | Poet | The Poet's First Poem | Poem | 24.95 | Least poetic poems.

Note: Not necessary a pipe delimited but some collection list.

Can some one share some link or help me write this XQuery? I am new to this.

Dave Cassel
  • 8,352
  • 20
  • 38
happybayes
  • 321
  • 4
  • 12
  • Note that different items (books) should typically be in separate documents. If you have a document with a books root and a lot of book elements underneath that, the search and indexing capabilities won't work as well. [MarkLogic data modeling guidelines](http://developer.marklogic.com/learn/data-modeling) would be useful reading. – Dave Cassel Apr 12 '16 at 19:40

2 Answers2

4

XQuery's sequence construct will hold multiple values, but it's not hierarchical - so if you create a sequence of sequences, it will simply join them all together into one large sequence.

This will capture all child element and attribute values into a sequence, but because of the property of sequences I just mentioned, there would be no built in way to get the first value of the second book. You would have to know that it's the 7th item. And that the first value of a third book would be the 14th item, and so on:

$books/book/(*|@*)/string()

Just to demonstrate how you would achieve a pipe delimited list:

string-join($books/book[1]/(*|@*)/node-name() ! string(), ' | '), (: Create header row :)
for $book in $books/book
return string-join($book/(*|@*)/string(), ' | ')
wst
  • 11,681
  • 1
  • 24
  • 39
  • please find my answer. But I am getting the values in a row, wondering id I can get it joined. I couldn't get how the string-join works. Can you help me modify the code to fix that. – happybayes Apr 11 '16 at 20:25
  • Your question didn't specify how you selected that XML sample so I just used `$books` as a placeholder. You would need to update that to reflect how it's stored in your database. It's also unclear to me from your comment what you mean by join. – wst Apr 11 '16 at 20:43
  • 1
    @wst's answer works in MarkLogic if $books is set up correctly. Specifically, it should be something like `declare variable $books := fn:doc("bookstore.xml")/bookstore` – Dave Cassel Apr 13 '16 at 11:10
1

@wst: Thank you so much. For some reason i couldn't run the same in marklogic. May be it works for generic XML Xquery. But I found the following solution 

for $x at $i in doc("bookstore.xml")/bookstore/book
return data($x)
happybayes
  • 321
  • 4
  • 12