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!

Matrix query

Arif2018Aug 12 2021

i want to list our all possible values in matrix format. i have a table with following values with one specific column having distinct values (company ,org_code,site) based on which i want to group the rows values (code_part,code_part_val) as columns, i am not sure if it is possible.

[code]
CREATE TABLE POST_DATA(COMP VARCHAR2(12),CODE_PART VARCHAR2(5),ORG_CODE VARCHAR2(12), SITE VARCHAR2(5), CODE_PART_VAL VARCHAR2(20))

INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','CS','CE01','CCS1-C02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','ES','CE01','CES1-C02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','B','FS','CE01','CFS1-C02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C03','B','FS','CME01','CMES1-C03');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','CS','CE01','CHM02');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','ES','CE01','SER04');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C02','C','FS','CE01','SER01');
INSERT INTO POST_DATA(COMP ,CODE_PART ,ORG_CODE , SITE , CODE_PART_VAL ) VALUES ('C03','C','FS','CME01','CMES01');

--needed output based on distinct column code_part

C02, B,C,'CS',CCS1-C02,CHM02
C02, B,C,'ES',CES1-C02,SER04
C02, B,C,'ES',CFS1-C02,SER01
C03, B,C,'FS',CMES1-C03,CMES01

[/CODE]
This post has been answered by Jan Gorkow on Sep 4 2021
Jump to Answer

Comments

BluShadow

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.

BluShadow

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
1 person found this helpful
odie_63 
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()

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.

New Roots

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.

New Roots

Hi Blueshadow,

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

odie_63 
Can you please share the function which extracts the xpath string from  xml file?

There's no documented function for that - probably because it's not a good idea

From an academic standpoint, here are a couple of ways to do it :

create table tmp_xml of xmltype;
insert into tmp_xml values (
xmltype('<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>')
);

1) Using XQuery only :

SQL> select x.node_id
  2       , x.node_name
  3       , x.node_type
  4       , x.node_xpath
  5  from tmp_xml t
  6     , xmltable(
  7       'declare function local:getChildren($e as node(), $pid as xs:string?, $pxp as xs:string?) as element()*
  8        {
  9          for $i at $p in $e/(node()|@*)
 10          let $id := if ($pid) then concat($pid,".",$p) else "1"
 11          let $xp := concat($pxp, "/",
 12                       typeswitch($i)
 13                         case text()      return "text()"
 14                         case attribute() return concat("@", local-name($i))
 15                         case element()   return concat(local-name($i), "[", count($i/preceding-sibling::*[name(.)=name($i)])+1, "]")
 16                         default          return ""
 17                     )
 18          return element r
 19          {
 20            element node_id {$id}
 21          , element parent_node_id {$pid}
 22          , element node_name {local-name($i)}
 23          , element node_value { if (not($i instance of element())) then data($i) else () }
 24          , element node_type {
 25              typeswitch($i)
 26                case text()                   return "text"
 27                case attribute()              return "attribute"
 28                case element()                return "element"
 29                case processing-instruction() return "processing-instruction"
 30                case comment()                return "comment"
 31                default                       return "other"
 32            }
 33          , element namespace_uri {namespace-uri($i)}
 34          , element node_xpath {$xp}
 35          }
 36          | local:getChildren($i, $id, $xp)
 37        }; (: :)
 38        local:getChildren($d,(),())'
 39        passing t.object_value as "d"
 40        columns node_id         varchar2(100)   path 'node_id'
 41              , node_name       varchar2(2000)  path 'node_name'
 42              , node_value      varchar2(4000)  path 'node_value'
 43              , parent_node_id  varchar2(100)   path 'parent_node_id'
 44              , node_type       varchar2(30)    path 'node_type'
 45              , namespace_uri   varchar2(2000)  path 'namespace_uri'
 46              , node_xpath      varchar2(4000)  path 'node_xpath'
 47       ) x
 48  ;
