使用 openxml 导入 xml

使用 openxml 导入 xml

---

openxml 在查询的 from 子句中使用，目的是从 xml 文档中生成结果集。openxml 使用 xpath 查询语言的子集从 xml 文档中选择节点。

 

在您使用 openxml 时，会对 xml 文档进行分析，而结果会采用树形式的模型。这个树由节点组成。xpath 表达式用于选择树中的节点。下面的列表描述了一些常用的 xpath 表达式：

• /    指明 xml 文档的根节点

• .（一个句点）    指明 xml 文档的当前节点

• //    指明当前节点的所有子代，包括当前节点

• ..    指明当前节点的父节点

• ./@属性名    指明当前节点的具有名称属性名的属性

• ./子级名    指明当前节点的那些是具有名称子级名的元素的子级

请看下面的 xml 文档：

 tee shirt
  54
 
 tee shirt
  75
 
 baseball cap
  112
 

元素不是根节点。您可以使用以下 xpath 表达式来引用它：

/inventory

假定当前节点是一个 元素。您可以使用以下 xpath 表达式来引用此节点：

.

要查找所有是 元素的子级的 元素，请使用以下 xpath 表达式：

/inventory/product

如果当前节点是 元素，而您需要引用 size 属性，请使用以下 xpath 表达式：

 

 

openxml 的第一个 xpath-query 参数每匹配一次，就会在结果集中生成一行。with 子句指定结果集的模式以及如何为结果集中的每一列找到值。例如，假定有以下查询：

select * from openxml( '
                         tee shirt
                          54
                          orange
                         
                         baseball cap

                          112
                          black
                         
                        ',
                       '/inventory/product' )
with ( name char (25) './text()',
       quantity char(3) 'quantity',
       color char(20) 'color')

第一个 xpath-query 参数是 /inventory/product，并且 xml 中有两个 元素，所以，此查询会生成两行。

with 子句指定了有三列：name、quantity 和 color。这些列的值是从 、 和 元素中获得的。上面的查询会生成以下结果：

name	quantity	color
tee shirt	54	orange
baseball cap	112	black

 

 

openxml 可以用来生成边缘表，在这种表中， xml 文档中的每一个元素都会有对应的一行。您可能会需要生成边缘表，以便能够使用 sql 来查询结果集中的数据。

下面的 sql 语句创建一个变量 x，该变量包含一个 xml 文档。该查询生成的 xml 有一个名为 的根元素，这个根元素是使用 xmlelement 函数生成的，而且会使用指定了 elements 修饰符的 for xml auto 为 employee、sales_order 和 customer 等表中的每一列生成元素。

create variable x xml;
set x=(select xmlelement( name root,
         (select * from employee
         key join sales_order
         key join customer
         for xml auto, elements)));

生成的 xml 看上去会像下面这样（已对结果进行格式化处理以便更容易阅读—查询返回的结果是一个连续字符串）：

 
  299
  902
  rollin
  overbey
  200
  191 companion ct.

  kanata
  ca
  94608
  5105557255
  a
  025487133
  39300.000
  1987-02-19

  1964-03-15
  y
  y
  n
  m
  
   2001
   101

   2000-03-16
   r1
   eastern
   299
   
    101
    michael
    devlin
    
114 pioneer avenue

    kingston
    nj
    07070
    2015558966
    the power group
   
  

...

下面的查询使用 descendant-or-self (//*) xpath 表达式来匹配上面 xml 文档中的每一个元素，而且，对于每一个元素都使用 id 元属性来为节点获取 id，而且将父 (../) xpath 表达式与 id 元属性一起使用来获取父节点。localname 元属性用于获取每个元素的名称。

select * from openxml( x, '//*' )
 with (id int '@mp:id',
       parent int '../@mp:id',
       name char(20) '@mp:localname',
       text long varchar 'text()' )
order by id

此查询生成的结果集会显示每个节点的 id、父节点的 id，以及 xml 文档中各元素的名称和内容。

id	parent	name	text
5	(null)	root	(null)
23	15	emp_id	299
47	15	manager_id	902
74	15	emp_fname	rollin
...	...	...	...

 

如果您有一个表，它的某一列包含 xml，您可以使用 openxml 一次查询出该列中所有的 xml 值。使用侧向派生表可以实现这一点。

下面的语句创建一个两列（manager_id 和 reports）的表。reports 列包含从 employee 表中生成的 xml 数据。

create table t (manager_id int, reports xml);
insert into t
select manager_id, xmlelement( name reports,
                    xmlagg(
                     xmlelement( name e, emp_id))) 
from employee
group by manager_id;

请执行下面的查询来查看 t 表中的数据：

select * from t;

此查询会产生以下结果：

manager_id	reports
1293	148 390 586 757 ...
1576	184 207 318 409 ...
902	129 195 299 467 ...
703	191 750 868 921 ...
...	...

下面的查询使用侧向派生表来生成一个两列的结果集，一列列出各位经理的 id，另一列列出接受该经理领导的各位雇员的 id：

select manager_id, eid
from t, lateral( openxml( t.reports, '//e' )
with (eid int '.') ) dt

此查询会生成以下结果：

manager_id	eid
1293	148
1293	390
1293	586
1293	757
...	...