如何在PL/SQL中使用SELECT XMLTABLE指定列提取XML文档

你的示例SQL无法正确提取数据，核心原因是没处理XML中的命名空间。原SOAP XML包含两个命名空间：SOAP信封的http://schemas.xmlsoap.org/soap/envelope/，以及业务数据的http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd，必须在XMLTABLE中声明并使用这些命名空间才能准确定位节点。

待处理的SOAP XML文档 #

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Header/>
<SOAP-ENV:Body>
<Response xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<Header xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<MessageID xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">2600</MessageID>
<RelatesTo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">12033428</RelatesTo>
</Header>
<Result xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<ResultCode xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">0</ResultCode>
<ResultMsgCode xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">Success</ResultMsgCode>
<ResultMsg xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">Success</ResultMsg>
</Result>
<Payload xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<SubmitShipmentResult xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<ReferenceNumber xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">01523</ReferenceNumber>
<DeliveryTrackingNumber xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">01753077</DeliveryTrackingNumber>
<LoggingDetails xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<LoggingDetail xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<Code xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">0</Code>
<Message xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">Message is rightly processed</Message>
</LoggingDetail>
</LoggingDetails>
<ShpUnits xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<ShpUnit xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<RefNo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">1523_0001</RefNo>
<ExtRefNo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">01753194</ExtRefNo>
</ShpUnit>
<ShpUnit xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<RefNo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">1523_0002</RefNo>
<ExtRefNo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">01753195</ExtRefNo>
</ShpUnit>
<ShpUnit xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">
<RefNo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">1523_0003</RefNo>
<ExtRefNo xmlns="http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd">01753196</ExtRefNo>
</ShpUnit>
</ShpUnits>
</SubmitShipmentResult>
</Payload>
</Response>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

修正后的基础提取SQL #

以下SQL处理了命名空间，可正确提取ReferenceNumber和DeliveryTrackingNumber：

SELECT xt.*
FROM t_xml x,
     XMLTABLE(
       XMLNAMESPACES(
         'http://schemas.xmlsoap.org/soap/envelope/' AS "s",
         'http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd' AS "dm"
       ),
       '/s:Envelope/s:Body/dm:Response/dm:Payload/dm:SubmitShipmentResult'
       PASSING x.COL_XML
       COLUMNS
         "RefNumber" VARCHAR2(30) PATH 'dm:ReferenceNumber',
         "DelTrackNumber" VARCHAR2(30) PATH 'dm:DeliveryTrackingNumber'
     ) xt;

提取嵌套的重复节点（如ShpUnit） #

如果需要提取ShpUnits下的多个ShpUnit条目，可使用嵌套XMLTABLE实现一对多数据提取：

SELECT main.RefNumber,
       main.DelTrackNumber,
       shp.RefNo,
       shp.ExtRefNo
FROM t_xml x,
     XMLTABLE(
       XMLNAMESPACES(
         'http://schemas.xmlsoap.org/soap/envelope/' AS "s",
         'http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd' AS "dm"
       ),
       '/s:Envelope/s:Body/dm:Response/dm:Payload/dm:SubmitShipmentResult'
       PASSING x.COL_XML
       COLUMNS
         "RefNumber" VARCHAR2(30) PATH 'dm:ReferenceNumber',
         "DelTrackNumber" VARCHAR2(30) PATH 'dm:DeliveryTrackingNumber',
         ShpUnits XMLTYPE PATH 'dm:ShpUnits'
     ) main,
     XMLTABLE(
       XMLNAMESPACES('http://Spv.co.za/schemas/CreateCashup/DataModel/Schema.xsd' AS "dm"),
       '/dm:ShpUnits/dm:ShpUnit'
       PASSING main.ShpUnits
       COLUMNS
         "RefNo" VARCHAR2(30) PATH 'dm:RefNo',
         "ExtRefNo" VARCHAR2(30) PATH 'dm:ExtRefNo'
     ) shp;

关键注意事项 #

• 命名空间声明：必须用XMLNAMESPACES子句给每个命名空间分配前缀，否则XMLTABLE无法识别带命名空间的节点。
• XPath前缀：所有节点路径都要加上对应的命名空间前缀（如s:对应SOAP信封，dm:对应业务数据）。
• 嵌套节点处理：对于重复的子节点，先将父节点提取为XMLTYPE，再用第二个XMLTABLE解析该子XML，实现批量提取。

内容的提问来源于stack exchange，提问作者Tum3lo