NODE_ID     NODE_NAME          NODE_TYPE  NODE_XPATH
----------- ------------------ ---------- ----------------------------------------------------
1.5.1       creationDate       element    /project[1]/summary[1]/creationDate[1]
1.5         summary            element    /project[1]/summary[1]
1.4.1                          text       /project[1]/projectStatus[1]/text()
1.4         projectStatus      element    /project[1]/projectStatus[1]
1.3.1                          text       /project[1]/lineOfBusiness[1]/text()
1.3         lineOfBusiness     element    /project[1]/lineOfBusiness[1]
1.2.1                          text       /project[1]/projectType[1]/text()
1.5.9.2.1                      text       /project[1]/summary[1]/appliesTo[1]/Mail[1]/text()
1.5.9.2     Mail               element    /project[1]/summary[1]/appliesTo[1]/Mail[1]
1.5.9.1.1                      text       /project[1]/summary[1]/appliesTo[1]/Retail[1]/text()
1.5.9.1     Retail             element    /project[1]/summary[1]/appliesTo[1]/Retail[1]
1.5.9       appliesTo          element    /project[1]/summary[1]/appliesTo[1]
1.5.8.1                        text       /project[1]/summary[1]/description[1]/text()
1.5.8       description        element    /project[1]/summary[1]/description[1]
1.1         projectNumber      element    /project[1]/projectNumber[1]
1           project            element    /project[1]
1.5.7.1                        text       /project[1]/summary[1]/clientType[1]/text()
1.5.7       clientType         element    /project[1]/summary[1]/clientType[1]
1.5.6.1                        text       /project[1]/summary[1]/internalProject[1]/text()
1.5.6       internalProject    element    /project[1]/summary[1]/internalProject[1]
1.5.5.1                        text       /project[1]/summary[1]/mandatoryReview[1]/text()
1.5.5       mandatoryReview    element    /project[1]/summary[1]/mandatoryReview[1]
1.5.4.1                        text       /project[1]/summary[1]/clientRequested[1]/text()
1.5.4       clientRequested    element    /project[1]/summary[1]/clientRequested[1]
1.5.3.1                        text       /project[1]/summary[1]/effectiveDate[1]/text()
1.5.3       effectiveDate      element    /project[1]/summary[1]/effectiveDate[1]
1.5.2       workflowStateDate  element    /project[1]/summary[1]/workflowStateDate[1]
1.5.1.1                        text       /project[1]/summary[1]/creationDate[1]/text()
1.2         projectType        element    /project[1]/projectType[1]
1.1.1                          text       /project[1]/projectNumber[1]/text()
30 rows selected.

2) Using XMLFlattenDOM function :

SQL> with edge_data as (
  2    select x.*
  3         , row_number() over(partition by parent_node_id, node_name order by node_id) as position
  4    from tmp_xml t
  5       , table(xmlflattendom(t.object_value)) x
  6  )
  7  select t.node_id
  8       , t.node_name
  9       , sys_connect_by_path(
 10           case node_type
 11             when 'element' then node_name || '[' || position || ']'
 12             when 'attribute' then '@' || node_name
 13             else 'text()'
 14           end
 15         , '/'
 16         ) as xpath
 17  from edge_data t
 18  where node_type in ('element', 'attribute', 'text')
 19  connect by prior node_id = parent_node_id
 20  start with parent_node_id is null ;
   NODE_ID NODE_NAME          XPATH
---------- ------------------ ----------------------------------------------------
         1 project            /project[1]
         2 projectNumber      /project[1]/projectNumber[1]
         3                    /project[1]/projectNumber[1]/text()
         4 projectType        /project[1]/projectType[1]
         5                    /project[1]/projectType[1]/text()
         6 lineOfBusiness     /project[1]/lineOfBusiness[1]
         7                    /project[1]/lineOfBusiness[1]/text()
         8 projectStatus      /project[1]/projectStatus[1]
         9                    /project[1]/projectStatus[1]/text()
        10 summary            /project[1]/summary[1]
        11 creationDate       /project[1]/summary[1]/creationDate[1]
        12                    /project[1]/summary[1]/creationDate[1]/text()
        13 workflowStateDate  /project[1]/summary[1]/workflowStateDate[1]
        14 effectiveDate      /project[1]/summary[1]/effectiveDate[1]
        15                    /project[1]/summary[1]/effectiveDate[1]/text()
        16 clientRequested    /project[1]/summary[1]/clientRequested[1]
        17                    /project[1]/summary[1]/clientRequested[1]/text()
        18 mandatoryReview    /project[1]/summary[1]/mandatoryReview[1]
        19                    /project[1]/summary[1]/mandatoryReview[1]/text()
        20 internalProject    /project[1]/summary[1]/internalProject[1]
        21                    /project[1]/summary[1]/internalProject[1]/text()
        22 clientType         /project[1]/summary[1]/clientType[1]
        23                    /project[1]/summary[1]/clientType[1]/text()
        24 description        /project[1]/summary[1]/description[1]
        25                    /project[1]/summary[1]/description[1]/text()
        26 appliesTo          /project[1]/summary[1]/appliesTo[1]
        27 Retail             /project[1]/summary[1]/appliesTo[1]/Retail[1]
        28                    /project[1]/summary[1]/appliesTo[1]/Retail[1]/text()
        29 Mail               /project[1]/summary[1]/appliesTo[1]/Mail[1]
        30                    /project[1]/summary[1]/appliesTo[1]/Mail[1]/text()
