Oracle XMLSequence属性名称和值转换

I am using XML-based auditing for our Oracle system. There are triggers in place on each table to create an XML representation of the record being inserted or updated into a single audit table in our database. The capturing of the audit data is working properly, however I am having issues extracting the information using Oracle's XML parsing utilities.

我正在为我们的Oracle系统使用基于XML的审计。每个表上都有触发器来创建正在插入或更新到我们数据库中的单个审计表中的记录的XML表示。捕获审计数据工作正常,但是我在使用Oracle的XML解析实用程序提取信息时遇到问题。

Using our audit table as an example, the audit table has an XML column(CLOB datatype) like the following:

使用我们的审计表作为示例,审计表具有XML列(CLOB数据类型),如下所示:

<Audit ev='MSGADT0052$' un='ANSAR' dt='09/06/2013 16:39 '/>
<bo n='EntityDocumentBO'/>
<A n='DocExpiryDate' nv='24-07-2015' ov='05-10-2018'/>
<A n='DocDescr' nv='National ID' ov='National ID'/>
<A n='DocReceivedDate' nv='09/06/2013 16:39 ' ov='24-11-2009 '/>

I would like to be able to parse the XML into one of two formats.

我希望能够将XML解析为两种格式之一。

• Each attribute name within the ROW element would be a column and each attribute value would be the value beneath the column, OR

ROW元素中的每个属性名称都是一列,每个属性值都是列下面的值OR

• Each attribute name and value combination would be on it's own row

每个属性名称和值组合都在它自己的行上

can anyone please help me with a code to serve my purpose as search and tried other old threads but i couldnt end up with a result because of luck of knowledge in XML. thank you...

任何人都可以帮我一个代码,以达到我的目的搜索和尝试其他旧线程,但我不能得到一个结果,因为XML的知识运气。谢谢...

1 个解决方案

#1

XMLSequence is deprecated from 11g. Here's an XMLTable approach that gets the Audit attributes and the A attributes as columns:

XMLSequence已从11g弃用。这是一个XMLTable方法,它将Audit属性和A属性作为列:

select x.*
from audit_table a
cross join XMLTable('for $i in /auditrec
    for $j in $i/A
      return <row ev="{$i/Audit/@ev}" un="{$i/Audit/@un}" dt="{$i/Audit/@dt}"
        n="{$j/@n}" ov="{$j/@ov}" nv="{$j/@nv}"/>'
  passing XMLType(a.clob_col)
  columns ev varchar2(11) path '@ev',
    un varchar2(5) path '@un',
    dt varchar2(10) path '@dt',
    n varchar2(20) path '@n',
    ov varchar2(16) path '@ov',
    nv varchar2(16) path '@nv'
) x;

EV          UN    DT         N                    OV               NV             
----------- ----- ---------- -------------------- ---------------- ----------------
MSGADT0052$ ANSAR 09/06/2013 DocExpiryDate        05-10-2018       24-07-2015      
MSGADT0052$ ANSAR 09/06/2013 DocDescr             National ID      National ID     
MSGADT0052$ ANSAR 09/06/2013 DocReceivedDate      24-11-2009       09/06/2013 16:39

I've restricted the column sizes quiet a bit to reduce scrolling here, so you'd need to set those to an appropriate length for your actual data. And you could include the bo node's n attribute as another column if you needed that.

我已经限制了列的大小安静,以减少滚动,因此您需要将它们设置为适合您实际数据的长度。如果需要,可以将bo节点的n属性作为另一列包含在内。