This discussion is archived
1 Reply Latest reply: Jul 1, 2012 12:10 PM by AlexAnd RSS

How to insert folloiwng XML file in Oracle -

user228899 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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> 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points