将具有重复节点的XML数据加载到Oracle中的表中 [英] Load XML data with repeating nodes into tables in Oracle
问题描述
我有以下要插入到Oracle 11g中的表中的XML数据(仅作为示例，实际的巨大，不能在此处发布):
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>
在这里，我有一个元素<author>在一个节点内重复多次.如何编写我的XQuery将结果"James McGovern，Per Bothner，Kurt Cagle，James Linn等"转换为专栏作者?
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?
我的代码如下:
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;
推荐答案
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
但是对于存储，我不确定在单列值中包含逗号分隔的字符串是否真的有意义；例如，这使得按特定顺序搜索书籍变得更加困难.您可以使用第二个XMLTable来更相关地提取数据:
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
然后使用外键将作者放入单独的表中.您显示的数据实际上并不能唯一地标识一本书或一位作者(您可以拥有两本标题相同的书-如果您的ISBN可能会有所帮助；或者两位作者的名字相同，并且您没有任何区分它们的方法)，因此实际上这对于您正在做的事情来说是不可能的，但是如果您的真实数据至少可以以某种方式唯一地识别一本书，那么您应该真正看一下子表容纳所有作者.
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.
一旦有了，您仍然可以使用listagg()生成逗号分隔的字符串以供显示，甚至可以直接从原始XML中获得演示:
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
(尽管这只是执行字符串连接的一种较长且可能效率较低的方法；但是可以在不了解XPath 2.0的Oracle早期版本中使用.)
(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).
这篇关于将具有重复节点的XML数据加载到Oracle中的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