30 rows selected.
5 people found this helpful
New Roots

Hi Odie_63,

   Great. Thank you very much for example.

New Roots

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.

BluShadow

What's your database version?

New Roots

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

BluShadow

Odie's code works fine for me on 11.2.0.3..  (I don't have 11.2.0.1 to test)

SQL> ed
Wrote file afiedt.buf
  1  select x.node_id
  2        , x.node_name
  3        , x.node_type
  4        , x.node_xpath
  5   from tmp_xml t
  6      , xmltable(
  7        'declare function local:getChildren($e as node(), $pid as xs:string?, $pxp as xs:string?) as element()*
  8         {
  9           for $i at $p in $e/(node()|@*)
 10           let $id := if ($pid) then concat($pid,".",$p) else "1"
 11           let $xp := concat($pxp, "/",
 12                        typeswitch($i)
 13                          case text()      return "text()"
 14                          case attribute() return concat("@", local-name($i))
 15                          case element()   return concat(local-name($i), "[", count($i/preceding-sibling::*[name(.)=name($i)])+1, "]")
 16                          default          return ""
 17                      )
 18           return element r
 19           {
 20             element node_id {$id}
 21           , element parent_node_id {$pid}
 22           , element node_name {local-name($i)}
 23           , element node_value { if (not($i instance of element())) then data($i) else () }
 24           , element node_type {
 25               typeswitch($i)
 26                 case text()                   return "text"
 27                 case attribute()              return "attribute"
 28                 case element()                return "element"
 29                 case processing-instruction() return "processing-instruction"
 30                 case comment()                return "comment"
 31                 default                       return "other"
 32             }
 33           , element namespace_uri {namespace-uri($i)}
 34           , element node_xpath {$xp}
 35           }
 36           | local:getChildren($i, $id, $xp)
 37         }; (: :)
 38         local:getChildren($d,(),())'
 39         passing t.object_value as "d"
 40         columns node_id         varchar2(100)   path 'node_id'
 41               , node_name       varchar2(2000)  path 'node_name'
 42               , node_value      varchar2(4000)  path 'node_value'
 43               , parent_node_id  varchar2(100)   path 'parent_node_id'
 44               , node_type       varchar2(30)    path 'node_type'
 45               , namespace_uri   varchar2(2000)  path 'namespace_uri'
 46               , node_xpath      varchar2(4000)  path 'node_xpath'
 47*        ) x
 48  /
