Load XML data with repeating nodes into tables in

Question

I have following XML data (just an example, the real one is huge, I can not post here) which I want to insert into a table in Oracle 11g:

<?xml version="1.0" encoding="ISO-8859-1"?>

<bookstore>

<book category="COOKING">
  <title lang="en">Everyday Italian</title>
  <author>Giada De Laurentiis</author>
  <year>2005</year>
  <price>30.00</price>
</book>

<book category="CHILDREN">
  <title lang="en">Harry Potter</title>
  <author>J K. Rowling</author>
  <year>2005</year>
  <price>29.99</price>
</book>

<book category="WEB">
  <title lang="en">XQuery Kick Start</title>
  <author>James McGovern</author>
  <author>Per Bothner</author>
  <author>Kurt Cagle</author>
  <author>James Linn</author>
  <author>Vaidyanathan Nagarajan</author>
  <year>2003</year>
  <price>49.99</price>
</book>

<book category="WEB">
  <title lang="en">Learning XML</title>
  <author>Erik T. Ray</author>
  <year>2003</year>
  <price>39.95</price>
</book>

</bookstore>

Here, I have the element <author> repeating several times within a node. How can I write my XQuery to get the result "James McGovern,Per Bothner, Kurt Cagle, James Linn, etc. " into to column author?

My code is as follows:

SELECT x1.*
  FROM test_xml t,
  XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.loc.gov/MARC21/slim'), '/bookstore/book'
    PASSING t.OBJECT_VALUE COLUMNS category VARCHAR2(100) PATH '@category',
    title VARCHAR2(100) PATH 'title',
    author VARCHAR2(100) PATH 'author',
    year VARCHAR2(100) PATH 'year',
    price VARCHAR2(100) PATH 'price'
  ) x1;

Answer 1

As @are showed you can use [string-join()](http://www.w3.org/TR/xpath-functions/#func-string-join), but presumably you want the authors associated with each book, which you can get by sticking with XMLTable - you could probably have adapted this yourself from that answer:

select x.*
from test_xml t
cross join xmltable('/bookstore/book'
  passing t.object_value
  columns title varchar2(30) path 'title',
    category varchar2(10) path '@category',
    year number(4) path 'year',
    price number path 'price',
    authors varchar2(100) path 'string-join(author/child::text(),",")'
) x;

TITLE                          CATEGORY         YEAR      PRICE AUTHORS                                                                                            
------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
Everyday Italian               COOKING          2005         30 Giada De Laurentiis                                                                                 
Harry Potter                   CHILDREN         2005      29.99 J K. Rowling                                                                                        
XQuery Kick Start              WEB              2003      49.99 James McGovern,Per Bothner,Kurt Cagle,James Linn,Vaidyanathan Nagarajan                             
Learning XML                   WEB              2003      39.95 Erik T. Ray                                                                                         

But for storage I'm not sure it really makes sense to have a comma-delimited string in a single column value; it makes it much harder to search for a book by a particular order, for example. You could extract the data more relationally with a second XMLTable:

select x.title, x.category, x.year, x.price, y.author
from test_xml t
cross join xmltable('/bookstore/book'
  passing t.object_value
  columns title varchar2(30) path 'title',
    category varchar2(10) path '@category',
    year number(4) path 'year',
    price number path 'price',
    authors xmltype path './author'
) x
cross join xmltable('/author'
  passing x.authors
  columns author varchar2(30) path '.'
) y;

TITLE                          CATEGORY         YEAR      PRICE AUTHOR                       
------------------------------ ---------- ---------- ---------- ------------------------------
Everyday Italian               COOKING          2005         30 Giada De Laurentiis           
Harry Potter                   CHILDREN         2005      29.99 J K. Rowling                  
XQuery Kick Start              WEB              2003      49.99 James McGovern                
XQuery Kick Start              WEB              2003      49.99 Per Bothner                   
XQuery Kick Start              WEB              2003      49.99 Kurt Cagle                    
XQuery Kick Start              WEB              2003      49.99 James Linn                    
XQuery Kick Start              WEB              2003      49.99 Vaidyanathan Nagarajan        
Learning XML                   WEB              2003      39.95 Erik T. Ray                   

And then put the authors into a separate table using a foreign key. The data you've showed doesn't really have a way to uniquely identify a book or an author (you can can have two books with the same title - if you had the ISBN that might help; or two authors with the same name, and you don't have any way to distinguish them), so in fact that may not be possible for what you're doing, but if you real data can at least uniquely identify a book somehow then you should really look at a child table to hold all the authors.

Once you have that you could still produce the comma-delimited string for display using listagg(), even straight from the raw XML as a demo:

select x.title, x.category, x.year, x.price,
  listagg(y.author, ',') within group (order by y.author) as authors
from test_xml t
cross join xmltable('/bookstore/book'
  passing t.object_value
  columns title varchar2(30) path 'title',
    category varchar2(10) path '@category',
    authors xmltype path './author',
    year number(4) path 'year',
    price number path 'price'
) x
cross join xmltable('/author'
  passing x.authors
  columns author varchar2(20) path '.'
) y
group by x.title, x.category, x.year, x.price;

TITLE                          CATEGORY         YEAR      PRICE AUTHORS                                                                                            
------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
Harry Potter                   CHILDREN         2005      29.99 J K. Rowling                                                                                        
Learning XML                   WEB              2003      39.95 Erik T. Ray                                                                                         
Everyday Italian               COOKING          2005         30 Giada De Laurentiis                                                                                 
XQuery Kick Start              WEB              2003      49.99 James Linn,James McGovern,Kurt Cagle,Per Bothner,Vaidyanathan Nagaraj                               

(though that's just a longer and probably less efficient way of doing string-join; but would work in earlier versions of Oracle that don't understand XPath 2.0).

Answer 2

You can use XMLQuery and string-join function (XPath 2.0).

create table test(xml xmltype);

select xmlcast(
xmlquery(('string-join(//book/author/child::text(),",")' )
passing xml returning content) as varchar2(256)) as result
    from test;

    result
1   Giada De Laurentiis,J K. Rowling,James McGovern,Per Bothner,Kurt Cagle,James Linn,Vaidyanathan Nagarajan,Erik T. Ray