Construct XMLType query to store data in Oracle11g

Question

What I am trying to achieve is to create a table containing separate rows for all the multi item child nodes for this particular xml :

<ABCD>
<EMPLOYEE id="11" date="25-Apr-1983"> 
<NameDetails> 
<Name NameType="a"> 
<NameValue> 
<FirstName>ABCD</FirstName> 
<Surname>PQR</Surname> 
<OriginalName>TEST1</OriginalName> 
<OriginalName>TEST2</OriginalName> 
</NameValue> 
</Name> 
 <Name NameType="b"> 
 <NameValue> 
<FirstName>TEST3</FirstName> 
<Surname>TEST3</Surname> 
</NameValue> 
 <NameValue> 
<FirstName>TEST5</FirstName> 
<MiddleName>TEST6</MiddleName> 
<Surname>TEST7</Surname> 
<OriginalName>JAB1</OriginalName> 
</NameValue> 
 <NameValue> 
<FirstName>HER</FirstName> 
<MiddleName>HIS</MiddleName> 
<Surname>LOO</Surname> 
</NameValue> 
</Name>  <Name NameType="c"> 
<NameValue> 
<FirstName>CDS</FirstName> 
<MiddleName>DRE</MiddleName> 
<Surname>QWE</Surname> 
</NameValue> 
 <NameValue> 
<FirstName>CCD</FirstName> 
<MiddleName>YTD</MiddleName> 
<Surname>QQA</Surname> 
</NameValue> 
 <NameValue> 
<FirstName>DS</FirstName> 
<Surname>AzDFz</Surname> 
</NameValue> 
</Name> 
</NameDetails> 

</EMPLOYEE >
</ABCD>

I tried using the query :

SELECT t.personid,n.nametypeid,t.firstname,t.middlename,t.surname,t.maidenname,t.originalName
FROM xml_files p,master_nametypes n,
     XMLTable(
      'for $i in ADCD/Employee/NameDetails/Name/NameValue
       return <row>
       {
          $i/../../../@id,
          $i/../@NameType,
          $i/FirstName,
          $i/MiddleName,
          $i/OriginalName
          $i/Surname,
          $i/MaidenName,
          $i/Suffix,
          $i/SingleStringName,
          $i/EntityName

       } 
       </row>' 
      PASSING p.filecontent
      COLUMNS 
              personid  number PATH '@id',
              nametypeid    VARCHAR2(255)  PATH '@NameType',
              firstname    VARCHAR2(4000)  PATH 'FirstName',
              middlename    VARCHAR2(4000)  PATH 'MiddleName',
              surname    VARCHAR2(4000)  PATH 'Surname',
              maidenname    VARCHAR2(4000)  PATH 'MaidenName',
              originalName    VARCHAR2(4000)  PATH '.'

              ) t  where t.nametypeid = n.nametype and n.recordtype = 'Employee'
;

But this will throw error when there is multiple child nodes like 'ORIGINALNAME' under 'NAMEVALUE' node. How can I retreive these values as well in separate rows based on their parent nodes. Can somebody help me to correct this query. Any help would be appreciated.

A.B.Cade · Accepted Answer · 2013-05-30 14:57:02Z

1

You can also try something like this (it will de-normalize the table):

Here is a sqlfiddle demo

