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;