angel7170
asked on
Parse data from XMLtype data in Oracle SQL
Hello,
I have an XML type data that needs to be parsed using oracle SQL. attached is the sample XMLtype data and the expected results. Please assist.
Expected results
Serialno PUBDATE RESTRICTCOUNT NOTDATE HOLREF DOCID
2326564 20040108 0 20031211 test test123
Current setup:
Serialno: 2326564
XMLType data:
I have an XML type data that needs to be parsed using oracle SQL. attached is the sample XMLtype data and the expected results. Please assist.
Expected results
Serialno PUBDATE RESTRICTCOUNT NOTDATE HOLREF DOCID
2326564 20040108 0 20031211 test test123
Current setup:
Serialno: 2326564
XMLType data:
<?xml version="1.0" encoding="UTF-8" standalone='yes'?>
<document system-creator="test" description="Application" document-subtype="APP" document-type="IBInput" version="1.0">
<ENOT PUBDATE="20040108" RESTRICTCOUNT="0" NOTDATE="20031211" NOTLANG="1">
<BIRTH HOLREF="test" OOCD="IE" DOCID="test123" INT="20031103" NOTDATE="20031211">
<HOLGR CLID="518543" NOTLANG="1">
<NAME>
<NAMEL>TEST SERVICES LTD.</NAMEL>
</NAME>
<ADDRESS>
<ADDRL>Address line 1 test,</ADDRL>
<ADDRL>Address line 2 test</ADDRL>
<ADDRL>County</ADDRL>
<COUNTRY>TEST</COUNTRY>
</ADDRESS>
<ENTNATL>TEST</ENTNATL>
<LEGNATU>
<LEGNATT>A company</LEGNATT>
<PLAINCO>testing</PLAINCO>
</LEGNATU>
<CORRIND/>
</HOLGR>
<REPGR CLID="234867">
<NAME>
<NAMEL>JOHN DOE</NAMEL>
</NAME>
<ADDRESS>
<ADDRL>Address line 1</ADDRL>
<ADDRL>Address line 2</ADDRL>
<COUNTRY>TEST</COUNTRY>
</ADDRESS>
</REPGR>
</BIRTH>
</ENOT>
</document>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh nevermind. It works. I forgot the column names are case sensitive.
Thank you
Thank you
>>I forgot the column names are case sensitive.
They shouldn't be. XML PATH names are but not columns.
They shouldn't be. XML PATH names are but not columns.
ASKER
I meant the PATH names in quotes :)
Thank you very much
Thank you very much
ASKER
I tried your query but there is no result coming up. The query returns empty. But as you see there is data.