NODE_ID         NODE_NAME                      NODE_TYPE                      NODE_XPATH
--------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------
1.1             projectNumber                  element                        /project[1]/projectNumber[1]
1.3.1                                          text                           /project[1]/lineOfBusiness[1]/text()
1.1.1                                          text                           /project[1]/projectNumber[1]/text()
1               project                        element                        /project[1]
1.3             lineOfBusiness                 element                        /project[1]/lineOfBusiness[1]
1.2.1                                          text                           /project[1]/projectType[1]/text()
1.5.9.2         Mail                           element                        /project[1]/summary[1]/appliesTo[1]/Mail[1]
1.5.9.1.1                                      text                           /project[1]/summary[1]/appliesTo[1]/Retail[1]/text()
1.5.9.1         Retail                         element                        /project[1]/summary[1]/appliesTo[1]/Retail[1]
1.5.9           appliesTo                      element                        /project[1]/summary[1]/appliesTo[1]
1.5.8.1                                        text                           /project[1]/summary[1]/description[1]/text()
1.5.8           description                    element                        /project[1]/summary[1]/description[1]
1.5.7.1                                        text                           /project[1]/summary[1]/clientType[1]/text()
1.5.7           clientType                     element                        /project[1]/summary[1]/clientType[1]
1.5.6.1                                        text                           /project[1]/summary[1]/internalProject[1]/text()
1.5.6           internalProject                element                        /project[1]/summary[1]/internalProject[1]
1.5.5.1                                        text                           /project[1]/summary[1]/mandatoryReview[1]/text()
1.2             projectType                    element                        /project[1]/projectType[1]
1.5.5           mandatoryReview                element                        /project[1]/summary[1]/mandatoryReview[1]
1.5.4.1                                        text                           /project[1]/summary[1]/clientRequested[1]/text()
1.5.4           clientRequested                element                        /project[1]/summary[1]/clientRequested[1]
1.5.3.1                                        text                           /project[1]/summary[1]/effectiveDate[1]/text()
1.5.3           effectiveDate                  element                        /project[1]/summary[1]/effectiveDate[1]
1.5.2           workflowStateDate              element                        /project[1]/summary[1]/workflowStateDate[1]
1.5.1.1                                        text                           /project[1]/summary[1]/creationDate[1]/text()
1.5.1           creationDate                   element                        /project[1]/summary[1]/creationDate[1]
1.5             summary                        element                        /project[1]/summary[1]
1.4.1                                          text                           /project[1]/projectStatus[1]/text()
1.4             projectStatus                  element                        /project[1]/projectStatus[1]
1.5.9.2.1                                      text                           /project[1]/summary[1]/appliesTo[1]/Mail[1]/text()
30 rows selected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
padders

I think this is pretty useful when analysing XML if you don't have XMLSpy or equivalent to hand where you can just hover over elements and get their xpath.

I added an attribute to the projectNumber node to test and I notice your first query returns the attribute before its element, was that intentional or is it non-deterministic if you use node()|@*?

odie_63 
I think this is pretty useful when analysing XML if you don't have XMLSpy or equivalent to hand where you can just hover over elements and get their xpath.

Yes, why not.

But for this usage, it lacks a proper namespace support. Not difficult to add though.

The design of this query (and of other flattening techniques of the same kind) primarily came after a discussion about the Edge Model, a schema-less storage model for XML.

This model is the basis for building simple XML indexes.

Unstructured XML indexes in Oracle are implemented differently though, with a PATH table and an internal token table, but the idea is similar.

I added an attribute to the projectNumber node to test and I notice your first query returns the attribute before its element, was that intentional or is it non-deterministic if you use node()|@*?

Do you mean this :

NODE_ID  NODE_NAME      NODE_TYPE  NODE_XPATH
-------- -------------- ---------- ------------------------------------
1        project        element    /project[1]
1.1.2                   text       /project[1]/projectNumber[1]/text()
1.1.1    id             attribute  /project[1]/projectNumber[1]/@id
1.1      projectNumber  element    /project[1]/projectNumber[1]

?

On my db (12.1.0.2), subsequent runs of the query give a different row order every single time.

NODE_IDs show that nodes are processed in a deterministic way, though the XQuery VM is apparently free to return rows in any order.

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

Are you testing using the sample XML document from your first post?

Since you're on 11.2.0.1, you're probably not using Binary XML storage?

In that version, the CBO will probably try to rewrite the XQuery expression using internal functions, which sometimes doesn't work that well.

Try to force functional evaluation via the NO_XML_QUERY_REWRITE hint :

SELECT /*+ no_xml_query_rewrite */
       ...
FROM ...
padders

> Do you mean this

Yes, I was observing that attribute 1.1.1 was numbered correctly as a child of element 1.1 (to my thinking) but appeared before element 1.1.

New Roots

Hi Blushadow,

Excuse for delayed response. when I execute the same in Oracle live sql window ,It is working fine. But I execute the same query in my database version I got the error message which i mentioned in earlier post. How can I run this with DB version 11.2.0.1

1.png

New Roots

Hi Odie_63,

Are you testing using the sample XML document from your first post?

Yes. I used same XML document.

Try to force functional evaluation via the NO_XML_QUERY_REWRITE hint :

SELECT /*+ no_xml_query_rewrite */ 
       ... 
