sql - 从具有 XML 内容的字段创建 View (SQL)

Question

我在 Oracle DBMS 中工作，并且有一个表，其中一个字段具有 XML 代码，因此我想从该字段创建一个 View 。这个相同的 View 应该包含一组属性，就像那些拥有 XML 的 View 一样。我已经有一个解决方案，但是它太麻烦了(大约有 50 个字段)并且想加快这个过程。

一段具有属性的代码....

<AREAMEDIC200 titulo="campo1; " valor="False" />
<AREAMEDIC210 titulo="campo2; " valor="False" />
<AREAMEDIC220 titulo="campo3; " valor="True" />

....

部分SQL

CREATE VIEW teste AS
Select NPROCESSO,
trim(extract(MENUXML, '//AREAMEDIC200/@valor')) AS c1,
trim(extract(MENUXML, '//AREAMEDIC210/@valor')) AS c2,
trim(extract(MENUXML, '//AREAMEDIC220/@valor')) AS c3,
.....
FROM TABELA1;

有人对加快开发速度有什么建议吗？

问候

Answer 1

假设 MENUXML 是 XMLType 类型，并且有一个根节点，您可以使用 xmltable 将值提取为行:

select t.nprocesso, x.titulo, x.valor
from tabela1 t,
xmltable('/TOPLEVEL/*' passing t.menuxml
  columns titulo varchar2(10) path '@titulo',
    valor varchar2(5) path '@valor') x

 NPROCESSO TITULO     VALOR
---------- ---------- -----
         1 campo1;    False 
         1 campo2;    False 
         1 campo3;    True  

然后您可以将其转换为列，但您仍然必须在某处列出可能的值 - 值的数量必须被称为解析时间，因此它不能完全灵活:

select *
from (
select t.nprocesso, rtrim(x.titulo, ';') as titulo, x.valor
from tabela1 t,
xmltable('/TOPLEVEL/*' passing t.menuxml
  columns titulo varchar2(10) path '@titulo',
    valor varchar2(5) path '@valor') x
)
pivot (min(valor) as v for (titulo) in ('campo1' as campo1, 'campo2' as campo2,
  'campo3' as campo3))

... except 导致 11.2.0.3 中的核心转储(ORA-07445:遇到异常:核心转储 [evaopn3()+125] [SIGSEGV]如果您有兴趣，除了 NO_XML_QUERY_REWRITE 提示没有帮助外，可能会注意到 1580106.1)。

由于您实际上并没有说您使用的是 11g，因此伪造枢轴的旧方法仍然有效，但工作量稍大:

select nprocesso,
  max(case when titulo = 'campo1; ' then valor end) as campo1,
  max(case when titulo = 'campo2; ' then valor end) as campo2,
  max(case when titulo = 'campo3; ' then valor end) as campo3
from (
select t.nprocesso, x.titulo, x.valor
from tabela1 t,
xmltable('/TOPLEVEL/*' passing t.menuxml
  columns titulo varchar2(10) path '@titulo',
    valor varchar2(5) path '@valor') x
)
group by nprocesso
order by nprocesso;

 NPROCESSO CAMPO1 CAMPO2 CAMPO3
---------- ------ ------ ------
         1 False  False  True   

SQL Fiddle demo . pivot 版本在那里工作(11.2.0.2，我相信)只要包含提示。

您“只”需要为 50 个可能值中的每一个重复 max... 子句。不确定您是否会认为这比您现在所做的更容易。

编辑:如果MENUXML确实是一个varchar2，您可以将它转换为一个XMLType，同时添加一个根节点，这样允许:

...
xmltable('/ROOT/*' passing xmltype('<ROOT>' || t.menuxml || '</ROOT>')
...