Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 element - sequence - complextype(XSD--->XML--->Table--->Inser)t ? как вставить данные ?  [new]
romai
Member

Откуда:
Сообщений: 130
Добрый день господа - я столкнулся с проблемой вставки данных в XML таблицу - прошу помочь найти решение.
Сейчас мне принципиально положить в таблицу хоть какие нибудь данные.

XSD - схема
----------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" ?>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
   targetNamespace="http://labuda/xml/AccountInterface.xsd" 
   xmlns:xsdLocal="http://labuda/xml/AccountInterface.xsd" 
   attributeFormDefault="unqualified" elementFormDefault="qualified" > 
    <xsd:annotation>
      <xsd:documentation>Copyright (C) 2001-2004 Siebel Systems, Inc. All rights reserved. Siebel XSD Generation</xsd:documentation>
    </xsd:annotation>
    <xsd:element name="SiebelMessage" type="xsdLocal:SiebelMessage" />
    <xsd:complexType name="SiebelMessage">
      <xsd:sequence>
        <xsd:element name="ListOfAccountInterface" type="xsdLocal:ListOfAccountInterface" minOccurs="0" maxOccurs="1" />
      </xsd:sequence>
      <xsd:attribute name="MessageId" type="xsd:string" />
      <xsd:attribute name="MessageType" type="xsd:string" />
      <xsd:attribute name="IntObjectName" type="xsd:string" />
      <xsd:attribute name="IntObjectFormat" type="xsd:string" />
    </xsd:complexType>
    <xsd:complexType name="ListOfAccountInterface">
      <xsd:sequence>
        <xsd:element name="Account" type="xsdLocal:Account" minOccurs="1" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="Account">
      <xsd:sequence>
        <xsd:element name="AccountId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AccountStatus" type="xsdLocal:string24" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AnnualRevenue" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="CurrencyCode" type="xsdLocal:string15" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="INN" type="xsdLocal:string15" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="FoundationDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Description" type="xsdLocal:string255" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="WebSite" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="IntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Location" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="SourceInfType" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="MainEmailAddress" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="MainFaxNumber" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="MainPhoneNumber" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="SourceInfDesc" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Name" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PartyName" type="xsdLocal:string150" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="RegistrationInfo" type="xsdLocal:string250" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="StartDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="FullName" type="xsdLocal:string1000" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ListOfBusAddress" type="xsdLocal:ListOfBusAddress" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ListOfManager" type="xsdLocal:ListOfManager" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ListOfContact" type="xsdLocal:ListOfContact" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ListOfOrganization" type="xsdLocal:ListOfOrganization" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ListOfIndustry" type="xsdLocal:ListOfIndustry" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ListOfFinAccount" type="xsdLocal:ListOfFinAccount" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
      <xsd:attribute name="operation" type="xsd:string"/>
    </xsd:complexType>
    <xsd:complexType name="ListOfBusAddress">
      <xsd:sequence>
        <xsd:element name="BusAddress" type="xsdLocal:BusAddress" minOccurs="0" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="BusAddress">
      <xsd:sequence>
        <xsd:element name="AddressId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="IntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Country" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PostalCode" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Province" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="County" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="State" type="xsdLocal:string10" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="City" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="StreetType" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="StreetName" type="xsdLocal:string200" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="HouseNumber" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Drob" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Bukva" type="xsdLocal:string1" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Korpus" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Stroenie" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ApartmentNumber" type="xsdLocal:string5" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AddressName" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
      <xsd:attribute name="IsPrimaryMVG" type="xsd:string"/>
      <xsd:attribute name="operation" type="xsd:string"/>
    </xsd:complexType>
    <xsd:complexType name="ListOfManager">
      <xsd:sequence>
        <xsd:element name="Manager" type="xsdLocal:Manager" minOccurs="1" maxOccurs="1"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="Manager">
      <xsd:sequence>
        <xsd:element name="FirstName" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="LastName" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Position" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PositionId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PositionIntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Login" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="DivisionIntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Division" type="xsdLocal:string255" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
      <xsd:attribute name="IsPrimaryMVG" type="xsd:string"/>
    </xsd:complexType>
    <xsd:complexType name="ListOfContact">
      <xsd:sequence>
        <xsd:element name="Contact" type="xsdLocal:Contact" minOccurs="0" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="Contact">
      <xsd:sequence>
        <xsd:element name="ContactId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AlternatePhoneNumber" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="BirthDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="CellularPhoneNumber" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="FaxPhoneNumber" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="FirstName" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="HomePhoneNumber" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ContactIntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="LastName" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="MiddleName" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PersonUId" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PrimaryOrganization" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="WorkPhoneNumber" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="WorkPhoneExtension" type="xsdLocal:string10" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="ListOfOrganization">
      <xsd:sequence>
        <xsd:element name="Organization" type="xsdLocal:Organization" minOccurs="1" maxOccurs="1"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="Organization">
      <xsd:sequence>
        <xsd:element name="Organization" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="OrganizationId" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="OrganizationIntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
      <xsd:attribute name="IsPrimaryMVG" type="xsd:string"/>
    </xsd:complexType>
    <xsd:complexType name="ListOfIndustry">
      <xsd:sequence>
        <xsd:element name="Industry" type="xsdLocal:Industry" minOccurs="1" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="Industry">
      <xsd:sequence>
        <xsd:element name="Description" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Industry" type="xsdLocal:string255" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="IndustryId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="IndustryIntegrationId" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="SICCode" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
      <xsd:attribute name="IsPrimaryMVG" type="xsd:string"/>
    </xsd:complexType>
    <xsd:complexType name="ListOfFinAccount">
      <xsd:sequence>
        <xsd:element name="FinAccount" type="xsdLocal:FinAccount" minOccurs="0" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="FinAccount">
      <xsd:sequence>
        <xsd:element name="AssetStatus" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AssetNumber" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AssetOwnerName" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="DivisionCode" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="DivisionName" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="DivisionId" type="xsdLocal:string50" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AssettType" type="xsdLocal:string250" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="CurrencyCode" type="xsdLocal:string15" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="AssetName" type="xsdLocal:string250" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="BalanceCode" type="xsdLocal:string30" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="OwnerId" type="xsdLocal:string15" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ProductId" type="xsdLocal:string15" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Id" type="xsdLocal:string15" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="ProductName" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="PrimaryOwnerName" type="xsdLocal:string100" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="StartDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="EndDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="CurrentRest" type="xsd:string" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
      <xsd:attribute name="operation" type="xsd:string"/>
    </xsd:complexType>
    <xsd:simpleType name ="string250">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="250"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string50">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="50"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string24">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="24"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string10">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="10"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string1000">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="1000"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string255">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="255"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string15">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="15"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string30">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="30"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string5">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="5"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string150">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="150"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string1">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="1"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string200">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="200"/>
      </xsd:restriction>
    </xsd:simpleType>
    <xsd:simpleType name ="string100">
      <xsd:restriction base="xsd:string">
        <xsd:maxLength value="100"/>
      </xsd:restriction>
    </xsd:simpleType>
  </xsd:schema>
