mysql - 如何使用mysql提取值函数从xml中检索数据

Question

我想检索 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')

Answer 1

首先获取 <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