SQL & PL/SQL

Hi all

I am using the below Oracle edition

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

i have the below XML and i need to get values of some nodes listed in select query

WITH testXml as

( Select XMLTYPE('<?xml version="1.0" encoding="utf-16"?>

<FranchiseTaxPaymentRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <ClientAccountNum>0</ClientAccountNum>

<PacketNum>9000011_20170928T1301264701</PacketNum>

  <TransType>NA</TransType>

<agentPONumber>B2B-01000-70731914-WO-XXX-4-00</agentPONumber>

  <attentionLine xsi:nil="true" />

  <returnUri xsi:nil="true" />

  <secureToken xsi:nil="true" />

  <franchiseTaxPaymentDocument>

    <franchiseTaxPayment xmlns="http://schemas.datacontract.org/2004/07/Delaware.ICIS.XmlFiling.Types">

<seqNumber>1</seqNumber>

      <attachment xsi:nil="true" />

<fileNumber>6191659</fileNumber>

<reportYear>2016</reportYear>

<amount>530.00</amount>

    </franchiseTaxPayment>

  </franchiseTaxPaymentDocument>

</FranchiseTaxPaymentRequest>') as SD

FROM DUAL

)

Select  EXTRACT(SD,'/FranchiseTaxPaymentRequest

/franchiseTaxPaymentDocument/franchiseTaxPayment/seqNumber/text()

').getStringVal() seqNumber

FROM testXml

i am getting null value

Need to get values for <seqNumber>, fileNumber, reportYear and Amount.

i have found that if after  <franchiseTaxPayment>  node  ,<xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

is appended then we are able to get the output.

Does the xml stored is in wrong format.

the XML data is stored in table with datatype CLOB.

Please assist.

Thanks

Aj.