mysql select 字段解析xml

或非与

于 2023-05-04 11:11:31 发布

阅读量353

点赞数

文章标签： mysql xml 数据库

本文链接：https://blog.csdn.net/qq_27517377/article/details/130483181

版权

bank_request为字段名称是xml如下：

<responseblock version="3.67">
  <requestreference>W58-0YTpAh47</requestreference>
  <response type="SCHEMEUPDATE">
    <billing>
      <payment type="VISA">
        <issuer>WELLS FARGO BANK  NATIONAL ASSOCIATION</issuer>
        <issuercountry>US</issuercountry>
        <pan>414718######8413</pan>
      </payment>
    </billing>
    <error>

select extractvalue(bank_request, '/responseblock/response/billing/payment/issuer') AS name from xxx ;

输出：WELLS FARGO BANK NATIONAL ASSOCIATION

select extractvalue(bank_request, '/responseblock/response/billing/payment/@type') as card_type from xxx;

输出：VISA