Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parse XML data - 10G

Posted on 2012-04-03
10
Medium Priority
?
459 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 78

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 78

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 78

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 78

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 78

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 78

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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
Suggested Courses

916 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