Parse XML data - 10G

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
LVL 1
jvoconnellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
slightwv (䄆 Netminder) Commented:
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
 
jvoconnellAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
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
 
jvoconnellAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
jvoconnellAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
jvoconnellAuthor Commented:
That did it! I can't thank you enough. Thanks for the knowledge sharing. Your help is always appreciated.!!!!!!!!
0
 
slightwv (䄆 Netminder) Commented:
Glad you got it working and glad to be of assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.