Skip to Main Content

SQL & PL/SQL

How to generate XPath string from XML file

New RootsAug 18 2016 — edited Aug 23 2016

Hi,

   Below is my XML format:

<project>

    <projectNumber>311927</projectNumber>

    <projectType>BUILD</projectType>

    <lineOfBusiness>COMMERCIAL</lineOfBusiness>

    <projectStatus>PROGRASS</projectStatus>

    <summary>

      <creationDate>08/02/2016</creationDate>

      <workflowStateDate></workflowStateDate>

      <effectiveDate>01/01/2014</effectiveDate>

      <clientRequested>FALSE</clientRequested>

      <mandatoryReview>FALSE</mandatoryReview>

      <internalProject>FALSE</internalProject>

      <clientType>Permanent</clientType>

      <description>Test Data 2</description>

      <appliesTo>

        <Retail>TRUE</Retail>

        <Mail>TRUE</Mail>

      </appliesTo>

    </summary>

</project>   

I'm loading above xml in one oracle configuration table  and querying by using below query. As of now I'm passing "XPath_string" manually.

Is there any way to generate "XPath_string" from xml file by using any inbuilt oracle function?  That function should read my xml and should produce xpath string for each tag value.

Eg output : 

Tagvalue:                                   Xpath

lineOfBusiness                        /project[1]/lineOfBusiness/text()

SELECT *

FROM

  (SELECT 1924901                                                                     AS "KEY_IND_1" ,

    1924801                                                                           AS "KEY_IND_2" ,

    EXTRACTVALUE(VALUE(P),'/project[1]/lineOfBusiness/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_3" ,

    EXTRACTVALUE(VALUE(P),'/project[1]/projectNumber/text()', 'xmlns:"RXCONSTRUCT"')  AS "KEY_IND_4" ,

    EXTRACTVALUE(VALUE(P),'/project[1]/projectStatus/text()', 'xmlns:"RXCONSTRUCT"')  AS "KEY_IND_5" ,

    EXTRACTVALUE(VALUE(P),'/project[1]/projectType/text()', 'xmlns:"RXCONSTRUCT"')    AS "KEY_IND_6"

  FROM TABLE (XMLSEQUENCE(

    (SELECT NVL(xml_clob,'') FROM input_files WHERE extract_level = 1

    ) )) P

  )

WHERE key_ind_1 IS NOT NULL

AND key_ind_2   IS NOT NULL

AND key_ind_3   IS NOT NULL

AND key_ind_4   IS NOT NULL

AND key_ind_5   IS NOT NULL

AND key_ind_6   IS NOT NULL;

SELECT *

FROM

  (SELECT 1925001 AS "KEY_IND_1" ,

    (SELECT MAX(project_id)

    FROM XML_USER.project

    WHERE file_seq_id = 1924801

    )                                                                                    AS "KEY_IND_2" ,

    1924801                                                                              AS "KEY_IND_3" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/clientRequested/text()', 'xmlns:"RXCONSTRUCT"')   AS "KEY_IND_4" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/clientType/text()', 'xmlns:"RXCONSTRUCT"')        AS "KEY_IND_5" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/creationDate/text()', 'xmlns:"RXCONSTRUCT"')      AS "KEY_IND_6" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/description/text()', 'xmlns:"RXCONSTRUCT"')       AS "KEY_IND_7" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/effectiveDate/text()', 'xmlns:"RXCONSTRUCT"')     AS "KEY_IND_8" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/internalProject/text()', 'xmlns:"RXCONSTRUCT"')   AS "KEY_IND_9" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/mandatoryReview/text()', 'xmlns:"RXCONSTRUCT"')   AS "KEY_IND_10" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/workflowStateDate/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_11" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/appliesTo/Retail/text()', 'xmlns:"RXCONSTRUCT"')  AS "KEY_IND_12" ,

    EXTRACTVALUE(VALUE(P),'/summary[1]/appliesTo/Mail/text()', 'xmlns:"RXCONSTRUCT"')    AS "KEY_IND_13"

  FROM TABLE (XMLSEQUENCE(

    (SELECT NVL(xml_clob,'') FROM input_files WHERE extract_level = 1

    ) )) P

  )

WHERE key_ind_1 IS NOT NULL

AND key_ind_2   IS NOT NULL

AND key_ind_3   IS NOT NULL

AND key_ind_4   IS NOT NULL

AND key_ind_5   IS NOT NULL

AND key_ind_6   IS NOT NULL

AND key_ind_7   IS NOT NULL

AND key_ind_8   IS NOT NULL

AND key_ind_9   IS NOT NULL

AND key_ind_10  IS NOT NULL

AND key_ind_11  IS NOT NULL

AND key_ind_12  IS NOT NULL

AND key_ind_13  IS NOT NULL;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 2 2020
Added on Aug 18 2016
21 comments
2,512 views