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

最新推荐文章于 2023-03-01 11:22:22 发布

俺爱吃萝卜

最新推荐文章于 2023-03-01 11:22:22 发布

阅读量7.7k

点赞数 2

分类专栏： sql oracle 文章标签： sql xml oracle namespace

本文链接：https://blog.csdn.net/liu22985342/article/details/54669490

版权

oracle 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

sql

1 篇文章 0 订阅

订阅专栏

在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')

俺爱吃萝卜

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
oracle解析xml，带命令空间的节点获取

在xml里有两个解析xml的函数extract和extractVALUE，这两个函数可以带两个参数，也可以带上个参数,第三个参数是命令空间，对于namespace_string,刚开始我也很疑惑，然后去网上找了很久的资料也没弄明白，因为没有范例，最后在自己的尝试下解决了，extract官网API如下：http://docs.oracle.com/cd/B19306_01/server.
复制链接

扫一扫