oraclesql中xml字段的数据解析

bvhaajcl 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(427)

我希望使用sql提取oracle数据库中xml字段的一部分。我看了很多帖子，我认为我走上了正确的道路，也许只是我没有正确地确定正确的道路。我在跟踪指向字段的xml路径时遇到问题。我已经列出了我正在尝试的内容，但没有产生任何结果，并且我已经使xml看起来尽可能可读。在本例中，我需要从datetimereceived和datetimecompleted中提取年/月/日/小时/分/秒/毫秒。

SELECT 
stbl.EXT_TRANSACTIONIDTXT,  
stbl.EXT_RESPONSETEXT, 
xt.* 
FROM sample_table stbl, 
    xmltable('/ResponseEx/Response/TransactionDetailsEx/TransactionDetails'
    PASSING XMLTYPE(stbl.EXT_RESPONSETEXT)
        COLUMNS 
            Year  PATH 'DateTimeReceived/Year',
            Month  PATH 'DateTimeReceived/Month'
            )xt;

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><ResponseEx xmlns="urn:lnrisk:ws:testing:ruleplan@ver=1">
 <Response> 
 <Messages>
 <Message>
 <Type>G</Type>
 <Code>C6</Code>
 <Message>No Data Found.
 </Message>
 </Message>
 <Message>
 <Type>G</Type>
 <Message>ORDER NUMBER: AQF6LSL
 </Message>
 </Message>
 </Messages>
 <RequesterInformation>
 <Name>BIGBOB</Name>
 <AccountNumber>1111ABC</AccountNumber>
 </RequesterInformation>
 <TransactionDetailsEx>
 <TransactionDetails>
 <RuleplanId>111112</RuleplanId>
 <DateTimeReceived>
 <Year>2020</Year>
 <Month>5</Month>
 <Day>19</Day>
 <Hour24>12</Hour24>
 <Minute>58</Minute>
 <Second>17</Second>
 <MilliSeconds>317</MilliSeconds>
 </DateTimeReceived>
 <DateTimeCompleted>
 <Year>2020</Year>
 <Month>5</Month>
 <Day>19</Day>
 <Hour24>12</Hour24>
 <Minute>58</Minute>
 <Second>17</Second>
 <MilliSeconds>563</MilliSeconds>
 </DateTimeCompleted>
 <QuoteBacks/>
 </TransactionDetails>
 <ProcessingStatus>Complete with Errors</ProcessingStatus>
 <TransactionId>ABC895CL</TransactionId>
 </TransactionDetailsEx>
 <SearchBy>
 <Subjects/>
 <Vehicles>
 <InquiryVehicle vehicleId="VEH1">
 <PlateNumber>EEEE44444</PlateNumber>
 <PlateState>AB</PlateState>
 </InquiryVehicle></Vehicles>
 </SearchBy><Products>
 <ClaimsDataFill>
 <InquiryClaimsDataFill>
 <CarrierName>BIGBOB</CarrierName>
 <CarrierPolicyNumber>9999999999</CarrierPolicyNumber>
 <ClaimNumber>cc:98486965</ClaimNumber>
 <ClaimState>CA</ClaimState>
 <ParticipantNumber>001</ParticipantNumber>
 <DateofLoss><Year>2020</Year>
 <Month>5</Month>
 <Day>19</Day>
 </DateofLoss>
 <ParticipantType>Claimant</ParticipantType>
 <ParticipantRole>Owner</ParticipantRole>
 <SearchBy>
 <Vehicles>
 <Vehicle ref="VEH1">VEH1</Vehicle>
 </Vehicles>
 </SearchBy>
 </InquiryClaimsDataFill>
 </ClaimsDataFill>
 </Products>
 <ProductResults>
 <ClaimsDataFillResults/>
 </ProductResults>
 </Response>
 </ResponseEx>

sql xml oracle

来源：https://stackoverflow.com/questions/61920519/parse-data-from-xml-field-in-oracle-sql

1条答案

按热度按时间

5kgi1eie1#

xmlns="urn:lnrisk:ws:testing:ruleplan@ver=1" -xml没有标准的默认名称空间，要查询此数据，必须在xmltable语句中包含有关的信息。

... xmltable(xmlnamespaces( default 'urn:lnrisk:ws:testing:ruleplan@ver=1'), '/ResponseEx/Response/TransactionDetailsEx/TransactionDetails'...

SELECT 

xt.* 
FROM     xmltable(xmlnamespaces( default 'urn:lnrisk:ws:testing:ruleplan@ver=1'), '/ResponseEx/Response/TransactionDetailsEx/TransactionDetails'
    PASSING XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="yes"?><ResponseEx xmlns="urn:lnrisk:ws:testing:ruleplan@ver=1">
 <Response> 
 <Messages>
 <Message>
 <Type>G</Type>
 <Code>C6</Code>
 <Message>No Data Found.
 </Message>
 </Message>
 <Message>
 <Type>G</Type>
 <Message>ORDER NUMBER: AQF6LSL
 </Message>
 </Message>
 </Messages>
 <RequesterInformation>
 <Name>BIGBOB</Name>
 <AccountNumber>1111ABC</AccountNumber>
 </RequesterInformation>
 <TransactionDetailsEx>
 <TransactionDetails>
 <RuleplanId>111112</RuleplanId>
 <DateTimeReceived>
 <Year>2020</Year>
 <Month>5</Month>
 <Day>19</Day>
 <Hour24>12</Hour24>
 <Minute>58</Minute>
 <Second>17</Second>
 <MilliSeconds>317</MilliSeconds>
 </DateTimeReceived>
 <DateTimeCompleted>
 <Year>2020</Year>
 <Month>5</Month>
 <Day>19</Day>
 <Hour24>12</Hour24>
 <Minute>58</Minute>
 <Second>17</Second>
 <MilliSeconds>563</MilliSeconds>
 </DateTimeCompleted>
 <QuoteBacks/>
 </TransactionDetails>
 <ProcessingStatus>Complete with Errors</ProcessingStatus>
 <TransactionId>ABC895CL</TransactionId>
 </TransactionDetailsEx>
 <SearchBy>
 <Subjects/>
 <Vehicles>
 <InquiryVehicle vehicleId="VEH1">
 <PlateNumber>EEEE44444</PlateNumber>
 <PlateState>AB</PlateState>
 </InquiryVehicle></Vehicles>
 </SearchBy><Products>
 <ClaimsDataFill>
 <InquiryClaimsDataFill>
 <CarrierName>BIGBOB</CarrierName>
 <CarrierPolicyNumber>9999999999</CarrierPolicyNumber>
 <ClaimNumber>cc:98486965</ClaimNumber>
 <ClaimState>CA</ClaimState>
 <ParticipantNumber>001</ParticipantNumber>
 <DateofLoss><Year>2020</Year>
 <Month>5</Month>
 <Day>19</Day>
 </DateofLoss>
 <ParticipantType>Claimant</ParticipantType>
 <ParticipantRole>Owner</ParticipantRole>
 <SearchBy>
 <Vehicles>
 <Vehicle ref="VEH1">VEH1</Vehicle>
 </Vehicles>
 </SearchBy>
 </InquiryClaimsDataFill>
 </ClaimsDataFill>
 </Products>
 <ProductResults>
 <ClaimsDataFillResults/>
 </ProductResults>
 </Response>
 </ResponseEx>')
        COLUMNS 
            Year  PATH 'DateTimeReceived/Year',
            Month  PATH 'DateTimeReceived/Month'
            )xt;

赞(0）回复(0）举报 2021-08-09

我来回答

oraclesql中xml字段的数据解析

1条答案

相关问题

热门标签

最新问答