Oracle 从基于灵活属性的XML读取数据的问题

嗨，汤姆，
我不是XML专家。我有一个灵活的基于属性的XML存储到XMLTYPE列TBTest.C1(XMLTYPE) 中的表，它是来自上游队列的消息。消息的XPATHs就像-

/*[name()='Message']
/*[name()='Message']/*[name()='Version']
/*[name()='Message']/*[name()='DateTime']
/*[name()='Message']/*[name()='Object']
/*[name()='Message']/*[name()='ObjectProperties']
/*[name()='Message']/*[name()='ObjectProperties']/*[name()='EventId']
/*[name()='Message']/*[name()='ObjectProperties']/*[name()='SourceSystem']
/*[name()='Message']/*[name()='ObjectProperties']/*[name()='EventType']
/*[name()='Message']/*[name()='AdditionalObjectProperties']
/*[name()='Message']/*[name()='AdditionalObjectProperties']/*[name()='ObjectElementValue']
/*[name()='Message']/*[name()='TestStatus']
/*[name()='Message']/*[name()='TestStatus']/*[name()='TestResult']
复制

样本数据:

v2.0
2018-06-10T11:00:05Z


   0000121
   S1
   New


   42440
   B1-42440
   B1


   New


   Pass
   Fail
   NA


   Fail
   Fail
   Fail

复制

我希望将这些数据提取成表格形式。我一直在尝试使用XMLTABLE运算符来获取数据，但无法成功。
我怎样才能实现这一点，任何快速的SQL来解决这个问题？

谢谢!

您可以使用XMLTable来执行此操作。以下是一些原则:

XML具有命名空间xmlns = "urn:message"。所以你要包括:

xmlnamespaces ( default 'urn:message' )
复制

在所有XMLTable调用的开始。因此，要获取元素的值，请执行以下操作:

select tests.*
from   t, xmltable (
  xmlnamespaces ( default 'urn:message' ),
  '/' passing t.x
  columns 
    DateTime path '/Message/DateTime',
    event_id path '/Message/ObjectProperties/EventId'
  ) tests;

DATETIME               EVENT_ID   
2018-06-10T11:00:05Z   0000121 
复制

您有多个具有相同名称的元素。要将这些提取到单独的行中，请将XMLTable调用链接在一起。通过将重复的元素定义为XMLType列来做到这一点。然后要将重复的元素变成行，请在下一个XMLTable调用的传递子句中使用双斜杠前缀:

select event_id, datetime, control_id
from   t, xmltable (
  xmlnamespaces ( default 'urn:message' ),
  '/' passing t.x
  columns 
    DateTime path '/Message/DateTime',
    event_id path '/Message/ObjectProperties/EventId',
    test_status xmltype path '/Message/TestStatus'
  ) tests, xmltable (
    xmlnamespaces ( default 'urn:message' ),
    '//TestStatus' passing tests.test_status
    columns 
      control_id  path '@ControlId'
  ) results;

EVENT_ID   DATETIME               CONTROL_ID   
0000121    2018-06-10T11:00:05Z   T1           
0000121    2018-06-10T11:00:05Z   T2 
复制

@ prefix允许您访问属性值。

从这里开始，根据需要继续链接呼叫:

select event_id, datetime, control_id, test_id, res
from   t, xmltable (
  xmlnamespaces ( default 'urn:message' ),
  '/' passing t.x
  columns 
    DateTime path '/Message/DateTime',
    event_id path '/Message/ObjectProperties/EventId',
      test_status xmltype path '/Message/TestStatus'
  ) tests, xmltable (
    xmlnamespaces ( default 'urn:message' ),
    '//TestStatus' passing tests.test_status
    columns 
      test_result xmltype path '/TestStatus/TestResult',
      control_id  path '@ControlId'
  ) results, xmltable (
    xmlnamespaces ( default 'urn:message' ),
    '//TestResult' passing results.test_result
    columns 
      test_id path '@TestId',
      res path 'text()'
  );

EVENT_ID   DATETIME               CONTROL_ID   TEST_ID   RES    
0000121    2018-06-10T11:00:05Z   T1           BB-0001   Pass   
0000121    2018-06-10T11:00:05Z   T1           BB-0002   Fail   
0000121    2018-06-10T11:00:05Z   T1           BB-0003   NA     
0000121    2018-06-10T11:00:05Z   T2           BT-0001   Fail   
0000121    2018-06-10T11:00:05Z   T2           BT-0002   Fail   
0000121    2018-06-10T11:00:05Z   T2           BT-0003   Fail  
复制

表创建和填充脚本:

create table t (
  x xmltype
);

insert into t values (xmltype ('
v2.0
2018-06-10T11:00:05Z


   0000121
   S1
   New


   42440
   B1-42440
   B1


   New


   Pass
   Fail
   NA


   Fail
   Fail
   Fail

'));
复制