oracle解析xml，带命令空间的节点获取

在xml里有两个解析xml的函数extract和extractVALUE，这两个函数可以带两个参数，也可以带上个参数,第三个参数是命令空间，对于namespace_string,刚开始我也很疑惑，然后去网上找了很久的资料也没弄明白，因为没有范例，最后在自己的尝试下解决了，

extract官网API如下：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions052.htm#i1131042

extract官网API如下：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions052.htm#i1131042

样例xml报文如下：

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices">
<soapenv:Header>
<euc:AuthHeaderRequest>
<userName>User1</userName>
<Token>45df847e-c02e-64d4-f6a1-76a9c8f2c537</Token>
</euc:AuthHeaderRequest>
</soapenv:Header>
<soapenv:Body>
<euc:EUCRevisionNewOrderRequest>
<header>
<Department>Business Solution - Business Broadband</Department>
<AcceptTNC>Yes</AcceptTNC>
<TransactionId>FB000120170119181518436</TransactionId>
</header>
<body>
<SalesOrderId>2017011914381897</SalesOrderId>
<PlanType>Non-Residential CA Customised PIR</PlanType>
<IsGovernment>N</IsGovernment>
<EUCBaseInfo>
<RSPCustomerRef>BBNC1000001230</RSPCustomerRef>
<ServiceType>NEUC</ServiceType>
</EUCBaseInfo>
<AdditionalInfo/>
<EndUserContact>
<Salutation>Mrs</Salutation>
<FirstName>rrrrrr</FirstName>
<LastName>rrrrrr</LastName>
<ContactNumber1>44444444</ContactNumber1>
<ContactNumber2>44444444</ContactNumber2>
<Email>www@qq.com.cn</Email>
</EndUserContact>
<SpecialRequest/>
<InstContactInfo>
<Salutation>Mrs</Salutation>
<FirstName>rrrrrr</FirstName>
<LastName>rrrrrr</LastName>
<ContactNumber1>44444444</ContactNumber1>
<ContactNumber2>44444444</ContactNumber2>
<Email>www@qq.com.cn</Email>
</InstContactInfo>
<ONInstPref>
<InstPrefDate>2017-02-04</InstPrefDate>
<InstONTimeSlot>09:00-13:00</InstONTimeSlot>
</ONInstPref>
<EUCConfig>
<ServicePortType>Gigabit Ethernet UTP (1Gbps) over GPON</ServicePortType>
<NumOfServicePorts>2</NumOfServicePorts>
<Layer2Option>IEEE802.1q</Layer2Option>
<PIRDL>250</PIRDL>
<PIRUL>250</PIRUL>
</EUCConfig>
<ServPortConfigInfoList>
<ServicePort>A</ServicePort>
<ServiceProfile>FB0001-BzHighBB_Ded-EUC-01</ServiceProfile>
<CoS>D</CoS>
<SVLAN>1022</SVLAN>
<CIRDL>50</CIRDL>
<CIRUL>50</CIRUL>
</ServPortConfigInfoList>
<ServPortConfigInfoList>
<ServicePort>B</ServicePort>
<ServiceProfile>FB0001-NR-SmartUC_Fixed-EUC-01</ServiceProfile>
<CoS>A</CoS>
<SVLAN>1049</SVLAN>
<CIRDL>5</CIRDL>
<CIRUL>5</CIRUL>
</ServPortConfigInfoList>
</body>
</euc:EUCRevisionNewOrderRequest>
</soapenv:Body>
</soapenv:Envelope>

直接获取节点或者节点值样例sql如下：

select extract(xmltype('<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices">
  <soapenv:Header>
    <euc:AuthHeaderRequest>
      <userName>User1</userName>
      <Token>45df847e-c02e-64d4-f6a1-76a9c8f2c537</Token>
    </euc:AuthHeaderRequest>
  </soapenv:Header>
  <soapenv:Body>
    <euc:EUCRevisionNewOrderRequest>
      <header>
        <Department>Business Solution - Business Broadband</Department>
        <AcceptTNC>Yes</AcceptTNC>
        <TransactionId>FB000120170119181518436</TransactionId>
      </header>
      <body>
        <SalesOrderId>2017011914381897</SalesOrderId>
      </body>
    </euc:EUCRevisionNewOrderRequest>
  </soapenv:Body>
</soapenv:Envelope>'),'/soapenv:Envelope/soapenv:Body/euc:EUCRevisionNewOrderRequest/body','xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices"')
  from dual a

select extractVALUE(xmltype('<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices">
 <soapenv:Header>
  <euc:AuthHeaderRequest>
   <userName>User1</userName>
   <Token>45df847e-c02e-64d4-f6a1-76a9c8f2c537</Token>
  </euc:AuthHeaderRequest>
 </soapenv:Header>
 <soapenv:Body>
  <euc:EUCRevisionNewOrderRequest>
   <header>
    <Department>Business Solution - Business Broadband</Department>
    <AcceptTNC>Yes</AcceptTNC>
    <TransactionId>FB000120170119181518436</TransactionId>
   </header>
   <body>
    <SalesOrderId>2017011914381897</SalesOrderId>
   </body>
  </euc:EUCRevisionNewOrderRequest>
 </soapenv:Body>
</soapenv:Envelope>'),'/soapenv:Envelope/soapenv:Body/euc:EUCRevisionNewOrderRequest/body/SalesOrderId','xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices"')
 from dual a

通过xmltable转换为表格sql样例如下：

SELECT *
  FROM XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS
                              "soapenv1",'http://platform.nucleusconnect.com/wsdl/EUCServices' AS
                              "euc1"),'$B/soapenv1:Envelope/soapenv1:Body/euc1:EUCRevisionNewOrderRequest/body'
                PASSING
                (select xmltype('<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:euc="http://platform.nucleusconnect.com/wsdl/EUCServices">
  <soapenv:Header>
    <euc:AuthHeaderRequest>
      <userName>User1</userName>
      <Token>45df847e-c02e-64d4-f6a1-76a9c8f2c537</Token>
    </euc:AuthHeaderRequest>
  </soapenv:Header>
  <soapenv:Body>
    <euc:EUCRevisionNewOrderRequest>
      <header>
        <Department>Business Solution - Business Broadband</Department>
        <AcceptTNC>Yes</AcceptTNC>
        <TransactionId>FB000120170119181518436</TransactionId>
      </header>
      <body>
        <SalesOrderId>2017011914381897</SalesOrderId>
      </body>
    </euc:EUCRevisionNewOrderRequest>
  </soapenv:Body>
</soapenv:Envelope>')
                   from dual a ) AS B COLUMNS SalesOrderId
                VARCHAR2(128) PATH '/body/SalesOrderId')

oracle解析xml，带命令空间的节点获取

猜你在找的Oracle相关文章