Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Billy Verreynne

Simple answer - no.

The machine name (and other client details) is set by the client driver. Not the client application.

BTW, why this requirement?

1 person found this helpful
1009739

I am working in ORACLE 11 g Release 2 using PL/SQL Developer Tool

Every time i am logging in i could find my machine name in OSUSER in v$session table.

So i want to stop is there any other tool or any other way to stop it

JustinCave
Answer

Why do you want to prevent the DBA from determining what machine you are connected from?

Given that this information is coming from your client machine, it is certainly possible to hack your machine so that it reports a different name assuming you have root/ Administrator access.  But that's something that an attacker would normally want to do, not something that a normal user would want to do.  That's why we're trying to determine why you are trying to hide this information.

Justin    

Marked as Answer by 1009739 · Sep 27 2020
1009739

See when i am accessing via guest account it take the name into it. So i don't want to insert guest name instead it must enter my machine name or null.

Billy Verreynne

So. What?

What is The Problem?

If you want to obfuscate what the OCI client driver reports to the server, you will need some low level hacks that are not easy and trivial. So why do you want to tackle such a hack complexity, instead of just doing your job via that database connection?

1 person found this helpful
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 2 2020
Added on Aug 18 2016
#sql, #xml
21 comments
3,412 views
1 mention