?
Solved

Parse XML data - 10G

Posted on 2012-04-03
10
Medium Priority
?
458 Views
Last Modified: 2012-04-04
Hello,

I have a table with a clob datatype and it contains XML data. I would like to parse out the data and am having issues. Although it's not in the format I want, I can get some output for the XML record with no header info, but nothing for the record that has the header info.


I would like to get multiple rows:

FNAME     LNAME

JOHN        DOE
JANE        DOE

Obviously I'm on the wrong track. I'm new to this and have looked up and tried many examples.
The most progess I can get is:


select
xmltype(e.rawxml).extract('/Beneficiarys/BENEFICIARY/FIRSTNAME/text()').getStringVal() FNAME,
xmltype(e.rawxml).extract('/Beneficiarys/BENEFICIARY/LASTNAME/text()').getStringVal() LNAME
      from XML_INPUT e

...which gives me

FNAME            LNAME
JOHNJANE       DOEDOE
<< NO DATA FOR THE RECORD WITH HEADER INFO  >>


I've attached a simple sample of the 'create table' and insert of 2 records just for an example.

Any help is appreciated.
xml-sample-table-data.sql
0
Comment
Question by:jvoconnell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802470
Try this simple change:

select
xmltype(e.rawxml).extract('//Beneficiarys/BENEFICIARY/FIRSTNAME/text()').getStringVal() FNAME,
xmltype(e.rawxml).extract('//Beneficiarys/BENEFICIARY/LASTNAME/text()').getStringVal() LNAME
      from XML_INPUT e


The one with header info changes the root node.  The '//' says I want this node ANYWHERE in the doc.

As single '/' means physical path and Beneficiarys must be the first node.
0
 
LVL 1

Author Comment

by:jvoconnell
ID: 37802550
wow. this is great, it will always have a header, I put the other one in for testing. is there a way I can pass a parameter that would put only one record into a row. I saw an extractvalue function, would I be going down the right path using that?

like
John Doe
Jane Doe

currently I get
JOHNJOHN      DOEDOE
JOHNJANE       DOEDOE
JOHNJOHN      DOEDOE
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802582
>>I put the other one in for testing

So you wanted to 'test' somthing that will never happen?

>>I saw an extractvalue function, would I be going down the right path using that?

extractvalue will decode the values.  Extract does not.

For example, it name has an '&' in it, say AT&T.  extract will return the encoded value: AT&amp;T.  Extractvalue will return AT&T.

>>is there a way I can pass a parameter that would put only one record into a row

Your XML really isn't 'valid'.  The syntax is correct but repeating nodes should stand along.  Then you could.

Is there a maximum of FIRSTNAMES in a single BENEFICIARY?
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802585
What I was saying about 'valid', the XML should probalby be like:

<Beneficiarys>
    <BENEFICIARY>
      <HICAN>XXXXXXXXXA</HICAN>
      <FIRSTNAME>JOHN</FIRSTNAME>
      <MIDDLENAME>
      </MIDDLENAME>
      <LASTNAME>DOE</LASTNAME>
      <DOB>1900101</DOB>
      <GENDER>M</GENDER>
    <ERRORCODE>00</ERRORCODE>
   </BENEFICIARY>
    <BENEFICIARY>

    <HICAN>XXXXPPXXXA</HICAN>
          <FIRSTNAME>JOHN</FIRSTNAME>
          <MIDDLENAME>
          </MIDDLENAME>
          <LASTNAME>DOE</LASTNAME>
          <DOB>1900101</DOB>
          <GENDER>M</GENDER>
    <ERRORCODE>00</ERRORCODE>
    </BENEFICIARY>
  </Beneficiarys>
0
 
LVL 1

Author Comment

by:jvoconnell
ID: 37802653
I want to apologize, I shouldn't have said one of the scenarios would never happen,  it's just that we would receive data with a header record a mojority of the time. I don't want you to think I was wasting your time.

A coworker gave me a test file taht I forwarded. I checked the production data and it is in fact set up like the "valid" example you gave.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802763
>>I don't want you to think I was wasting your time

If it came across that way, I apologize.  It wasn't meant that way.  I was just trying to understand why you wanted to do that.
0
 
LVL 1

Author Comment

by:jvoconnell
ID: 37803670
The code below gives me the basic result I want. It gives me a new row for each FirstName in the XML data. (assuming a "valid" format as described above)

I've been on this for a long time, I can't seem to get it to return multiple columns.
Is this the right path to stay on to get this logic to work to get multiple columns back.
(i.e FIRSTNAME, LASTNAME, DOB)


 SELECT x2.FIRSTNAME
         FROM XML_INPUT t,
     XMLTable(
      '//Beneficiarys/Beneficiary/FIRSTNAME'
      passing XMLTYPE(t.RAWXML)
      columns
        FIRSTNAME VARCHAR2(50) path '.'
     ) x2
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37804142
I believe so.  Just add additional columns and likely remove the FIRSNAME node from the XMLTABLE:

XMLTable(
      '//Beneficiarys/Beneficiary

Then add the additional columns under
columns
        FIRSTNAME VARCHAR2(50) path '.'
     ) x2


I'm not where I can test this.  If no other Expert answers this before tomorrow, I'll set up a test case using your data.
0
 
LVL 1

Author Comment

by:jvoconnell
ID: 37805794
That did it! I can't thank you enough. Thanks for the knowledge sharing. Your help is always appreciated.!!!!!!!!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37805942
Glad you got it working and glad to be of assistance.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question