Solved

Parse XML data - 10G

Posted on 2012-04-03
10
448 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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

705 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

23 Experts available now in Live!

Get 1:1 Help Now