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:

<?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>

Select allOpen in new window