如何以XML的形式查询分层数据

Question

我有一个这样的表:

      COL1 COL2             COL3
---------- ---------- ----------
         T1 Direct              M1
         T1 Direct              M2
         T1 List                M3
         M3 Direct              M4
         M3 Direct              M5
         M3 List                M6
         M6 Direct              M7
         M6 Direct              M8
         T2 Direct              M9

Col2 - 'Direct'表示Col3是Col1的直接子元素。

Col2 - 'List'表示Col3是Col1的子列表。

所以对于上面的例子，T1有M1,M2作为直接子，M3是子。同样，M3有M4,M5直接子节点，M6是子节点。

我需要上面的转换为XML像下面使用Oracle查询。

 <ROOT>
 <T1>
    <M1/>
    <M2/>
    <M3>
       <M4/>
       <M5/>
       <M6>
         <M7/>
         <M8/>
       <M6/>
    <M3/>
 <T1/>
 <T2>
   <M9 />
 <T2>
 </ROOT>

我怎样才能得到上面的XML结果?我被允许添加额外的条目表，以保持整个xml在<ROOT>标记。

Answer 1

XML节点名不能以数字开头，因此您所显示的输出无效。您可以使用不同的名称来获取结构，或者使用这些值来获取属性。

从获取层次结构的查询开始(col2在这里似乎无关紧要，它只是描述从col1和col3存在的层次结构):

select lpad(' ', level) || col3
from t42
connect by col1 = prior col3 and col1 != col3
start with col1 = col3;
LPAD('',LEVEL)||COL3                                                           
--------------------------------------------------------------------------------
 1                                                                              
  2                                                                             
  3                                                                             
   4                                                                            
   5                                                                            
   6                                                                            
    7                                                                           
    8                                                                           

然后可以将它们转换为XML元素，在本例中，前面加一个固定字符使其成为有效名称:

select level, xmlelement(evalname 'x' || col3)
from t42
connect by col1 = prior col3 and col1 != col3
start with col1 = col3;
     LEVEL XMLELEMENT(EVALNAME'X'||COL3)                                             
---------- --------------------------------------------------------------------------------
         1 <x1></x1>
         2 <x2></x2>
         2 <x3></x3>
         3 <x4></x4>
         3 <x5></x5>
         3 <x6></x6>
         4 <x7></x7>
         4 <x8></x8>

然后您可以在dbms_xmlgen.newContextFromHierarchy调用中使用该查询:

select dbms_xmlgen.getxmltype(
  dbms_xmlgen.newcontextfromhierarchy(
    q'[select level, xmlelement(evalname 'x' || col3)
      from t42
      connect by col1 = prior col3 and col1 != col3
      start with col1 = col3]')) as result
from dual;
RESULT                                                                         
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<x1>
  <x2/>
  <x3>
    <x4/>
    <x5/>
    <x6>
      <x7/>
      <x8/>
    </x6>
  </x3>
</x1>

或作为属性:

select dbms_xmlgen.getxmltype(
  dbms_xmlgen.newcontextfromhierarchy(
    q'[select level,
        xmlelement("x", xmlattributes(level as "level", col3 as "col3"))
      from t42
      connect by col1 = prior col3 and col1 != col3
      start with col1 = col3]')) as result
from dual;
RESULT                                                                         
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<x level="1" col3="1">
  <x level="2" col3="2"/>
  <x level="2" col3="3">
    <x level="3" col3="4"/>
    <x level="3" col3="5"/>
    <x level="3" col3="6">
      <x level="4" col3="7"/>
      <x level="4" col3="8"/>
    </x>
  </x>
</x>

如果你真的想，你可以将第一个版本转换为字符串，并去掉虚拟字符:

select replace(
  dbms_xmlgen.getxmltype(
    dbms_xmlgen.newcontextfromhierarchy(
      q'[select level, xmlelement(evalname 'x' || col3)
        from t42
        connect by col1 = prior col3 and col1 != col3
        start with col1 = col3]')).getstringval(),
  'x', null) as result
from dual;
RESULT                                                                         
--------------------------------------------------------------------------------
<?ml version="1.0"?>                                                            
<1>                                                                             
  <2/>                                                                          
  <3>                                                                           
    <4/>                                                                        
    <5/>                                                                        
    <6>                                                                         
      <7/>                                                                      
      <8/>                                                                      
    </6>                                                                        
  </3>                                                                          
</1>

或等效的getclobval()用于大型文档。但这是一个字符串，而不是真正的XML;如果您试图将其转换回XMLType，则会得到类似"LPX-00231:在Name或Nmtoken中发现无效字符49('1')"的错误。无论如何，你可能有一些东西会接受它