1 Reply Latest reply: Jul 1, 2012 2:10 PM by AlexAnd RSS

    How to insert folloiwng XML file in Oracle -

    user228899
      Hello

      This is on Database 11g.

      I have a very large XML file with following elements. I am new to PL SQL XML programming. How can I make Oracle read the OS file location where the XML file is located, open the XML file and insert XML data residing in their tag elements and non-tag elements ?
      <BillOfMaterialItem revisionIdentifier="--" billOfMaterialItemIdentifier="645-173-1" billOfMaterialItemUniqueIdentifier="IVI123019387" itemQuantity="6" globalProductQuantityTypeCode="PerAssembly" globalProductQuantityTypeCodeOther="" notes="" description="3/8" WIDE" proprietarySequenceIdentifier="115510509">
      - <AdditionalAttributes groupLabel="BOM Information">
        <AdditionalAttribute name="Line Number" value="20" dataType="Float" /> 
        </AdditionalAttributes>
        </BillOfMaterialItem>
      {code}
      
      Thanks,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: How to insert folloiwng XML file in Oracle -
          AlexAnd
          forum has many example
          look at Re: Load xml data in Oracle table

          steps:
          - create oracle directory
          - create table(s) for your data, may be with column xmltype
          - parse xml files from oracle directory and insert into table(s)

          for example
          SQL> select * from v$version where rownum=1;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           
          SQL> 
          SQL> select *
            2      from (xmltable('*'
            3                     passing (xmltype(bfilename('GET_XML','sample.xml'),nls_charset_id('AL32UTF8')))
            4                     columns billofmaterialitem_revisionid varchar2(10) path '/BillOfMaterialItem/@revisionIdentifier'
            5                            , billofmaterialitem_itemid varchar2(20) path '/BillOfMaterialItem/@billOfMaterialItemIdentifier'
            6                            , BillOfMaterialItem_ItUId varchar2(20) PATH '/BillOfMaterialItem/@billOfMaterialItemUniqueIdentifier'
            7                            , billofmaterialitem_itemq varchar2(20) path '/BillOfMaterialItem/@itemQuantity'
            8                            , billofmaterialitem_gqtcode varchar2(20) path '/BillOfMaterialItem/@globalProductQuantityTypeCode'
            9                            , BillOfMaterialItem_notes varchar2(20) PATH '/BillOfMaterialItem/@notes'
           10                            , BillOfMaterialItem_desc varchar2(20) PATH '/BillOfMaterialItem/@description'
           11                            , billofmaterialitem_prseqid varchar2(20) path '/BillOfMaterialItem/@proprietarySequenceIdentifier'
           12                            , additionalattributes_grlbl varchar2(20) path '/BillOfMaterialItem/AdditionalAttributes/@groupLabel'
           13                            , additionalattribute_name varchar2(20) path '/BillOfMaterialItem/AdditionalAttributes//AdditionalAttribute/@name'
           14                            , additionalattribute_value varchar2(20) path '/BillOfMaterialItem/AdditionalAttributes//AdditionalAttribute/@value'
           15                            , AdditionalAttribute_dataType varchar2(20) PATH '/BillOfMaterialItem/AdditionalAttributes//AdditionalAttribute/@dataType'
           16                    )
           17           ) x
           18  /
           
          BILLOFMATERIALITEM_REVISIONID BILLOFMATERIALITEM_ITEMID BILLOFMATERIALITEM_ITUID BILLOFMATERIALITEM_ITEMQ BILLOFMATERIALITEM_GQTCODE BILLOFMATERIALITEM_NOTES BILLOFMATERIALITEM_DESC BILLOFMATERIALITEM_PRSEQID ADDITIONALATTRIBUTES_GRLBL ADDITIONALATTRIBUTE_NAME ADDITIONALATTRIBUTE_VALUE ADDITIONALATTRIBUTE_DATATYPE
          ----------------------------- ------------------------- ------------------------ ------------------------ -------------------------- ------------------------ ----------------------- -------------------------- -------------------------- ------------------------ ------------------------- ----------------------------
          --                            645-173-1                 IVI123019387             6                        PerAssembly                                         3/8                     115510509                  BOM Information            Line Number              20                        Float
           
          SQL>