Oracle 从关系数据生成动态XMLAttributes。

我们已经将xml数据以关系方式存储在数据库中。元素以分层的方式，在单独的表中的属性，可以将其连接到多个元素。我们能够通过dbms_xmlgen动态生成元素，但是我们没有发现xml属性的解决方案。

我创建了一个示例，其中表在两个带块中进行模拟: 元素的hirarchy和属性的attr
这两个表可以通过 “hirarchy.id = attr.element_id” 连接。

followin语句为我们提供了包含所有元素但没有属性的XML

SELECT DBMS_XMLGEN.getXML( 
         DBMS_XMLGEN.newcontextfromhierarchy( 
           'WITH hirarchy AS(' || chr(10) || 
           '  SELECT 1 ID, NULL parent_id, ''article_list'' NAME, NULL VALUE, 1 POS FROM dual UNION ALL' || chr(10) || 
           '  SELECT 2, 1, ''article'', ''shoe'', 2 FROM dual UNION ALL' || chr(10) || 
           '  SELECT 3, 1, ''article'', ''shirt'', 2 FROM dual' || chr(10) || 
           '), attr AS(' || chr(10) || 
           '  SELECT 2 element_id, ''article_no'' NAME, ''123456789'' VALUE FROM dual UNION ALL' || chr(10) || 
           '  SELECT 3 element_id, ''article_no'' NAME, ''0815'' VALUE FROM dual UNION ALL' || chr(10) || 
           '  SELECT 3 element_id, ''name'' NAME, ''T-Shirt'' VALUE FROM dual' || chr(10) || 
           ')' || chr(10) || 
           ' SELECT LEVEL, XMLELEMENT(EVALNAME(h.name), h.value)' || chr(10) || 
           '   FROM hirarchy h' || chr(10) || 
           'CONNECT BY PRIOR h.id = h.parent_id' || chr(10) || 
           '  START WITH h.parent_id IS NULL' || chr(10) || 
           ' ORDER SIBLINGS BY POS' 
         ) 
      )    
 FROM dual

结果应该是这样的

  
shoe
  
shirt

现在我们正在寻找一种动态生成这些属性的方法。解决方案可能使用SQL或PL/SQL。

您可以使用XMLAttributes定义元素的属性。这需要一个逗号分隔的名称和值列表。

所以你可以通过以下方式得到你想要的:

-为每个元素的属性分配行号
-外部将每个位置的属性加入层次结构一次
-将属性名称/值对的列表传递给XMLAttributes

所以你有N个外部联接到编号的属性。这意味着您需要知道元素可以具有的最大属性数!

set long 10000
with rws as (
  select q'!WITH hirarchy AS(
  SELECT 1 ID, NULL parent_id, 'article_list' NAME, NULL VALUE, 1 POS FROM dual UNION ALL
  SELECT 2, 1, 'article', 'shoe', 2 FROM dual UNION ALL
  SELECT 3, 1, 'article', 'shirt', 2 FROM dual
), attr AS(
  SELECT 2 element_id, 'article_no' NAME, '123456789' VALUE FROM dual UNION ALL
  SELECT 3 element_id, 'article_no' NAME, '0815' VALUE FROM dual UNION ALL
  SELECT 3 element_id, 'name' NAME, 'T-Shirt' VALUE FROM dual
), attr_rn as (
  select a.*, row_number () over ( 
           partition by element_id 
           order by name 
         ) rn
  from   attr a
)
   SELECT LEVEL, 
          XMLELEMENT(
            EVALNAME(h.name), 
            xmlattributes ( 
              a1.value as EVALNAME(a1.name) ,
              a2.value as EVALNAME(a2.name) 
            ),
            h.value
          ) x
   FROM hirarchy h
   left join attr_rn a1
   on     a1.element_id = h.id
   and    a1.rn = 1
   left join attr_rn a2
   on     a2.element_id = h.id
   and    a2.rn = 2
   CONNECT BY PRIOR h.id = h.parent_id
   START WITH h.parent_id IS NULL
   ORDER SIBLINGS BY POS
!' x from dual
)
SELECT 
       DBMS_XMLGEN.getXML( 
         DBMS_XMLGEN.newcontextfromhierarchy( 
           x
         ) 
       )  ,
       x  
FROM rws
/

DBMS_XMLGEN.GETXML(DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY(X))                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       


  
shoe
  
shirt

    WITH hirarchy AS(
  SELECT 1 ID, NULL parent_id, 'article_list' NAME, NULL VALUE, 1 POS FROM dual UNION ALL
  SELECT 2, 1, 'article', 'shoe', 2 FROM dual UNION ALL
  SELECT 3, 1, 'article', 'shirt', 2 FROM dual
), attr AS(
  SELECT 2 element_id, 'article_no' NAME, '123456789' VALUE FROM dual UNION ALL
  SELECT 3 element_id, 'article_no' NAME, '0815' VALUE FROM dual UNION ALL
  SELECT 3 element_id, 'name' NAME, 'T-Shirt' VALUE FROM dual
), attr_rn as (
  select a.*, row_number () over ( 
           partition by element_id 
           order by name 
         ) rn
  from   attr a
)
   SELECT LEVEL, 
          XMLELEMENT(
            EVALNAME(h.name), 
            xmlattributes ( 
              a1.value as EVALNAME(a1.name) ,
              a2.value as EVALNAME(a2.name) 
            ),
            h.value
          ) x
   FROM hirarchy h
   left join attr_rn a1
   on     a1.element_id = h.id
   and    a1.rn = 1
   left join attr_rn a2
   on     a2.element_id = h.id
   and    a2.rn = 2
   CONNECT BY PRIOR h.id = h.parent_id
   START WITH h.parent_id IS NULL
   ORDER SIBLINGS BY POS

可能有一种更优雅的方法可以做到这一点，但是如果你想动态生成属性名称，我不确定它会是什么