Forum Stats

  • 3,855,696 Users
  • 2,264,544 Discussions
  • 7,906,141 Comments

Discussions

Hi All, I have a XML file and i am trying to get the field elements but unable to get it. It says xp

User_KIXOP
User_KIXOP Member Posts: 8 Red Ribbon
edited Aug 4, 2017 8:06AM in XQuery

Hi All, I have a XML file and i am trying to get the field elements but unable to get it. It says xpath not found.

XML file is like below

<ns0:LoadCardTransactions xmlns:ns1="http://www.pp.com/ifcs">

<ns1:Header>

           <ns2:FileName xmlns:ns2="http://www.pp.com/core">CardTransBase24_AU_0357.xml</ns2:FileName>

           <ns2:SequenceNumber xmlns:ns2="http://www.pp.com/core">0357</ns2:SequenceNumber>

           <ns2:RecordCount xmlns:ns2="http://www.pp.com/core">63</ns2:RecordCount>

           <ns2:CountryCode xmlns:ns2="http://www.pp.com/core">AU</ns2:CountryCode>

           <ns2:StartDate xmlns:ns2="http://www.pp.com/core">2017-07-20</ns2:StartDate>

           <ns2:StartTime xmlns:ns2="http://www.pp.com/core">00:00:45</ns2:StartTime>

           <ns2:Identifier xmlns:ns2="http://www.pp.com/core">Y</ns2:Identifier>

           <ns2:ExternalSupplier xmlns:ns2="http://www.pp.com/core">pp AU</ns2:ExternalSupplier>

           <ns2:SettlementDate xmlns:ns2="http://www.pp.com/core">2017-07-19</ns2:SettlementDate>

</ns1:Header>

</ns0:LoadCardTransactions>

I am trying to get FileName , but it says xpath not found.. I am using Oracle 12c.

   SELECT

    req.id,

    extractValue(

        req.content,

    '/ns1:LoadCardTransactions/ns1:Header/ns2:FileName/','xmlns:ns1="http://www.pp.com/ifcs" xmlns:ns2="http://www.pp.com/core"') Title

FROM

    SAP_PRICE_FILE_TBL1 req

Please help.

Best Answer

  • cormaco
    cormaco Member Posts: 1,991 Silver Crown
    edited Aug 3, 2017 10:13AM Answer ✓

    Hi there,

    I did the following to get the required result:

    Renamed ns0:LoadCardTransactions in your example to ns1:LoadCardTransactions.

    Removed the trailing slash from '/ns1:LoadCardTransactions/ns1:Header/ns2:FileName/'

    WITH req(CONTENT) AS     (SELECT xmltype('<ns1:LoadCardTransactions xmlns:ns1="http://www.pp.com/ifcs"><ns1:Header>           <ns2:FileName xmlns:ns2="http://www.pp.com/core">CardTransBase24_AU_0357.xml</ns2:FileName>           <ns2:SequenceNumber xmlns:ns2="http://www.pp.com/core">0357</ns2:SequenceNumber>           <ns2:RecordCount xmlns:ns2="http://www.pp.com/core">63</ns2:RecordCount>           <ns2:CountryCode xmlns:ns2="http://www.pp.com/core">AU</ns2:CountryCode>           <ns2:StartDate xmlns:ns2="http://www.pp.com/core">2017-07-20</ns2:StartDate>           <ns2:StartTime xmlns:ns2="http://www.pp.com/core">00:00:45</ns2:StartTime>           <ns2:Identifier xmlns:ns2="http://www.pp.com/core">Y</ns2:Identifier>           <ns2:ExternalSupplier xmlns:ns2="http://www.pp.com/core">pp AU</ns2:ExternalSupplier>           <ns2:SettlementDate xmlns:ns2="http://www.pp.com/core">2017-07-19</ns2:SettlementDate></ns1:Header></ns1:LoadCardTransactions>')FROM dual)SELECT EXTRACTVALUE(        req.content,    '/ns1:LoadCardTransactions/ns1:Header/ns2:FileName','xmlns:ns1="http://www.pp.com/ifcs" xmlns:ns2="http://www.pp.com/core"') TitleFROM reqResult:CardTransBase24_AU_0357.xml

    Please note that extractvalue is deprecated and XMLQUERY should be used instead.

Answers

This discussion has been closed.