CODEWITHSUNDEEP
xmloracle-databaseextractclob
Marianne
Marianne

Reputation: 43

ExtractValue from Oracle CLOB returning null

I am new to XML and Clobs. I have read plenty of the posts concerning the subject. I am trying to extract the studentID value from this clob. I am using Oracle 11.2

<OcXml xmlns="http://oc.csr.com/xml">
  <OcSharedXml>
    <Templates>
      <Template ns:TemplateId="130" ns:TemplateName="Student Graduation Fees" ns:TemplateFilename="Student_Graduation_Fees_1.doc" ns:TemplateSequence="1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="http://oc.rsi.com/xml">
        <Sections>
          <Section ns:SectionId="146" ns:SectionName="Main" ns:SectionSequence="1" ns:MaxRepeat="1">
            <Elements>
              <Element ns:ElementId="134" ns:ElementName="CurrentDt" ns:FieldName="DateOfNotice" ns:DefaultValue="" ns:Editable="false" ns:ElementSequence="1" ns:ElementType="ElmProc" ns:Mandatory="false" ns:Shared="true" ns:DataType="String" ns:Format="NoFmt" ns:RecipientId="" ns:Multiline="false" ns:RecipientAddressFlag="false">
                <ElementValue>2/19/2015</ElementValue>
              </Element>
              <Element ns:ElementId="24" ns:ElementName="InvoiceNo" ns:FieldName="InvoiceNo" ns:DefaultValue="" ns:Editable="true" ns:ElementSequence="2" ns:ElementType="ElmProc" ns:Mandatory="false" ns:Shared="true" ns:DataType="String" ns:Format="NoFmt" ns:RecipientId="" ns:Multiline="false" ns:RecipientAddressFlag="false">
                <ElementValue>352</ElementValue>
              </Element>
              <Element ns:ElementId="16" ns:ElementName="StudentID" ns:FieldName="StudentID" ns:DefaultValue="" ns:Editable="true" ns:ElementSequence="3" ns:ElementType="ElmProc" ns:Mandatory="false" ns:Shared="true" ns:DataType="String" ns:Format="NoFmt" ns:RecipientId="" ns:Multiline="false" ns:RecipientAddressFlag="false">
                <ElementValue>557481300</ElementValue>
              </Element>
              <Element ns:ElementId="43" ns:ElementName="DegreeType" ns:FieldName="DegreeType" ns:DefaultValue="" ns:Editable="false" ns:ElementSequence="4" ns:ElementType="ElmProc" ns:Mandatory="false" ns:Shared="true" ns:DataType="String" ns:Format="NoFmt" ns:RecipientId="" ns:Multiline="false" ns:RecipientAddressFlag="false">
                <ElementValue>BS</ElementValue>
              </Element>
              <Element ns:ElementId="44" ns:ElementName="GraduationYear" ns:FieldName="GraduationYear" ns:DefaultValue="" ns:Editable="false" ns:ElementSequence="5" ns:ElementType="ElmProc" ns:Mandatory="false" ns:Shared="true" ns:DataType="String" ns:Format="NoFmt" ns:RecipientId="" ns:Multiline="false" ns:RecipientAddressFlag="false">
                <ElementValue>2015</ElementValue>
              </Element>
            </Elements>
          </Section>
        </Sections>
      </Template>
    </Templates>
  </OcSharedXml>
</OcXml>

This is my select statement. It returns nulls

SELECT EXTRACTVALUE(xmltype(clob_column), '/Templates/Template/Sections/Elements/Element[@ElementName="StudentID"]') FROM student_table;

Thanks!

Upvotes: 1

Views: 3036

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You haven't given all the nodes in the path - you are missing OcXml, OcSharedXml and Section, as well as ElementValue - but you also need to provide the namespace information since the attribute you're looking for is called ns:ElementName:

SELECT EXTRACTVALUE(xmltype(clob_column),
  '/OcXml/OcSharedXml/Templates/Template/Sections/Section/Elements/Element[@ns:ElementName="StudentID"]/ElementValue',
  'xmlns="http://oc.csr.com/xml" xmlns:ns="http://oc.rsi.com/xml"')
FROM student_table;

EXTRACTVALUE(XMLTYPE(CLOB_COLUMN),'/OCXML/OCSHAREDXML/TEMPLATES/TEMPLATE/SECTION
--------------------------------------------------------------------------------
557481300

Or simplifying/wildcarding the path:

SELECT EXTRACTVALUE(xmltype(clob_column),
  '//Element[@ns:ElementName="StudentID"]/ElementValue',
  'xmlns="http://oc.csr.com/xml" xmlns:ns="http://oc.rsi.com/xml"')
FROM student_table;

EXTRACTVALUE(XMLTYPE(CLOB_COLUMN),'//ELEMENT[@NS:ELEMENTNAME="STUDENTID"]/ELEMEN
--------------------------------------------------------------------------------
557481300

Upvotes: 5

Related Questions