13 Replies Latest reply on Mar 24, 2014 8:50 AM by odie_63

    Reg: XML parsing in SQL -

    ranit B

      Hi Experts,

       

      I am trying to parse a XML content and put into relational table.

       

      Setup-

      create table test_xml(
      x_val clob
      );

       

       

      DECLARE
      val clob := TO_CLOB('<?xml version="1.0" encoding="ISO-8859-1"?>
      <POWERMART CREATION_DATE="03/14/2014 19:20:05" REPOSITORY_VERSION="182.91">
      <REPOSITORY NAME="RS_AAA_DEV" VERSION="182" CODEPAGE="Latin1" DATABASETYPE="Oracle">
      <FOLDER NAME="dev_biswasra" GROUP="" OWNER="dev_ranit" SHARED="NOTSHARED" DESCRIPTION="Ranit" PERMISSIONS="rwx---r--" UUID="4623dd59-fcbf-4c71-9234-9a8d03e6fc51">
          <SOURCE BUSINESSNAME ="" DATABASETYPE ="Flat File" DBDNAME ="FlatFile" DESCRIPTION ="" NAME ="src_ranit_testing" OBJECTVERSION ="1" OWNERNAME ="" VERSIONNUMBER ="1">
              <FLATFILE CODEPAGE ="MS1252" CONSECDELIMITERSASONE ="NO" DELIMITED ="YES" DELIMITERS ="," ESCAPE_CHARACTER ="" KEEPESCAPECHAR ="NO" LINESEQUENTIAL ="NO" MULTIDELIMITERSASAND ="YES" NULLCHARTYPE ="ASCII" NULL_CHARACTER ="*" PADBYTES ="1" QUOTE_CHARACTER ="DOUBLE" REPEATABLE ="NO" ROWDELIMITER ="0" SHIFTSENSITIVEDATA ="NO" SKIPROWS ="1" STRIPTRAILINGBLANKS ="NO"/>
              <TABLEATTRIBUTE NAME ="Base Table Name" VALUE =""/>
              <TABLEATTRIBUTE NAME ="Search Specification" VALUE =""/>
              <TABLEATTRIBUTE NAME ="Sort Specification" VALUE =""/>
              <TABLEATTRIBUTE NAME ="Datetime Format" VALUE ="A  19 mm/dd/yyyy hh24:mi:ss"/>
              <TABLEATTRIBUTE NAME ="Thousand Separator" VALUE ="None"/>
              <TABLEATTRIBUTE NAME ="Decimal Separator" VALUE ="."/>
              <TABLEATTRIBUTE NAME ="Add Currently Processed Flat File Name Port" VALUE ="NO"/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="1" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="50" LEVEL ="0" NAME ="l_number" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="50" PHYSICALOFFSET ="0" PICTURETEXT ="" PRECISION ="50" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="2" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="1" LEVEL ="0" NAME ="invo" NULLABLE ="NULL" OCCURS ="0" OFFSET ="50" PHYSICALLENGTH ="1" PHYSICALOFFSET ="50" PICTURETEXT ="" PRECISION ="1" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="3" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="2" LEVEL ="0" NAME ="reco_inf" NULLABLE ="NULL" OCCURS ="0" OFFSET ="51" PHYSICALLENGTH ="2" PHYSICALOFFSET ="51" PICTURETEXT ="" PRECISION ="2" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="4" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="1" LEVEL ="0" NAME ="Source_Code" NULLABLE ="NULL" OCCURS ="0" OFFSET ="53" PHYSICALLENGTH ="1" PHYSICALOFFSET ="53" PICTURETEXT ="" PRECISION ="1" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="5" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="30" LEVEL ="0" NAME ="fno" NULLABLE ="NULL" OCCURS ="0" OFFSET ="54" PHYSICALLENGTH ="30" PHYSICALOFFSET ="54" PICTURETEXT ="" PRECISION ="30" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="6" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="4" LEVEL ="0" NAME ="date_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="84" PHYSICALLENGTH ="4" PHYSICALOFFSET ="84" PICTURETEXT ="" PRECISION ="4" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="7" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="8" LEVEL ="0" NAME ="baln_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="88" PHYSICALLENGTH ="8" PHYSICALOFFSET ="88" PICTURETEXT ="" PRECISION ="8" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="8" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="11" LEVEL ="0" NAME ="inter_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="96" PHYSICALLENGTH ="11" PHYSICALOFFSET ="96" PICTURETEXT ="" PRECISION ="11" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="9" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="11" LEVEL ="0" NAME ="prin_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="107" PHYSICALLENGTH ="11" PHYSICALOFFSET ="107" PICTURETEXT ="" PRECISION ="11" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="10" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="5" LEVEL ="0" NAME ="codec" NULLABLE ="NULL" OCCURS ="0" OFFSET ="118" PHYSICALLENGTH ="5" PHYSICALOFFSET ="118" PICTURETEXT ="" PRECISION ="5" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="11" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="6" LEVEL ="0" NAME ="date_x2" NULLABLE ="NULL" OCCURS ="0" OFFSET ="123" PHYSICALLENGTH ="6" PHYSICALOFFSET ="123" PICTURETEXT ="" PRECISION ="6" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="12" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="8" LEVEL ="0" NAME ="others" NULLABLE ="NULL" OCCURS ="0" OFFSET ="129" PHYSICALLENGTH ="8" PHYSICALOFFSET ="129" PICTURETEXT ="" PRECISION ="8" SCALE ="0" USAGE_FLAGS =""/>
          </SOURCE>
          <SOURCE BUSINESSNAME ="" DATABASETYPE ="Oracle" DBDNAME ="DB_AAAA" DESCRIPTION ="" NAME ="DB_AAAA_RUN_SESSION" OBJECTVERSION ="1" OWNERNAME ="DB_AAAA" VERSIONNUMBER ="1">
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number(p,s)" DESCRIPTION ="" FIELDNUMBER ="1" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="PRIMARY KEY" LENGTH ="40" LEVEL ="0" NAME ="RUN_SESSION_ID" NULLABLE ="NOTNULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="38" PHYSICALOFFSET ="0" PICTURETEXT ="" PRECISION ="38" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="2" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="SESSION_STATUS" NULLABLE ="NULL" OCCURS ="0" OFFSET ="40" PHYSICALLENGTH ="45" PHYSICALOFFSET ="38" PICTURETEXT ="" PRECISION ="45" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="date" DESCRIPTION ="" FIELDNUMBER ="3" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="19" LEVEL ="0" NAME ="SESSION_START_TIME" NULLABLE ="NULL" OCCURS ="0" OFFSET ="40" PHYSICALLENGTH ="19" PHYSICALOFFSET ="83" PICTURETEXT ="" PRECISION ="19" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="date" DESCRIPTION ="" FIELDNUMBER ="4" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="19" LEVEL ="0" NAME ="SESSION_END_TIME" NULLABLE ="NULL" OCCURS ="0" OFFSET ="59" PHYSICALLENGTH ="19" PHYSICALOFFSET ="102" PICTURETEXT ="" PRECISION ="19" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number(p,s)" DESCRIPTION ="" FIELDNUMBER ="5" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="40" LEVEL ="0" NAME ="SRC_REC_COUNT" NULLABLE ="NULL" OCCURS ="0" OFFSET ="78" PHYSICALLENGTH ="38" PHYSICALOFFSET ="121" PICTURETEXT ="" PRECISION ="38" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number(p,s)" DESCRIPTION ="" FIELDNUMBER ="6" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="40" LEVEL ="0" NAME ="TGT_REC_COUNT" NULLABLE ="NULL" OCCURS ="0" OFFSET ="118" PHYSICALLENGTH ="38" PHYSICALOFFSET ="159" PICTURETEXT ="" PRECISION ="38" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number(p,s)" DESCRIPTION ="" FIELDNUMBER ="7" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="40" LEVEL ="0" NAME ="REJ_REC_COUNT" NULLABLE ="NULL" OCCURS ="0" OFFSET ="158" PHYSICALLENGTH ="38" PHYSICALOFFSET ="197" PICTURETEXT ="" PRECISION ="38" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="8" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="MAPPING_NAME" NULLABLE ="NULL" OCCURS ="0" OFFSET ="198" PHYSICALLENGTH ="100" PHYSICALOFFSET ="235" PICTURETEXT ="" PRECISION ="100" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="9" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="SESSION_NAME" NULLABLE ="NULL" OCCURS ="0" OFFSET ="198" PHYSICALLENGTH ="100" PHYSICALOFFSET ="335" PICTURETEXT ="" PRECISION ="100" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="10" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="WORKFLOW_NAME" NULLABLE ="NULL" OCCURS ="0" OFFSET ="198" PHYSICALLENGTH ="100" PHYSICALOFFSET ="435" PICTURETEXT ="" PRECISION ="100" SCALE ="0" USAGE_FLAGS =""/>
          </SOURCE>
          <SOURCE BUSINESSNAME ="" DATABASETYPE ="Oracle" DBDNAME ="DB_AAAA" DESCRIPTION ="" NAME ="TEST_FILE_AAAA" OBJECTVERSION ="1" OWNERNAME ="DB_AAAA" VERSIONNUMBER ="1">
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="1" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="l_number" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="50" PHYSICALOFFSET ="0" PICTURETEXT ="" PRECISION ="50" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="char" DESCRIPTION ="" FIELDNUMBER ="2" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="invo" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="1" PHYSICALOFFSET ="50" PICTURETEXT ="" PRECISION ="1" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="char" DESCRIPTION ="" FIELDNUMBER ="3" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="reco_inf" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="2" PHYSICALOFFSET ="51" PICTURETEXT ="" PRECISION ="2" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="char" DESCRIPTION ="" FIELDNUMBER ="4" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="SOURCE_CODE" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="1" PHYSICALOFFSET ="53" PICTURETEXT ="" PRECISION ="1" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="5" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="fno" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="30" PHYSICALOFFSET ="54" PICTURETEXT ="" PRECISION ="30" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="6" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="date_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="15" PHYSICALOFFSET ="84" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="7" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="baln_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="15" PHYSICALOFFSET ="99" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="8" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="inter_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="11" PHYSICALOFFSET ="114" PICTURETEXT ="" PRECISION ="11" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="9" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="prin_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="11" PHYSICALOFFSET ="125" PICTURETEXT ="" PRECISION ="11" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="10" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="codec" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="5" PHYSICALOFFSET ="136" PICTURETEXT ="" PRECISION ="5" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="11" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="date_x2" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="6" PHYSICALOFFSET ="141" PICTURETEXT ="" PRECISION ="6" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="char" DESCRIPTION ="" FIELDNUMBER ="12" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="others" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="8" PHYSICALOFFSET ="147" PICTURETEXT ="" PRECISION ="8" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="char" DESCRIPTION ="" FIELDNUMBER ="13" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="FILLER" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="4" PHYSICALOFFSET ="155" PICTURETEXT ="" PRECISION ="4" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="14" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="repos" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="15" PHYSICALOFFSET ="159" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="date" DESCRIPTION ="" FIELDNUMBER ="15" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="19" LEVEL ="0" NAME ="CREATED_DT" NULLABLE ="NULL" OCCURS ="0" OFFSET ="24" PHYSICALLENGTH ="19" PHYSICALOFFSET ="174" PICTURETEXT ="" PRECISION ="19" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="date" DESCRIPTION ="" FIELDNUMBER ="16" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="19" LEVEL ="0" NAME ="MODIFIED_DT" NULLABLE ="NULL" OCCURS ="0" OFFSET ="43" PHYSICALLENGTH ="19" PHYSICALOFFSET ="193" PICTURETEXT ="" PRECISION ="19" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="17" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="0" LEVEL ="0" NAME ="MODIFIED_USER" NULLABLE ="NULL" OCCURS ="0" OFFSET ="62" PHYSICALLENGTH ="20" PHYSICALOFFSET ="212" PICTURETEXT ="" PRECISION ="20" SCALE ="0" USAGE_FLAGS =""/>
          </SOURCE>
          <SOURCE BUSINESSNAME ="" DATABASETYPE ="Oracle" DBDNAME ="DB_AAAA" DESCRIPTION ="" NAME ="DUMMY" OBJECTVERSION ="1" OWNERNAME ="DB_AAAA" VERSIONNUMBER ="1">
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="1" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="ID" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="15" PHYSICALOFFSET ="0" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/>
              <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="2" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="SEQ_VAL" NULLABLE ="NULL" OCCURS ="0" OFFSET ="24" PHYSICALLENGTH ="15" PHYSICALOFFSET ="15" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/>
          </SOURCE>
      </FOLDER>
      </REPOSITORY>
      </POWERMART>');

       

      BEGIN
      insert into test_xml(x_val) values(val);
      end;

       

      Scenario -

      <POWERMART>

      <REPOSITORY>

      <FOLDER>

          <SOURCE id=1>

                <FLATFILE . . .>

                     . . .

                </FLATFILE>

                . . .

           </SOURCE>

           <SOURCE id=2>

                . . .

           </SOURCE>

          <TARGET . . .>

                . . .

           </TARGET>

           <MAPPING id=1>

                . . .

           </MAPPING>

      <FOLDER>

      <REPOSITORY>

      <POWERMART>

      This is the kind-of structure of the XML, but not exact.

       

      Powermart, Repository and Folder - occur only once at the top. But, "SOURCE", "TARGET", "MAPPING" ,etc.. can occur multiple times. So, I want to store them as separate records. And Yes - there will be redundant/repeating data for these 3 columns.

       

      I am aware this is not a normalized structure, but as of now I need something like this.

       

      Expected o/p -

       

      Table structure :

      Powermart_CREATION_DATE

      Powermart_REPOSITORY_VERSION

      Repository_NAME

      Repository_VERSION

      Repository_CODEPAGE

      Repository_DATABASETYPE

      Folder_

      Folder_

      .

      .

      Source_BUSINESSNAME

      Source_DATABASETYPE

      Source_DBDNAME

      Source_DESCRIPTION

      .

      .

      Flatfile_CODEPAGE

      Flatfile_CONSECDELIMITERSASONE

      .

      .

      TableAttribute_

      Sourcefield_BUSINESSNAME

      Sourcefield_DATATYPE

      .

      .

      etc . . .

      Basically, I want to store each and every information - values and attributes of the XML in separate columns.

      Or, please suggest any normalized structure/idea if possible.

       

      Please let me know if you need any further clarification regarding this.

       

       

      Thanks

      -- Ranit

      (on Oracle 11.2.0.3.0)

        • 1. Re: Reg: XML parsing in SQL -
          ranit B

          My try -

          SELECT tx1.* , tx2.*

          FROM

            test_xml dx, XMLTable(

            '/POWERMART'

            passing XMLTYPE(dx.x_val)

            COLUMNS

              rid FOR ordinality,

              powermart_creation_dt VARCHAR2(30CHAR) path '/POWERMART/@CREATION_DATE',

              powermart_repository_version VARCHAR2(30) path '/POWERMART/@REPOSITORY_VERSION',

              ---

              repository_name VARCHAR2(30) path '/POWERMART/REPOSITORY/@NAME',

              repository_version VARCHAR2(30) path '/POWERMART/REPOSITORY/@VERSION',

              repository_codepage VARCHAR2(30) path '/POWERMART/REPOSITORY/@CODEPAGE',

              repository_databasetype VARCHAR2(30) path '/POWERMART/REPOSITORY/@DATABASETYPE',

              ---

              folder_name VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@NAME',

              folder_group VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@GROUP',

              folder_owner VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@OWNER',

              folder_shared VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@SHARED',

              folder_description VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@DESCRIPTION',

              folder_permissions VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@PERMISSIONS',

              folder_uuid VARCHAR2(50) path '/POWERMART/REPOSITORY/FOLDER/@UUID',

              ---

              xml_source XMLTYPE path '/POWERMART/REPOSITORY/FOLDER/SOURCE[2]' --<-- or, SOURCE[1]

          ) tx1, XMLTable(

              '/'

              passing tx1.xml_source

              COLUMNS

                source_buss_name VARCHAR2(30) path '@BUSINESSNAME',

                source_database_type VARCHAR2(30) path '@DATABASETYPE'

                ) tx2

          ;

          This gives me individual records.

           

          Please advise.

          • 2. Re: Reg: XML parsing in SQL -
            ranit B

            I am able to proceed further...

            But, data-redundancy is my next concern.

            select
              --tx1.*,
              --tx_source.*,
              tx_tableattrib.*,
              tx_sourcefield.*
            FROM
              test_xml dx, XMLTable(
              '/POWERMART'
              passing XMLTYPE(dx.x_val)
              COLUMNS
                rid FOR ordinality,
                powermart_creation_dt VARCHAR2(30CHAR) path '/POWERMART/@CREATION_DATE',
                powermart_repository_version VARCHAR2(30) path '/POWERMART/@REPOSITORY_VERSION',
                ---
                repository_name VARCHAR2(30) path '/POWERMART/REPOSITORY/@NAME',
                repository_version VARCHAR2(30) path '/POWERMART/REPOSITORY/@VERSION',
                repository_codepage VARCHAR2(30) path '/POWERMART/REPOSITORY/@CODEPAGE',
                repository_databasetype VARCHAR2(30) path '/POWERMART/REPOSITORY/@DATABASETYPE',
                ---
                folder_name VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@NAME',
                folder_group VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@GROUP',
                folder_owner VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@OWNER',
                folder_shared VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@SHARED',
                folder_description VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@DESCRIPTION',
                folder_permissions VARCHAR2(30) path '/POWERMART/REPOSITORY/FOLDER/@PERMISSIONS',
                folder_uuid VARCHAR2(50) path '/POWERMART/REPOSITORY/FOLDER/@UUID',
                ---
                xml_source XMLTYPE path '/POWERMART/REPOSITORY/FOLDER/SOURCE',
                ---
                xml_target XMLTYPE path '/POWERMART/REPOSITORY/FOLDER/TARGET'
            ) tx1 , XMLTable(
                'for $i in $src/SOURCE return $i'
                passing tx1.xml_source as "src"
                columns
                  source_id for ordinality,
                  source_buss_name VARCHAR2(30) path '@BUSINESSNAME',
                  source_database_type VARCHAR2(30) path '@DATABASETYPE',
                  source_database_name VARCHAR2(30) path '@DBNAME',
                  source_description VARCHAR2(30) path '@DESCRIPTION',
                  source_name VARCHAR2(30) path '@NAME',
                  source_object_version VARCHAR2(30) path '@OBJECTVERSION',
                  source_ownername VARCHAR2(30) path '@OWNERNAME',
                  source_version_no VARCHAR2(30) path '@VERSIONNUMBER',
                  ---
                  flatfile_codepage VARCHAR2(20) path 'FLATFILE/@CODEPAGE',
                  flatfile_CONSECDELIMITERSASONE VARCHAR2(20) path 'FLATFILE/@CONSECDELIMITERSASONE',
                  flatfile_DELIMITED VARCHAR2(20) path 'FLATFILE/@DELIMITED',
                  flatfile_DELIMITERS VARCHAR2(20) path 'FLATFILE/@DELIMITERS',
                  flatfile_ESCAPE_CHARACTER VARCHAR2(20) path 'FLATFILE/@ESCAPE_CHARACTER',
                  flatfile_KEEPESCAPECHAR VARCHAR2(20) path 'FLATFILE/@KEEPESCAPECHAR',
                  flatfile_LINESEQUENTIAL VARCHAR2(20) path 'FLATFILE/@LINESEQUENTIAL',
                  flatfile_MULTIDELIMITERSASAND VARCHAR2(20) path 'FLATFILE/@MULTIDELIMITERSASAND',
                  flatfile_NULLCHARTYPE VARCHAR2(20) path 'FLATFILE/@NULLCHARTYPE',
                  flatfile_NULL_CHARACTER VARCHAR2(20) path 'FLATFILE/@NULL_CHARACTER',
                  flatfile_PADBYTES VARCHAR2(20) path 'FLATFILE/@PADBYTES',
                  flatfile_QUOTE_CHARACTER VARCHAR2(20) path 'FLATFILE/@QUOTE_CHARACTER',
                  flatfile_REPEATABLE VARCHAR2(20) path 'FLATFILE/@REPEATABLE',
                  flatfile_ROWDELIMITER VARCHAR2(20) path 'FLATFILE/@ROWDELIMITER',
                  flatfile_SHIFTSENSITIVEDATA VARCHAR2(20) path 'FLATFILE/@SHIFTSENSITIVEDATA',
                  flatfile_SKIPROWS VARCHAR2(20) path 'FLATFILE/@SKIPROWS',
                  flatfile_STRIPTRAILINGBLANKS VARCHAR2(20) path 'FLATFILE/@STRIPTRAILINGBLANKS',
                  ---
                  tableattribute XMLTYPE path 'TABLEATTRIBUTE',
                  sourcefield XMLTYPE path 'SOURCEFIELD'
                  ) tx_source,
            xmltable(
              'for $i in $tbl_attrib/TABLEATTRIBUTE return $i'
              passing tx_source.tableattribute as "tbl_attrib"
              columns
                tbl_attrib for ordinality,
                tbl_attrib_name varchar2(50) path '@NAME',
                tbl_attrib_value varchar2(50) path '@VALUE'
            ) tx_tableattrib,
            xmltable(
              'for $i in $sf/SOURCEFIELD return $i'
              passing tx_source.sourcefield as "sf"
              columns
                sourcefield_id for ordinality,
                sourcefield_businessname varchar2(50) path '@BUSINESSNAME',
                sourcefield_datatype varchar2(50) path '@DATATYPE',
                sourcefield_description varchar2(50) path '@DESCRIPTION',
                sourcefield_fieldnumber varchar2(50) path '@FIELDNUMBER',
                sourcefield_fieldproperty varchar2(50) path '@FIELDPROPERTY',
                sourcefield_fieldtype varchar2(50) path '@FIELDTYPE',
                sourcefield_hidden varchar2(50) path '@HIDDEN',
                sourcefield_keytype varchar2(50) path '@KEYTYPE',
                sourcefield_length varchar2(50) path '@LENGTH',
                sourcefield_level varchar2(50) path '@LEVEL',
                sourcefield_name varchar2(50) path '@NAME',
                sourcefield_nullable varchar2(50) path '@NULLABLE',
                sourcefield_occurs varchar2(50) path '@OCCURS',
                sourcefield_offset varchar2(50) path '@OFFSET',
                sourcefield_physicallength varchar2(50) path '@PHYSICALLENGTH',
                sourcefield_physicaloffset varchar2(50) path '@PHYSICALOFFSET',
                sourcefield_picturetext varchar2(50) path '@PICTURETEXT',
                sourcefield_precision varchar2(50) path '@PRECISION',
                sourcefield_scale varchar2(50) path '@SCALE',
                sourcefield_usage_flags varchar2(50) path '@USAGE_FLAGS'
            ) tx_sourcefield
            ;

            • 3. Re: Reg: XML parsing in SQL -
              Karthick2003

              I dont think this fit in a relational model. TABLEATTRIBUTE and SOURCEFIELD would result in cartesian product.

               

              <SOURCE BUSINESSNAME ="" DATABASETYPE ="Flat File" DBDNAME ="FlatFile" DESCRIPTION ="" NAME ="src_ranit_testing" OBJECTVERSION ="1" OWNERNAME ="" VERSIONNUMBER ="1">

                      <FLATFILE CODEPAGE ="MS1252" CONSECDELIMITERSASONE ="NO" DELIMITED ="YES" DELIMITERS ="," ESCAPE_CHARACTER ="" KEEPESCAPECHAR ="NO" LINESEQUENTIAL ="NO" MULTIDELIMITERSASAND ="YES" NULLCHARTYPE ="ASCII" NULL_CHARACTER ="*" PADBYTES ="1" QUOTE_CHARACTER ="DOUBLE" REPEATABLE ="NO" ROWDELIMITER ="0" SHIFTSENSITIVEDATA ="NO" SKIPROWS ="1" STRIPTRAILINGBLANKS ="NO"/>

                      <TABLEATTRIBUTE NAME ="Base Table Name" VALUE =""/>

                      <TABLEATTRIBUTE NAME ="Search Specification" VALUE =""/>

                      <TABLEATTRIBUTE NAME ="Sort Specification" VALUE =""/>

                      <TABLEATTRIBUTE NAME ="Datetime Format" VALUE ="A  19 mm/dd/yyyy hh24:mi:ss"/>

                      <TABLEATTRIBUTE NAME ="Thousand Separator" VALUE ="None"/>

                      <TABLEATTRIBUTE NAME ="Decimal Separator" VALUE ="."/>

                      <TABLEATTRIBUTE NAME ="Add Currently Processed Flat File Name Port" VALUE ="NO"/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="1" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="50" LEVEL ="0" NAME ="l_number" NULLABLE ="NULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="50" PHYSICALOFFSET ="0" PICTURETEXT ="" PRECISION ="50" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="2" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="1" LEVEL ="0" NAME ="invo" NULLABLE ="NULL" OCCURS ="0" OFFSET ="50" PHYSICALLENGTH ="1" PHYSICALOFFSET ="50" PICTURETEXT ="" PRECISION ="1" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="3" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="2" LEVEL ="0" NAME ="reco_inf" NULLABLE ="NULL" OCCURS ="0" OFFSET ="51" PHYSICALLENGTH ="2" PHYSICALOFFSET ="51" PICTURETEXT ="" PRECISION ="2" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="4" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="1" LEVEL ="0" NAME ="Source_Code" NULLABLE ="NULL" OCCURS ="0" OFFSET ="53" PHYSICALLENGTH ="1" PHYSICALOFFSET ="53" PICTURETEXT ="" PRECISION ="1" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="5" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="30" LEVEL ="0" NAME ="fno" NULLABLE ="NULL" OCCURS ="0" OFFSET ="54" PHYSICALLENGTH ="30" PHYSICALOFFSET ="54" PICTURETEXT ="" PRECISION ="30" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="6" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="4" LEVEL ="0" NAME ="date_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="84" PHYSICALLENGTH ="4" PHYSICALOFFSET ="84" PICTURETEXT ="" PRECISION ="4" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="7" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="8" LEVEL ="0" NAME ="baln_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="88" PHYSICALLENGTH ="8" PHYSICALOFFSET ="88" PICTURETEXT ="" PRECISION ="8" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="8" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="11" LEVEL ="0" NAME ="inter_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="96" PHYSICALLENGTH ="11" PHYSICALOFFSET ="96" PICTURETEXT ="" PRECISION ="11" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="9" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="11" LEVEL ="0" NAME ="prin_x" NULLABLE ="NULL" OCCURS ="0" OFFSET ="107" PHYSICALLENGTH ="11" PHYSICALOFFSET ="107" PICTURETEXT ="" PRECISION ="11" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="10" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="5" LEVEL ="0" NAME ="codec" NULLABLE ="NULL" OCCURS ="0" OFFSET ="118" PHYSICALLENGTH ="5" PHYSICALOFFSET ="118" PICTURETEXT ="" PRECISION ="5" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="11" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="6" LEVEL ="0" NAME ="date_x2" NULLABLE ="NULL" OCCURS ="0" OFFSET ="123" PHYSICALLENGTH ="6" PHYSICALOFFSET ="123" PICTURETEXT ="" PRECISION ="6" SCALE ="0" USAGE_FLAGS =""/>

                      <SOURCEFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="12" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="8" LEVEL ="0" NAME ="others" NULLABLE ="NULL" OCCURS ="0" OFFSET ="129" PHYSICALLENGTH ="8" PHYSICALOFFSET ="129" PICTURETEXT ="" PRECISION ="8" SCALE ="0" USAGE_FLAGS =""/>

                  </SOURCE>

               

              Other than that you can do this. its almost what you have already have.

               

              select t.*

                   , t1.*

                   , t2.*

                from test_xml x

                   , xmltable

                     (

                        '/POWERMART' passing xmltype(x.x_val)

                        columns

                         p_creation_date           varchar2(50) path '@CREATION_DATE',

                         r_repo_name               varchar2(50) path 'REPOSITORY/@NAME',

                         r_version                 varchar2(50) path 'REPOSITORY/@VERSION',

                         r_codepage                varchar2(50) path 'REPOSITORY/@CODEPAGE',

                         r_databasetype            varchar2(50) path 'REPOSITORY/@DATABASETYPE',

                         f_name                    varchar2(50) path 'REPOSITORY/FOLDER/@NAME',

                         f_group                   varchar2(50) path 'REPOSITORY/FOLDER/@GROUP',

                         f_owner                   varchar2(50) path 'REPOSITORY/FOLDER/@OWNER',

                         f_shared                  varchar2(50) path 'REPOSITORY/FOLDER/@SHARED',

                         f_description             varchar2(50) path 'REPOSITORY/FOLDER/@DESCRIPTION',

                         f_permissions             varchar2(50) path 'REPOSITORY/FOLDER/@PERMISSIONS',

                         f_uuid                    varchar2(50) path 'REPOSITORY/FOLDER/@UUID',

                        s_source                  xmltype      path 'REPOSITORY/FOLDER/SOURCE'

                     ) t

                   , xmltable

                     (

                        '/SOURCE' passing t.s_source

                        columns

                         s_businessname            varchar2(50) path '@BUSINESSNAME',

                         s_databasetype            varchar2(50) path '@DATABASETYPE',

                         s_dbdname                 varchar2(50) path '@DBDNAME',

                         s_description             varchar2(50) path '@DESCRIPTION',

                         s_name                    varchar2(50) path '@NAME',

                         s_objectversion           varchar2(50) path '@OBJECTVERSION',

                         s_ownername               varchar2(50) path '@OWNERNAME',

                         s_versionnumber           varchar2(50) path '@VERSIONNUMBER',

                         f_codepage                varchar2(50) path 'FLATFILE/@CODEPAGE',

                         f_consecdelimitersasone   varchar2(50) path 'FLATFILE/@CONSECDELIMITERSASONE',

                         f_delimited               varchar2(50) path 'FLATFILE/@DELIMITED',

                         f_delimiters              varchar2(50) path 'FLATFILE/@DELIMITERS',

                         f_escape_character        varchar2(50) path 'FLATFILE/@ESCAPE_CHARACTER',

                         f_keepescapechar          varchar2(50) path 'FLATFILE/@KEEPESCAPECHAR',

                         f_linesequential          varchar2(50) path 'FLATFILE/@LINESEQUENTIAL',

                         f_multidelimitersasand    varchar2(50) path 'FLATFILE/@MULTIDELIMITERSASAND',

                         f_nullchartype            varchar2(50) path 'FLATFILE/@NULLCHARTYPE',

                         f_null_character          varchar2(50) path 'FLATFILE/@NULL_CHARACTER',

                         f_padbytes                varchar2(50) path 'FLATFILE/@PADBYTES',

                         f_quote_character         varchar2(50) path 'FLATFILE/@QUOTE_CHARACTER',

                         f_repeatable              varchar2(50) path 'FLATFILE/@REPEATABLE',

                         f_rowdelimiter            varchar2(50) path 'FLATFILE/@ROWDELIMITER',

                         f_shiftsensitivedata      varchar2(50) path 'FLATFILE/@SHIFTSENSITIVEDATA',

                         f_skiprows                varchar2(50) path 'FLATFILE/@SKIPROWS',

                         f_striptrailingblanks     varchar2(50) path 'FLATFILE/@STRIPTRAILINGBLANKS',

                         f_sourcefield             xmltype      path 'SOURCEFIELD'

                     ) t1

                   , xmltable

                     (

                        '/SOURCEFIELD' passing t1.f_sourcefield

                        columns

                         sf_businessname           varchar2(50) path '@BUSINESSNAME',

                         sf_datatype               varchar2(50) path '@DATATYPE',

                         sf_description            varchar2(50) path '@DESCRIPTION',

                         sf_fieldnumber            varchar2(50) path '@FIELDNUMBER',

                         sf_fieldproperty          varchar2(50) path '@FIELDPROPERTY',

                         sf_fieldtype              varchar2(50) path '@FIELDTYPE',

                         sf_hidden                 varchar2(50) path '@HIDDEN',

                         sf_keytype                varchar2(50) path '@KEYTYPE',

                         sf_length                 varchar2(50) path '@LENGTH',

                         sf_level                  varchar2(50) path '@LEVEL',

                         sf_name                   varchar2(50) path '@NAME',

                         sf_nullable               varchar2(50) path '@NULLABLE',

                         sf_occurs                 varchar2(50) path '@OCCURS',

                         sf_offset                 varchar2(50) path '@OFFSET',

                         sf_physicallength         varchar2(50) path '@PHYSICALLENGTH',

                         sf_physicaloffset         varchar2(50) path '@PHYSICALOFFSET',

                         sf_picturetext            varchar2(50) path '@PICTURETEXT',

                         sf_precision              varchar2(50) path '@PRECISION',

                         sf_scale                  varchar2(50) path '@SCALE',

                         sf_usage_flags            varchar2(50) path '@USAGE_FLAGS'

                     ) t2

              1 person found this helpful
              • 4. Re: Reg: XML parsing in SQL -
                ranit B

                I dont think this fit in a relational model. TABLEATTRIBUTE and SOURCEFIELD would result in cartesian product

                Exactly correct, Karthick. This I mentioned in my previous reply.

                 

                Your workout looks so simple and also meets the requirement so far. Let me add further XML tags and extend your concept.

                 

                Just wondering few things -

                1) exactly where is your query differing from mine?

                2) How is your query result in normalized data.

                 

                3) How does this fetch the TABLEATTRIBUTE values? Do you have any idea/workaround?

                        <TABLEATTRIBUTE NAME ="Base Table Name" VALUE =""/>

                        <TABLEATTRIBUTE NAME ="Search Specification" VALUE =""/>

                        <TABLEATTRIBUTE NAME ="Sort Specification" VALUE =""/>

                        <TABLEATTRIBUTE NAME ="Datetime Format" VALUE ="A  19 mm/dd/yyyy hh24:mi:ss"/>

                        <TABLEATTRIBUTE NAME ="Thousand Separator" VALUE ="None"/>

                        <TABLEATTRIBUTE NAME ="Decimal Separator" VALUE ="."/>

                        <TABLEATTRIBUTE NAME ="Add Currently Processed Flat File Name Port" VALUE ="NO"/>

                Can this be done if we have one TABLEATTRIBUTE with all values like this (somewhat resembles the JSON format) -

                 

                TABLEATTRIBUTE

                [{NAME1 : VALUE1}, {NAME2 : VALUE2}, {NAME3 : VALUE3}, . . .]

                 

                ex -

                 

                TABLEATTRIBUTE

                [{"Base Table Name":""}, {"Search Specification":""}, {"Sort Specification":""}, {"Datetime Format":"A  19 mm/dd/yyyy hh24:mi:ss"},

                {"Thousand Separator":"None"}, {"Decimal Separator":"."}, {"Add Currently Processed Flat File Name Port":"NO"}]

                Please help me understand this.

                • 5. Re: Reg: XML parsing in SQL -
                  Karthick2003

                  > 3) How does this fetch the TABLEATTRIBUTE values? Do you have any idea/workaround?

                   

                  This does not fit in a relational model. A tabular representation is not possible for that part. As I already mentioned you need to define the relation between those nodes.

                  • 6. Re: Reg: XML parsing in SQL -
                    ranit B

                    Another doubt : (I am getting conceptually wrong somewhere)

                     

                    XML structure -

                    <POWERMART>

                    <REPOSITORY>

                      <FOLDER>

                            <MAPPING NAME="aaa">

                             . . .

                            </MAPPING>

                            <MAPPING NAME="bbb">

                             . . .

                            </MAPPING>

                            <MAPPING NAME="ccc">

                             . . .

                            </MAPPING>

                      </FOLDER>

                    </REPOSITORY>

                    </POWERMART>

                     

                    My query -

                    select

                    --t.*, t1.*, t2.*, t3.*

                      t5.*

                    from

                    test_xml x

                    , XMLTABLE

                    (

                      '/POWERMART' passing xmltype(x.x_val)

                      columns

                       p_creation_date           varchar2(50) path '@CREATION_DATE',

                       r_repo_name               varchar2(50) path 'REPOSITORY/@NAME',

                       r_version                 varchar2(50) path 'REPOSITORY/@VERSION',

                       r_codepage                varchar2(50) path 'REPOSITORY/@CODEPAGE',

                       r_databasetype            varchar2(50) path 'REPOSITORY/@DATABASETYPE',

                       f_name                    varchar2(50) path 'REPOSITORY/FOLDER/@NAME',

                       f_group                   varchar2(50) path 'REPOSITORY/FOLDER/@GROUP',

                       f_owner                   varchar2(50) path 'REPOSITORY/FOLDER/@OWNER',

                       f_shared                  varchar2(50) path 'REPOSITORY/FOLDER/@SHARED',

                       f_description             varchar2(50) path 'REPOSITORY/FOLDER/@DESCRIPTION',

                       f_permissions             varchar2(50) path 'REPOSITORY/FOLDER/@PERMISSIONS',

                       f_uuid                    varchar2(50) path 'REPOSITORY/FOLDER/@UUID',

                       s_source                  xmltype      path 'REPOSITORY/FOLDER/SOURCE',

                          t_target                  xmltype      path 'REPOSITORY/FOLDER/TARGET',

                          m_mapping                 xmltype      path 'REPOSITORY/FOLDER/MAPPING'

                    ) t

                    /*, XMLTABLE

                    (

                      '/SOURCE' passing t.s_source

                      columns

                       s_businessname            varchar2(50) path '@BUSINESSNAME',

                       s_databasetype            varchar2(50) path '@DATABASETYPE',

                       s_dbdname                 varchar2(50) path '@DBDNAME',

                       s_description             varchar2(50) path '@DESCRIPTION',

                       s_name                    varchar2(50) path '@NAME',

                       s_objectversion           varchar2(50) path '@OBJECTVERSION',

                       s_ownername               varchar2(50) path '@OWNERNAME',

                       s_versionnumber           varchar2(50) path '@VERSIONNUMBER',

                       f_codepage                varchar2(50) path 'FLATFILE/@CODEPAGE',

                       f_consecdelimitersasone   varchar2(50) path 'FLATFILE/@CONSECDELIMITERSASONE',

                       f_delimited               varchar2(50) path 'FLATFILE/@DELIMITED',

                       f_delimiters              varchar2(50) path 'FLATFILE/@DELIMITERS',

                       f_escape_character        varchar2(50) path 'FLATFILE/@ESCAPE_CHARACTER',

                       f_keepescapechar          varchar2(50) path 'FLATFILE/@KEEPESCAPECHAR',

                       f_linesequential          varchar2(50) path 'FLATFILE/@LINESEQUENTIAL',

                       f_multidelimitersasand    varchar2(50) path 'FLATFILE/@MULTIDELIMITERSASAND',

                       f_nullchartype            varchar2(50) path 'FLATFILE/@NULLCHARTYPE',

                       f_null_character          varchar2(50) path 'FLATFILE/@NULL_CHARACTER',

                       f_padbytes                varchar2(50) path 'FLATFILE/@PADBYTES',

                       f_quote_character         varchar2(50) path 'FLATFILE/@QUOTE_CHARACTER',

                       f_repeatable              varchar2(50) path 'FLATFILE/@REPEATABLE',

                       f_rowdelimiter            varchar2(50) path 'FLATFILE/@ROWDELIMITER',

                       f_shiftsensitivedata      varchar2(50) path 'FLATFILE/@SHIFTSENSITIVEDATA',

                       f_skiprows                varchar2(50) path 'FLATFILE/@SKIPROWS',

                       f_striptrailingblanks     varchar2(50) path 'FLATFILE/@STRIPTRAILINGBLANKS',

                          --- f_tableattribute          xmltype      path 'TABLEATTRIBUTE',

                       s_sourcefield             xmltype      path 'SOURCEFIELD'

                    ) t1*/

                      , XMLTABLE

                      (

                        '/MAPPING' passing t.m_mapping

                        columns

                          --map_id for ordinality,

                          map_name      varchar2(50) path '@NAME'

                      ) t5

                      /*, xmltable

                      (

                        '/TARGET' passing t.t_target

                        columns

                          t_businessname          varchar2(50)  path '@BUSINESSNAME',

                          t_constraint            varchar2(50)  path '@CONSTRAINT',

                          t_databasetype          varchar2(50)  path '@DATABASETYPE',

                          t_description           varchar2(100) path '@DESCRIPTION',

                          t_name                  varchar2(50)  path '@NAME',

                          t_objectversion         varchar2(50)  path '@OBJECTVERSION',

                          t_tableoptions          varchar2(50)  path '@TABLEOPTIONS',

                          t_versionnumber         varchar2(50)  path '@VERSIONNUMBER',

                          t_targetfield           xmltype       path 'TARGETFIELD'

                      ) t2 */

                    /*, XMLTABLE

                    (

                      '/SOURCEFIELD' passing t1.s_sourcefield

                      columns

                       sf_businessname           varchar2(50) path '@BUSINESSNAME',

                       sf_datatype               varchar2(50) path '@DATATYPE',

                       sf_description            varchar2(50) path '@DESCRIPTION',

                       sf_fieldnumber            varchar2(50) path '@FIELDNUMBER',

                       sf_fieldproperty          varchar2(50) path '@FIELDPROPERTY',

                       sf_fieldtype              varchar2(50) path '@FIELDTYPE',

                       sf_hidden                 varchar2(50) path '@HIDDEN',

                       sf_keytype                varchar2(50) path '@KEYTYPE',

                       sf_length                 varchar2(50) path '@LENGTH',

                       sf_level                  varchar2(50) path '@LEVEL',

                       sf_name                   varchar2(50) path '@NAME',

                       sf_nullable               varchar2(50) path '@NULLABLE',

                       sf_occurs                 varchar2(50) path '@OCCURS',

                       sf_offset                 varchar2(50) path '@OFFSET',

                       sf_physicallength         varchar2(50) path '@PHYSICALLENGTH',

                       sf_physicaloffset         varchar2(50) path '@PHYSICALOFFSET',

                       sf_picturetext            varchar2(50) path '@PICTURETEXT',

                       sf_precision              varchar2(50) path '@PRECISION',

                       sf_scale                  varchar2(50) path '@SCALE',

                       sf_usage_flags            varchar2(50) path '@USAGE_FLAGS'

                    ) t3

                      , XMLTABLE

                      (

                        '/TARGETFIELD' passing t2.t_targetfield

                        columns

                            tf_id for ordinality,

                            tf_businessname           varchar2(50) path '@BUSINESSNAME',

                            tf_datatype               varchar2(50) path '@DATATYPE',

                            tf_description            varchar2(50) path '@DESCRIPTION',

                            tf_fieldnumber            varchar2(50) path '@FIELDNUMBER',

                            tf_keytype                varchar2(50) path '@KEYTYPE',

                            tf_name                   varchar2(50) path '@NAME',

                            tf_nullable               varchar2(50) path '@NULLABLE',

                            sf_picturetext            varchar2(50) path '@PICTURETEXT',

                            sf_precision              varchar2(50) path '@PRECISION',

                            sf_scale                  varchar2(50) path '@SCALE'

                      ) t4 */

                    ;

                    This gives me the output of t5.map_name. But, if I uncomment XMLTABLE - t1, it gives NULL as result.

                     

                    Could you please explain me this behavior?

                     

                    Please advise.

                    • 7. Re: Reg: XML parsing in SQL -
                      Karthick2003

                      I dont see nodes TARGET and MAPPING in the input xml.

                      • 8. Re: Reg: XML parsing in SQL -
                        ranit B

                        Please consider this as input XML -

                        <?xml version="1.0" encoding="ISO-8859-1"?>

                        <POWERMART CREATION_DATE="03/14/2014 19:20:05" REPOSITORY_VERSION="182.91">

                        <REPOSITORY NAME="RS_AAA_DEV" VERSION="182" CODEPAGE="Latin1" DATABASETYPE="Oracle">

                        <FOLDER NAME="dev_ranit" GROUP="" OWNER="dev_ranit" SHARED="NOTSHARED" DESCRIPTION="Ranit" PERMISSIONS="rwx---r--" UUID="4623dd59-fcbf-4c71-9234-9a8d03e6fc51">

                        <MAPPING DESCRIPTION ="" ISVALID ="YES" NAME ="mapping_x1" OBJECTVERSION ="1" VERSIONNUMBER ="1">

                                <TRANSFORMATION DESCRIPTION ="" NAME ="SQ_test_x1" OBJECTVERSION ="1" REUSABLE ="NO" TYPE ="Source Qualifier" VERSIONNUMBER ="1">

                                    <TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="AAA_NUMBER" PICTURETEXT ="" PORTTYPE ="INPUT/OUTPUT" PRECISION ="50" SCALE ="0"/>

                                    <TABLEATTRIBUTE NAME ="User Defined Join" VALUE =""/>

                                    <TABLEATTRIBUTE NAME ="Source Filter" VALUE =""/>

                                    <TABLEATTRIBUTE NAME ="Number Of Sorted Ports" VALUE ="0"/>

                                </TRANSFORMATION>

                            </MAPPING>

                        </FOLDER>

                        </REPOSITORY>

                        </POWERMART>

                         

                        I guess it is not mandatory to have SOURCE and TARGET in the input.

                        • 9. Re: Reg: XML parsing in SQL -
                          Karthick2003

                          because your s_source XML is NULL. And you are using it as the source for T1. the resutset of T1 will be empty. So what will happen when you join a table with 0 rows . Got it?

                           

                          Try OUTER JOIN

                           

                          select

                            t5.*

                          from

                          test_xml x

                          , XMLTABLE

                          (

                            '/POWERMART' passing xmltype(x.x_val)

                            columns

                             p_creation_date           varchar2(50) path '@CREATION_DATE',

                             r_repo_name               varchar2(50) path 'REPOSITORY/@NAME',

                             r_version                 varchar2(50) path 'REPOSITORY/@VERSION',

                             r_codepage                varchar2(50) path 'REPOSITORY/@CODEPAGE',

                             r_databasetype            varchar2(50) path 'REPOSITORY/@DATABASETYPE',

                             f_name                    varchar2(50) path 'REPOSITORY/FOLDER/@NAME',

                             f_group                   varchar2(50) path 'REPOSITORY/FOLDER/@GROUP',

                             f_owner                   varchar2(50) path 'REPOSITORY/FOLDER/@OWNER',

                             f_shared                  varchar2(50) path 'REPOSITORY/FOLDER/@SHARED',

                             f_description             varchar2(50) path 'REPOSITORY/FOLDER/@DESCRIPTION',

                             f_permissions             varchar2(50) path 'REPOSITORY/FOLDER/@PERMISSIONS',

                             f_uuid                    varchar2(50) path 'REPOSITORY/FOLDER/@UUID',

                             s_source                  xmltype      path 'REPOSITORY/FOLDER/SOURCE',

                             t_target                  xmltype      path 'REPOSITORY/FOLDER/TARGET',

                             m_mapping                 xmltype      path 'REPOSITORY/FOLDER/MAPPING'

                          ) t

                          left join XMLTABLE

                          (

                            '/SOURCE' passing t.s_source

                            columns

                             s_businessname            varchar2(50) path '@BUSINESSNAME',

                             s_databasetype            varchar2(50) path '@DATABASETYPE',

                             s_dbdname                 varchar2(50) path '@DBDNAME',

                             s_description             varchar2(50) path '@DESCRIPTION',

                             s_name                    varchar2(50) path '@NAME',

                             s_objectversion           varchar2(50) path '@OBJECTVERSION',

                             s_ownername               varchar2(50) path '@OWNERNAME',

                             s_versionnumber           varchar2(50) path '@VERSIONNUMBER',

                             f_codepage                varchar2(50) path 'FLATFILE/@CODEPAGE',

                             f_consecdelimitersasone   varchar2(50) path 'FLATFILE/@CONSECDELIMITERSASONE',

                             f_delimited               varchar2(50) path 'FLATFILE/@DELIMITED',

                             f_delimiters              varchar2(50) path 'FLATFILE/@DELIMITERS',

                             f_escape_character        varchar2(50) path 'FLATFILE/@ESCAPE_CHARACTER',

                             f_keepescapechar          varchar2(50) path 'FLATFILE/@KEEPESCAPECHAR',

                             f_linesequential          varchar2(50) path 'FLATFILE/@LINESEQUENTIAL',

                             f_multidelimitersasand    varchar2(50) path 'FLATFILE/@MULTIDELIMITERSASAND',

                             f_nullchartype            varchar2(50) path 'FLATFILE/@NULLCHARTYPE',

                             f_null_character          varchar2(50) path 'FLATFILE/@NULL_CHARACTER',

                             f_padbytes                varchar2(50) path 'FLATFILE/@PADBYTES',

                             f_quote_character         varchar2(50) path 'FLATFILE/@QUOTE_CHARACTER',

                             f_repeatable              varchar2(50) path 'FLATFILE/@REPEATABLE',

                             f_rowdelimiter            varchar2(50) path 'FLATFILE/@ROWDELIMITER',

                             f_shiftsensitivedata      varchar2(50) path 'FLATFILE/@SHIFTSENSITIVEDATA',

                             f_skiprows                varchar2(50) path 'FLATFILE/@SKIPROWS',

                             f_striptrailingblanks     varchar2(50) path 'FLATFILE/@STRIPTRAILINGBLANKS',

                                --- f_tableattribute          xmltype      path 'TABLEATTRIBUTE',

                             s_sourcefield             xmltype      path 'SOURCEFIELD'

                          ) t1 on 1=1

                            left join XMLTABLE

                            (

                              '/MAPPING' passing t.m_mapping

                              columns

                                --map_id for ordinality,

                                map_name      varchar2(50) path '@NAME'

                            ) t5 on 1=1;

                          1 person found this helpful
                          • 10. Re: Reg: XML parsing in SQL -
                            ranit B

                            because your s_source XML is NULL. And you are using it as the source for T1. the resutset of T1 will be empty. So what will happen when you join a table with 0 rows . Got it?

                             

                            Try OUTER JOIN

                            Thanks Karthick. Now I realized my mistake.

                             

                            But, this situation will never arise practically because my XML will always contain SOURCE and  TARGET, as without that the MAPPING is meaningless and can not exist.

                            Since my XML is extremely huge and even CLOB is not able to handle it in WITH clause, I am using chunks of it to parse one-by-one.

                             

                            Even have a workaround for this, but don't have access to _DIRECTORIES -

                            DECLARE

                                   v_xml XMLTYPE := XMLTYPE( BFILENAME('XML_DIR', 'my_file.xml'), NLS_CHARSET_ID('AL32UTF8') )

                            BEGIN

                                 . . .

                            END:

                             

                            Thanks.

                            • 11. Re: Reg: XML parsing in SQL -
                              odie_63

                              First off, in order to get decent performance, you have to store the XML document in an XMLType table (or column) with binary XML storage (this is the default starting with 11.2.0.2).

                              e.g.

                              create table test_xml(
                              x_val xmltype
                              )

                              xmltype column x_val store as securefile binary xml;


                              What I don't understand is why on earth would you want to store the data in a single table when the different blocks have no relationship between each other?

                              1 person found this helpful
                              • 12. Re: Reg: XML parsing in SQL -
                                ranit B

                                Hi Odie,

                                 

                                Thanks for your response.

                                First off, in order to get decent performance, you have to store the XML document in an XMLType table (or column) with binary XML storage (this is the default starting with 11.2.0.2)

                                Why do you say "get decent performance" and not "good performance" ? Might be because it will further depend on the way query is written?

                                 

                                What I don't understand is why on earth would you want to store the data in a single table when the different blocks have no relationship between each other?

                                Very genuine concern and same point was also raised by Karthick.

                                Only thing was trying to fit in same table w/o creating many tables. I know it violates the NFs but was trying if it works.

                                • 13. Re: Reg: XML parsing in SQL -
                                  odie_63

                                  Why do you say "get decent performance" and not "good performance" ? Might be because it will further depend on the way query is written?

                                  I meant "decent" as "good enough" because the query will be resolved using streaming XPath evaluation, which is a huge improvement compared to the functional (DOM) evaluation that occurs when the XMLType storage method is CLOB.

                                  Indeed, the query may be further improved by using a structured XMLIndex on top of that.

                                  1 person found this helpful