SELECT t.personid,t.nametypeid,t.firstname,t.middlename,t.surname,t.maidenname,t.originalName
FROM xml_files p,
     XMLTable(
      '
       for $m in $ADCD//NameValue/OriginalName
       |  $ADCD//NameValue[not(exists(OriginalName))]
       return
       <row>
       {
          ($m/../../../../@id , $m/../../../@id)[1],
          ($m/../../@NameType,$m/../@NameType)[1],
          ($m/../FirstName,$m/FirstName)[1],
          ($m/../MiddleName,$m/MiddleName)[1],
          ($m,"")[1],
          ($m/../Surname,$m/Surname)[1],
          ($m/../MaidenName,$m/MaidenName)[1],
          ($m/../Suffix,$m/Suffix)[1],
          ($m/../SingleStringName,$m/SingleStringName)[1],
          ($m/../EntityName,$m/EntityName)[1]
       } 
       </row>' 

      PASSING p.filecontent as "ADCD"
      COLUMNS 

              personid  number PATH '@id',
              nametypeid    VARCHAR2(255)  PATH '@NameType',
              firstname    VARCHAR2(4000)  PATH 'FirstName',
              middlename    VARCHAR2(4000)  PATH 'MiddleName',
              surname    VARCHAR2(4000)  PATH 'Surname',
              maidenname    VARCHAR2(4000)  PATH 'MaidenName',
              originalName    VARCHAR2(4000)  PATH 'OriginalName'


              ) t  
;

answered May 30, 2013 at 14:57

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kiran Over a year ago

Thanks a lot A.B. for your time ..But when I tried, this doesn't fetch me any resullt. It gives me zero rows as output.I am not sure why that is so.

A.B.Cade Over a year ago

In the demo it works, so it's probably something in the xpath, since I made some "shortcuts"...

Kiran Over a year ago

Hi A.B ..I tried in the demo giving the correct xpath and all and it works perfectly there. But the same code is not fetching me any results for same data when I tried using SQL developer 3.2.09. I have no clue why that is so? :(

A.B.Cade Over a year ago

OK, this one has no shortcuts- sqlfiddle.com/#!4/f132f/71 does it work for you ?

Kiran Over a year ago

Thanks a lot A.B for your time..It does work in the sql fiddle with the data and all but not when I use it in the sql developer with exact data. Not sure about the cause though. Anyways I have used Vincent's code to insert data to my relational tables. Later I can extract it with some delimiters or so....Thanks a lot for your kind support.

Vincent Malgrat · Accepted Answer · 2013-05-30 13:37:04Z

Try this:

SQL> SELECT t.personid, t.firstname, t.middlename,
  2         t.surname,t.maidenname,
  3         replace(replace(t.originalName, '<OriginalName>'),
  4                 '</OriginalName>', ' ') originalName
  5  FROM xml_files p,
  6       XMLTABLE (
  7        --'ABCD/EMPLOYEE/NameDetails/Name/NameValue'
  8        'for $i in ABCD/EMPLOYEE/NameDetails/Name/NameValue
  9         return <row>
 10                    {$i/../../../@id}
 11                    {$i/../@NameType}
 12                    {$i/FirstName}{$i/MiddleName}{$i/OriginalName}
 13                    {$i/Surname}{$i/MaidenName}
 14                </row>'
 15        PASSING p.filecontent
 16        COLUMNS
 17                personid     NUMBER         PATH '@id',
 18                nametypeid   VARCHAR2(255)  PATH '@NameType',
 19                firstname    VARCHAR2(4000) PATH 'FirstName',
 20                middlename   VARCHAR2(4000) PATH 'MiddleName',
 21                surname      VARCHAR2(4000) PATH 'Surname',
 22                maidenname   VARCHAR2(4000) PATH 'MaidenName',
 23                originalName XMLTYPE        PATH 'OriginalName'
 24                ) t;

  PERSONID FIRSTNAME  MIDDLENAME  SURNAME  MAIDENNAME  ORIGINALNAME
---------- ---------- ----------- -------- ----------- ------------
        11 ABCD                   PQR                  TEST1 TEST2
        11 TEST3                  TEST3                
        11 TEST5      TEST6       TEST7                JAB1
        11 HER        HIS         LOO                  
        11 CDS        DRE         QWE                  
        11 CCD        YTD         QQA

Thanks a lot Vincent. This works..But I would like to have the Multiple "OriginalName" to be in two different rows instead of showing in one row.How can I modify this to achieve that.

Collectives™ on Stack Overflow

Construct XMLType query to store data in Oracle11g

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related