Oracle 9i XML

From: Steven Alfisi <steven_at_alfisi.com>
Date: 18 Aug 2003 08:55:07 -0700
Message-ID: <a05aabfc.0308180755.11a8755d_at_posting.google.com>

[Quoted] I am trying to create a set of structures in an Oracle 9.0.1 database to store XML data. There are numerous approaches to take but I can't seem to find one that works best for my situation. Listed below is an example of the xml data I will be receiving. Any ideas on what to do? I was trying to create an object-relational structure using nested tables and the OracleXMLSave in the XDK for Java but I was having problems with the lineitem sections.

TIA <xml>

      <activity_data>
        <activity_header>
          <activity_type>ACTV</activity_type>
          <activity_count>2</activity_count>
        </activity_header>
        <activity_detail>
          <activity offline="false">
            <account>


              <account_number>333</account_number>

              <lineitem_count>2</lineitem_count>


              <lineitem>


                <account_attribute>
                  <name>store_id</name>
                  <value>1</value>
                </account_attribute>
                <account_attribute>
                  <name>sku_number</name>
                  <value>132</value>
                </account_attribute>


              </lineitem>


              <lineitem>


                <account_attribute>
                  <name>mac_id</name>
                  <value>545605640</value>
                </account_attribute>
                <account_attribute>
                  <name>store_id</name>
                  <value>132</value>
                </account_attribute>
                <account_attribute>
                  <name>sku_number</name>
                  <value>43435</value>
                </account_attribute>


              </lineitem>

              <plan>xxx</plan>


              <date date_type="sale">2003-08-05</date>


            </account>
            <customer>


              <customer_id>1</customer_id>

              <last_name>a</last_name>

              <first_name>b</first_name>

              <middle_initial></middle_initial>


              <address address_type="home">


                <address1>123 main st</address1>
                <city>any town</city>
                <state>nj</state>
                <zip_code>12345</zip_code>


              </address>


              <phone phone_type="home">


                <area_code>123</area_code>
                <phone_number>4567890</phone_number>


              </phone>


              <phone phone_type="work">


                <area_code>321</area_code>
                <phone_number>9876543</phone_number>


              </phone>


            </customer>
          </activity>
          <activity offline="false">
            <account>


              <account_number>2</account_number>

              <lineitem_count>2</lineitem_count>


              <lineitem>


                <account_attribute>
                  <name>store_id</name>
                  <value>2</value>
                </account_attribute>
                <account_attribute>
                  <name>sku_number</name>
                  <value>4560</value>
                </account_attribute>


              </lineitem>


              <lineitem>


                <account_attribute>
                  <name>mac_id</name>
                  <value>576569888888</value>
                </account_attribute>
                <account_attribute>
                  <name>store_id</name>
                  <value>5</value>
                </account_attribute>
                <account_attribute>
                  <name>sku_number</name>
                  <value>1234</value>
                </account_attribute>


              </lineitem>

              <plan>xxxx</plan>


              <date date_type="sale">2003-08-05</date>


            </account>
            <customer>


              <customer_id>2424</customer_id>

              <last_name>f</last_name>

              <first_name>x</first_name>

              <middle_initial></middle_initial>


              <address address_type="home">


                <address1>123 main st</address1>
                <city>any town</city>
                <state>nj</state>
                <zip_code>12345</zip_code>


              </address>


              <phone phone_type="home">


                <area_code>435</area_code>
                <phone_number>5464354</phone_number>


              </phone>


              <phone phone_type="work">


                <area_code>453</area_code>
                <phone_number>2564923</phone_number>


              </phone>


            </customer>
          </activity>
        </activity_detail>
      </activity_data>

    </xml> Received on Mon Aug 18 2003 - 17:55:07 CEST