Solved

Parse XML data - 10G

Posted on 2012-04-03
10
450 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
  • 6
  • 4
10 Comments
 
LVL 76

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 76

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
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 76

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now