Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Unanswered: Begun working with XML within Oracle...

    Oracle 11.02.04

    I have a test table set up, containing several XML files we've used to pass data into an MES system. When inserting the files into the table, they were parsed and saved without complaint.

    The test table is defined as
    Code:
    CREATE TABLE NTI.TEST_XML (
            ID                             NUMBER(10, 0),
            XML_DESCR               VARCHAR2(30 BYTE)	NOT NULL,
            XML_TEMPLATE	        XMLTYPE NOT NULL,
            XML_COMMENT         VARCHAR2(200 BYTE),
            CONSTRAINT PK_TEST_XML PRIMARY KEY (ID) USING INDEX TABLESPACE TALLEY STORAGE ( INITIAL 64 K
                                                                                                                                                    NEXT 1 M																		                                                                                                                                        MAXEXTENTS UNLIMITED)
    One of the recently used xml files as inserted into the table is:

    Code:
    INSERT INTO NTI.TEST_XML(ID, XML_DESCR, XML_TEMPLATE, XML_COMMENT) VALUES
    (1, 'PART_PUSH', XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
    <!--
    Proprietary and Confidential
    Copyright 1995-2010 iBASEt, Inc.
    Unpublished-rights reserved under the Copyright Laws of the United States
    US Government Procurements:
    Commercial Software licensed with Restricted Rights.
    Use, reproduction, or disclosure is subject to restrictions set forth in
    license agreement and purchase contract.
    iBASEt, Inc. 27442 Portola Parkway, Suite 300, Foothill Ranch, CA 92610
    
    Solumina software may be subject to United States Dept of Commerce Export Controls.
    Contact iBASEt for specific Expert Control Classification information.
    -->
    <SyncItemMaster xmlns="http://www.openapplications.org/oagis/9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ibts="http://www.ibaset.com/solumina/oagis" xsi:schemaLocation="http://www.openapplications.org/oagis/9 ../../../org_openapplications_oagis/9_4_1/Developer/BODs/SyncItemMaster.xsd http://www.ibaset.com/solumina/oagis ../Developer/UserArea/ibtsSchemas.xsd" languageCode="en-US" versionID="9_4" releaseID="9_4" systemEnvironmentCode="Production">
      <ApplicationArea>
        <Sender>
          <LogicalID>IPSol</LogicalID>
          <ComponentID>ItemMaster</ComponentID>
          <TaskID>normalizedString</TaskID>
          <ReferenceID>PTALL1513</ReferenceID>
          <ConfirmationCode>Always</ConfirmationCode>
          <AuthorizationID>loquin</AuthorizationID>
        </Sender>
        <CreationDateTime>1999-12-16T15:48:04-07:00</CreationDateTime>
        <Signature/>
        <BODID>2ffc4ea1-bf6c-9f8d-e053-202814ac3d2b</BODID>
        <UserArea/>
      </ApplicationArea>
      <DataArea>
        <Sync>
          <ActionCriteria>
            <ActionExpression actionCode="Replace">/SyncItemMaster/DataArea/ItemMaster[1]</ActionExpression>
          </ActionCriteria>
        </Sync>
        <ItemMaster>
          <ItemMasterHeader>
            <ItemID>
              <ID>PTALL1513</ID>
              <RevisionID>-</RevisionID>
            </ItemID>
            <Description>PLANNING ONLY-LINER</Description>
            <Classification>
              <Codes>
                <Code sequence="1" name="PartType">Component</Code>
                <!-- or "PartType" or "ToolType"-->
              </Codes>
            </Classification>
            <Type>PART</Type>
            <StorageUOMCode>LB</StorageUOMCode>
            <SerialControlIndicator>false</SerialControlIndicator>
            <LotControlIndicator>false</LotControlIndicator>
            <ExpirationControlCode>N</ExpirationControlCode>
            <UserArea>
              <ibts:ItemMasterHeaderUserArea>
                <ibts:EngineeringParentItem>
                  <ID>PTALL1513</ID>
                  <RevisionID>-</RevisionID>
                </ibts:EngineeringParentItem>
                <ibts:ComponentSerialControlIndicator>false</ibts:ComponentSerialControlIndicator>
                <ibts:ComponentLotControlIndicator>false</ibts:ComponentLotControlIndicator>
                <ibts:SpoolIndicator>false</ibts:SpoolIndicator>
                <ibts:StandardItemIndicator>false</ibts:StandardItemIndicator>
                <ibts:UserConfigurableFields>
                  <ibts:UserConfigurableIdentifier name="ItemFlag1" alias="ItemFlag1">N</ibts:UserConfigurableIdentifier>
                  <ibts:UserConfigurableIdentifier name="ItemFlag2" alias="ItemFlag2">N</ibts:UserConfigurableIdentifier>
                  <ibts:UserConfigurableIdentifier name="ItemVarChar1" alias="ItemVarChar1"/>
                </ibts:UserConfigurableFields>
              </ibts:ItemMasterHeaderUserArea>
            </UserArea>
          </ItemMasterHeader>
        </ItemMaster>
      </DataArea>
    </SyncItemMaster>
    '), 'Recent (WORKING) BIS Xfer file for PN PTALL1513');
    I also placed a simpler XML structure into a record for initial testing, with:
    Code:
    INSERT INTO NTI.TEST_XML(ID, XML_DESCR, XML_TEMPLATE, XML_COMMENT) VALUES
    (10, 'Test Simple XML', XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
    <mydata>
      <key>100</key>
      <name>Don Dingle</name>
      <address>
        <addr1>2837 Ford Prefect Way</addr1>
        <addr2>Apt 221B</addr2>
        <city>Milford</city>
        <state>NH</state>
        <zip>03055</zip>
      </address>
      <phone>(603)-123-4567</phone>
    </mydata>
    '), NULL);
    With the simple construct, the following Oracle statement returns city value without a problem:
    Code:
    SELECT t.ID
    	, EXTRACTVALUE(t.XML_TEMPLATE , '/mydata/address/city') AS "city"
    	FROM NTI.test_xml t
    	WHERE t.id = 10;
    However, this select statement returns an empty value for CreationDateTime
    Code:
    SELECT t.ID
    	, EXTRACTVALUE(t.XML_TEMPLATE , '/SyncItemMaster/ApplicationArea/CreationDateTime') AS "CreationDateTime"
    	FROM NTI.test_xml t
    	WHERE t.id = 1;
    Any ideas as to why there's no extracted value with the latter call? No errors are being reported, and as I said, in both cases, the XML parsed fine when the records were inserted.

    Thanks

    Lou
    Last edited by loquin; 04-08-16 at 17:52.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •