Forum Stats

  • 3,837,425 Users
  • 2,262,256 Discussions
  • 7,900,277 Comments

Discussions

Categories

How to generate XPath string from XML file

New Roots
New Roots Member Posts: 184 Blue Ribbon
edited Aug 23, 2016 4:51AM in SQL & PL/SQL

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 "<span class="codeinlineitalic">XPath_string" manually.</span>

Is there any way to generate "<span class="codeinlineitalic">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.</span>

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 NULLAND key_ind_2   IS NOT NULLAND key_ind_3   IS NOT NULLAND key_ind_4   IS NOT NULLAND key_ind_5   IS NOT NULLAND 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 NULLAND key_ind_2   IS NOT NULLAND key_ind_3   IS NOT NULLAND key_ind_4   IS NOT NULLAND key_ind_5   IS NOT NULLAND key_ind_6   IS NOT NULLAND key_ind_7   IS NOT NULLAND key_ind_8   IS NOT NULLAND key_ind_9   IS NOT NULLAND key_ind_10  IS NOT NULLAND key_ind_11  IS NOT NULLAND key_ind_12  IS NOT NULLAND key_ind_13  IS NOT NULL;
Tagged:
New RootsBluShadowEtbinpadders
· Share on TwitterShare on Facebook
«13

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond
    edited Aug 18, 2016 5:27AM

    There are various ways of querying XML data, though perhaps it's going a bit far to expect Oracle to parse your XML data and determine the Xpaths you need (some XML data may leave out optional elements).  That is why we have XML Schemas to define what the XML should look like, and you can register XML Schemas with the database via XML DB, and use that, certainly to help improve performance when shredding an XML document (especially when you have large or lots of documents)

    For your information, using EXTRACTVALUE and TABLE(XMLSEQUENCE... is deprecated functionality.  Since 10g, there has been XMLTABLE and more recently XMLQUERY which can be used more effectively to extract data from XML (and it's easier to read).

    I would suggest you take a look over in the XML DB space which is dedicated to dealing with XML in the database.

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond
    edited Aug 18, 2016 5:39AM

    Example of using XMLTABLE...

    SQL> ed
    Wrote file afiedt.buf  1  with t(xml) as (select xmltype('
      2  <projects xmlns="http://www.oracle.com/somedefaultnamespace">
      3    <project>
      4      <projectNumber>311927</projectNumber>
      5      <projectType>BUILD</projectType>
      6      <lineOfBusiness>COMMERCIAL</lineOfBusiness>
      7      <projectStatus>PROGRASS</projectStatus>
      8      <summary>
      9        <creationDate>08/02/2016</creationDate>
     10        <workflowStateDate></workflowStateDate>
     11        <effectiveDate>01/01/2014</effectiveDate>
     12        <clientRequested>FALSE</clientRequested>
     13        <mandatoryReview>FALSE</mandatoryReview>
     14        <internalProject>FALSE</internalProject>
     15        <clientType>Permanent</clientType>
     16        <description>Test Data 2</description>
     17        <appliesTo>
     18          <Retail>TRUE</Retail>
     19          <Mail>TRUE</Mail>
     20        </appliesTo>
     21      </summary>
     22    </project>
     23    <project>
     24      <projectNumber>311928</projectNumber>
     25      <projectType>BUILD</projectType>
     26      <lineOfBusiness>INTERNAL</lineOfBusiness>
     27      <projectStatus>ON HOLD</projectStatus>
     28      <summary>
     29        <creationDate>01/06/2016</creationDate>
     30        <workflowStateDate></workflowStateDate>
     31        <effectiveDate>01/01/2015</effectiveDate>
     32        <clientRequested>FALSE</clientRequested>
     33        <mandatoryReview>FALSE</mandatoryReview>
     34        <internalProject>TRUE</internalProject>
     35        <clientType>Temporary</clientType>
     36        <description>Test Data 3</description>
     37        <appliesTo>
     38          <Retail>FALSE</Retail>
     39          <Mail>TRUE</Mail>
     40        </appliesTo>
     41      </summary>
     42    </project>
     43  </projects>') from dual)
     44  --
     45  -- end of test data
     46  --
     47  select x.*
     48  from   t
     49        ,xmltable(xmlnamespaces(default 'http://www.oracle.com/somedefaultnamespace')
     50                 ,'/projects/project'
     51                  passing t.xml
     52                  columns r for ordinality
     53                         ,projectNumber       number       path './projectNumber'
     54                         ,projectType         varchar2(10) path './projectType'
     55                         ,lineOfBusiness      varchar2(10) path './lineOfBusiness'
     56                         ,projectStatus       varchar2(10) path './projectStatus'
     57                         ,creationDate        varchar2(10) path './summary/creationDate'
     58                         ,workflowStateDate   varchar2(10) path './summary/worlflowStateDate'
     59                         ,effectiveDate       varchar2(10) path './summary/effectiveDate'
     60                         ,clientReq           varchar2(5)  path './summary/clientRequested'
     61                         ,mandatoryReview     varchar2(5)  path './summary/mandatoryReview'
     62                         ,internalProject     varchar2(5)  path './summary/internalProject'
     63                         ,clientType          varchar2(10) path './summary/clientType'
     64                         ,description         varchar2(20) path './summary/description'
     65                         ,appliesToRetail     varchar2(5)  path './summary/appliesTo/Retail'
     66                         ,appliesToMail       varchar2(5)  path './summary/appliesTo/Mail'
     67*                ) x
    SQL> /         R PROJECTNUMBER PROJECTTYP LINEOFBUSI PROJECTSTA CREATIONDA WORKFLOWST EFFECTIVED CLIEN MANDA INTER CLIENTTYPE DESCRIPTION          APPLI APPLI
    ---------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ----- ----- ----- ---------- -------------------- ----- -----
             1        311927 BUILD      COMMERCIAL PROGRASS   08/02/2016            01/01/2014 FALSE FALSE FALSE Permanent  Test Data 2          TRUE  TRUE
             2        311928 BUILD      INTERNAL   ON HOLD    01/06/2016            01/01/2015 FALSE FALSE TRUE  Temporary  Test Data 3          FALSE TRUE
    New RootsNew Roots
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Aug 18, 2016 5:50AM 
    Is there any way to generate "<span class="codeinlineitalic">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.</span>Eg output :  Tagvalue:                                  XpathlineOfBusiness                        /project[1]/lineOfBusiness/text()

    Yes, that's possible. But why would you need it?

    As BluShadow said, use XMLTABLE with static XQuery expressions. That's the best way to query XML documents.

    · Share on TwitterShare on Facebook
  • New Roots
    New Roots Member Posts: 184 Blue Ribbon
    edited Aug 18, 2016 5:56AM

    Hi Odie,

       Yes .I will have to look at BluShadow's suggestion. Can you please share the function which extracts the xpath string from  xml file? It could be helpful for me.

    · Share on TwitterShare on Facebook
  • New Roots
    New Roots Member Posts: 184 Blue Ribbon
    edited Aug 18, 2016 5:59AM

    Hi Blueshadow,

      Thanks for sharing the sample code. It sis very helpful . Is there any way to construct path automatically?

    · Share on TwitterShare on Facebook
  • New Roots
    New Roots Member Posts: 184 Blue Ribbon
    edited Aug 19, 2016 1:33AM

    Hi Odie_63,

       Great. Thank you very much for example.

    · Share on TwitterShare on Facebook
  • New Roots
    New Roots Member Posts: 184 Blue Ribbon
    edited Aug 19, 2016 5:27AM

    Hi @odie_63

    When I execute your first method below error message is raised. Please help me to resolve.

    ORA-19112: error raised during evaluation:

    XVM-01004: [XPTY0004] Expression type does not match a required type

    19112. 00000 -  "error raised during evaluation: %s"

    *Cause:    The error function was called during evaluation of the XQuery expression.

    *Action:   Check the detailed error message for the possible causes.

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond
    edited Aug 19, 2016 5:28AM

    What's your database version?

    · Share on TwitterShare on Facebook
  • New Roots
    New Roots Member Posts: 184 Blue Ribbon
    edited Aug 19, 2016 5:34AM

    Hi Blushadow,

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

    "CORE    11.2.0.1.0    Production"

    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

    · Share on TwitterShare on Facebook
This discussion has been closed.