FROM ... 

I tried to use the above hint in my select statement. But no luck.

odie_63

Try the 2nd query then. It'll be more efficient anyway.

New Roots

Hi Odie_63,

  I tried to implement your second approach. My original xml file have lot of nodes. some nodes have same field names.  I want to insert field values into different tables. For instance the project node fields should be stored in the project table in addition to primary key column.

Summary and appliesTo node tag fields should be stored in the summary table.

project table  structure :

------------------------------------------------------------------

PROJECT_IDNOT NULL NUMBER
FILE_SEQ_ID     NUMBER
LINEOFBUSINESS VARCHAR2(200)
PROJECTNUMBER   VARCHAR2(200)
PROJECTSTATUS   VARCHAR2(200)
PROJECTTYPE     VARCHAR2(200)

desc summary
Name              Null    Type         
----------------- -------- -------------
SUMMARY_ID        NOT NULL NUMBER       
PROJECT_ID                NUMBER       
FILE_SEQ_ID                NUMBER       
CLIENTREQUESTED            VARCHAR2(200)
CLIENTTYPE                VARCHAR2(200)
CREATIONDATE              VARCHAR2(200)
DESCRIPTION                VARCHAR2(200)
EFFECTIVEDATE              VARCHAR2(200)
INTERNALPROJECT            VARCHAR2(200)
MANDATORYREVIEW            VARCHAR2(200)
WORKFLOWSTATEDATE          VARCHAR2(200)
APPLIESTO_MAIL            VARCHAR2(10) 
APPLIESTO_RETAIL          VARCHAR2(10)

My approach :

Inserting  fieldname, parent tag name, field order ( column order in the actual tables) ,xpath informations in a temporary configuration table.

Later I will use this table to prepare dynamic insert script with primary key and foreign key's  to load all the tables.

Please suggest .

-- Added

I'm using below query to get the tagname and parent tag name and  xpath from your approach.

WITH edge_data AS
  (SELECT x.* ,
    row_number() over(partition BY parent_node_id, node_name order by node_id) AS position
  FROM tmp_xml t ,
    TABLE(xmlflattendom(t.object_value)) x
  )
SELECT x.* ,
  regexp_substr ( xpath, '/([^[]+)', 1, regexp_count(xpath,'/')-1, null, 1) node_name
,regexp_substr ( xpath, '/([^[]+)', 1, regexp_count(xpath,'/')-2, null, 1) parent_node
FROM
  (SELECT t.node_id ,
    t.node_name ,
    t.node_type,
    sys_connect_by_path(
    CASE node_type
      WHEN 'element'
      THEN node_name
        || '['
        || position
        || ']'
      WHEN 'attribute'
      THEN '@'
        || node_name
      ELSE 'text()'
    END , '/' ) AS xpath
  FROM edge_data t
  WHERE node_type             IN ('element', 'attribute', 'text')
    CONNECT BY prior node_id   = parent_node_id
    START WITH parent_node_id IS NULL
  )x
WHERE node_type='text';
odie_63 
My approach :
Inserting  fieldname, parent tag name, field order ( column order in the actual tables) ,xpath informations in a temporary configuration table.
Later I will use this table to prepare dynamic insert script with primary key and foreign key's  to load all the tables.
Please suggest .

What I suggest is to drop this approach altogether.

You may think it's rather smart to implement generic stuff like that but at the end of the day, it'll be harder to maintain and less efficient.

My piece of advice : use static XMLTABLE/XMLQUERY statements with static paths.

My original xml file have lot of nodes. some nodes have same field names.

So the XML contains collection(s) of repeating nodes. All very classic so far.

Given the fact that you requested XPath expressions with positional predicates (e.g. [1], [2] etc.), I'm inclined to think that you want to extract each value one by one, and that would be the worst thing to do with respect to what Oracle offers to do the job properly and efficiently.

Again, XMLTABLE is the right tool to use here.

1 person found this helpful
User_4CDUU

Hi @odie-63

Thank you for your solution.

But I need use xpath to get the path for updating:
example:
for $i at $p in $e//summary/(node()|@*)
But it doesn't work.
I want all path related summary node.

Can you give me an advice?

1 - 21

Post Details

Added on Aug 12 2021
#12c
5 comments
389 views
2 mentions