mysql - 如何使用mysql提取值函数从xml中检索数据
问题描述
我想检索ReferenceNumber中存在的值
我使用了以下查询,但结果为空
SELECT EXTRACTVALUE('<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <DCRequest xmlns="http://hello.com/dc/extsvc"> <Authentication type="Ond"> <UserId>hello</UserId> <Password>345545</Password> </Authentication> <RequestInfo> <SolutionSetId>1617</SolutionSetId> <SolutionSetVersion>85</SolutionSetVersion> <ExecutionMode>NewWithContext</ExecutionMode> <EnvironmentId>1</EnvironmentId> </RequestInfo> <Fields> <Field key="ApplicationData"> <![CDATA[<ApplicationData> <SkipFlag>false</SkipFlag> <Purpose>05</Purpose> <ReferenceNumber>1741759</ReferenceNumber> <SkipDSTuNtcFlag>false</SkipDSTuNtcFlag> <SkipDSTuIDVisionFlag>true</SkipDSTuIDVisionFlag> </ApplicationData>]]]]>> </Field> <Field key="Applicants"> <![CDATA[<Applicants> <Applicant> <ApplicantType>Main</ApplicantType> <ApplicantFirstName>rishi</ApplicantFirstName> <DateOfBirth>16061988</DateOfBirth> <Gender>2</Gender> <Emails> <Email> <EmailId>rishi543ta88@gmail.com</EmailId> <EmailIdType>02</EmailIdType> </Email> </Emails> <Telephones> <Telephone> <TelephoneNumber>76434475257</TelephoneNumber> <TelephoneType>01</TelephoneType> </Telephone> </Telephones> <Identifiers> <Identifier> <IdNumber>AMRPG4334N</IdNumber> <IdType>01</IdType> </Identifier> </Identifiers> <Addresses> <Address> <AddressLine1>43434345 road</AddressLine1> <City>Mumbai West</City> <PinCode>4005080</PinCode> <AddressType>052</AddressType> <ResidenceType>502</ResidenceType> <StateCode>257</StateCode> </Address> </Addresses> </Applicant> </Applicants>]]]]>> </Field> </Fields> </DCRequest>','/Fields/Field/ReferenceNumber')
解决方案
首先获取 的所有子项<Field key="ApplicationData">
,然后搜索ReferenceNumber
。所以你需要这个:
SELECT EXTRACTVALUE(t, '//ReferenceNumber') AS ReferenceNumber FROM (
SELECT EXTRACTVALUE(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <DCRequest xmlns="http://hello.com/dc/extsvc"> <Authentication type="Ond"> <UserId>hello</UserId> <Password>345545</Password> </Authentication> <RequestInfo> <SolutionSetId>1617</SolutionSetId> <SolutionSetVersion>85</SolutionSetVersion> <ExecutionMode>NewWithContext</ExecutionMode> <EnvironmentId>1</EnvironmentId> </RequestInfo> <Fields> <Field key="ApplicationData"> <![CDATA[<ApplicationData> <SkipFlag>false</SkipFlag> <Purpose>05</Purpose> <ReferenceNumber>1741759</ReferenceNumber> <SkipDSTuNtcFlag>false</SkipDSTuNtcFlag> <SkipDSTuIDVisionFlag>true</SkipDSTuIDVisionFlag> </ApplicationData>]]]]>> </Field> <Field key="Applicants"> <![CDATA[<Applicants> <Applicant> <ApplicantType>Main</ApplicantType> <ApplicantFirstName>rishi</ApplicantFirstName> <DateOfBirth>16061988</DateOfBirth> <Gender>2</Gender> <Emails> <Email> <EmailId>rishi543ta88@gmail.com</EmailId> <EmailIdType>02</EmailIdType> </Email> </Emails> <Telephones> <Telephone> <TelephoneNumber>76434475257</TelephoneNumber> <TelephoneType>01</TelephoneType> </Telephone> </Telephones> <Identifiers> <Identifier> <IdNumber>AMRPG4334N</IdNumber> <IdType>01</IdType> </Identifier> </Identifiers> <Addresses> <Address> <AddressLine1>43434345 road</AddressLine1> <City>Mumbai West</City> <PinCode>4005080</PinCode> <AddressType>052</AddressType> <ResidenceType>502</ResidenceType> <StateCode>257</StateCode> </Address> </Addresses> </Applicant> </Applicants>]]]]>> </Field> </Fields> </DCRequest>',
'DCRequest/Fields/Field[1]'
) AS t
) AS t1
推荐阅读
- visual-studio-code - VS Code 中 Jupyter Notebook 的“内核因退出代码 1 而死”错误
- c++ - C++ 数组错误:“可变大小的对象可能未初始化”
- javascript - 如何在实时服务器而不是本地主机上使用 Tornado 实现 WebSocket
- amazon-web-services - 我没有在 Amazon Lex Bot 中看到“发布”按钮,而且我发现的所有教程屏幕截图都与我的 AWS 控制台不一致
- python - Quickfix Python 问题与心跳和订单事件之间的消息序列号
- dynamics-crm - 营销电子邮件中创建的动态内容关系是什么格式?(协助编辑)
- r - 如何在 R 中交叉引用 CSV 和 .txt 文件以查找重叠?
- java - 在 Spring Boot 应用程序中使用 Liquibase 时配置自定义数据源
- python - 如何在特定内核上启动并行进程?
- kubernetes - 如何解决错误:主机 nxdomain(不存在的域)的 epmd 错误?