Регистрация
----------------------------------------------------------------

BEGIN
    DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://labuda/xml/AccountInterface.xsd',    
    SCHEMADOC => bfilename('XMLDIR','BANKAccountInterface.xsd')
    );
END;
Создание таблицы
---------------------------------------------------------------
CREATE table xListSiebelMessage of XMLTYPE
XMLSCHEMA "http://labuda/xml/AccountInterface.xsd"
element  "SiebelMessage";
Внимание вопрос
как создать таблицу от клмплексного типа

Вставка данных
---------------------------------------------------------------
Insert into xListSiebelMessage values 
(xmltype('<SiebelMessage><ListOfAccountInterface><Account><AccountStatus> Заявка рассматривается</AccountStatus></Account></ListOfAccountInterface></SiebelMessage>')); 
----------------------------------------------------------------

Сообщение было отредактировано: 8 сен 08, 19:38
20 июн 07, 12:54    [4291863]     Ответить | Цитировать Сообщить модератору
 Re: element - sequence - complextype(XSD--->XML--->Table--->Inser)t ? как вставить данные ?  [new]
romai
Member

Откуда:
Сообщений: 130
Ошибка при вставке Ora-30937
Отсутствует определение схемы для SiebelMessage(пространство имён) в родительском '/'
20 июн 07, 13:07    [4292109]     Ответить | Цитировать Сообщить модератору
 Re: element - sequence - complextype(XSD--->XML--->Table--->Inser)t ? как вставить данные ?  [new]
romai
Member

Откуда:
Сообщений: 130
поправочка - регистрация
Регистрация----------------------------------------------------------------
BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://labuda/xml/AccountInterface.xsd', 
SCHEMADOC => bfilename('XMLDIR','AccountInterface.xsd')
);
END;


Сообщение было отредактировано: 8 сен 08, 19:40
20 июн 07, 14:11    [4293319]     Ответить | Цитировать Сообщить модератору
 Re: element - sequence - complextype(XSD--->XML--->Table--->Inser)t ? как вставить данные ?  [new]
OldOwl
Guest
атрибутик xmlns="http://labuda/xml/AccountInterface.xsd" добавь в корень своего документа
21 июн 07, 05:50    [4296966]     Ответить | Цитировать Сообщить модератору
 Re: element - sequence - complextype(XSD--->XML--->Table--->Inser)t ? как вставить данные ?  [new]
romai
Member

Откуда:
Сообщений: 130
Сработало - отлично, но при выборке - value error
23 июн 07, 12:44    [4306984]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить