24.3. An Example of a Complex Type Mapping Supporting Mixed Content to a Database Schema

Mixed content allows free-form text interspersed with elements. Listing 24.4 demonstrates an XML representation of assemblyCatalogEntryDescriptionType that permits valid lists of part numbers to be interspersed with text. This listing is similar to Listing 11.4 and differs only in the type that forms the element type and the addition of an attribute for the manufacturer.

Listing 24.4. An XML Document Fragment of a Complex Type That Supports Mixed Content
<xsd:complexType name="assemblyCatalogEntryDescriptionType" 
        mixed="true" 
        id="assemblyCatalogEntryDescriptionType.[ccc] 
catalog.cType"> 
 <xsd:annotation> 
   <xsd:documentation xml:lang="en"> 
     Allow the description of a part 
     to include assembly part number references. 
   </xsd:documentation> 
 </xsd:annotation> 
 <xsd:sequence minOccurs="0" maxOccurs="unbounded"> 
   <xsd:element name="assemblypartList" 
          type="assemblypartNumberListType"/> 
 </xsd:sequence> 
 <xsd:attribute name="manufacturer" type="xsd:string"/> 
</xsd:complexType>

The XML document representation of an element type whose structure type is assemblyCatalogEntryDescriptionType might look like the following:

<xsd:element name="description" 
      type="assemblyCatalogEntryDescriptionType"/>

Given the preceding element type, the following description element is valid in an XML instance:

<description> 
 Our product line in this area, the 
 <assemblypartList>ASM2000 ASM2002</assemblypartList> 
 are far superior to our competitors' products, the 
 <assemblypartList manufacturer="Acme">ASM020 ASM030 
 </assemblypartList> which are manufactured in Elbonia. 
 Even our last generation products 
 <assemblypartList>ASM0200 ASM0202</assemblypartList> 
 are far superior to our competitors' existing products. 
</description>

In mapping this to a relational database, it is important to understand the usage requirements of the description element. If the sole need is to simply insert the entire description without validation and retrieve it later on, a character database column datatype is sufficient. Otherwise, all the assemblyPartList items must be stored individually. In addition, a charac-ter database column is needed for storing the entire mixed content string. First, Listing 24.5 maps an assemblyPartNumberListType to a database representation. Second, Listing 24.6 maps a description to a database representation.

Listing 24.5. Mapping assemblyPartNumberListType to the Database Tables AssPartNumberListType and AssPartNumberType with a Column Constraint
--Start the sequence at 1, don't let it cycle, and have 
--the numbers in the sequence ordered. 
CREATE SEQUENCE Seq_AssPartNumberListType INCREMENT BY 1 START 
 WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER; 

CREATE TABLE AssPartNumberListType ( 
assPartNumberListTypeID NUMBER(9) NOT NULL, 
PRIMARY KEY (assPartNumberListTypeID) 
); 

CREATE TABLE AssPartNumberType ( 
assPartNumberTypeID NUMBER(9) NOT NULL, 
listOrder      NUMBER(3) NOT NULL, 
partNumberType    VARCHAR2(11) NOT NULL 
CHECK (INSTR(partNumberType, UNISTR('00D')) = 0 AND 
    INSTR(partNumberType, UNISTR('00A')) = 0 AND 
    INSTR(partNumberType, UNISTR('009')) = 0 AND 
    LENGTH(partNumberType) = 
     LENGTH(TRIM(partNumberType)) AND 
    INSTR(partNumberType, ' ') = 0 AND 
    LENGTH(partNumberType) BETWEEN 4 AND 11 AND 
    SUBSTR(partNumberType, 1, 3) = 'ASM' AND 
    -- If the value is not a number, then an 
    -- error will be thrown. IS NOT NULL makes 
    -- this valid SQL, but is meaningless. 
    CAST(SUBSTR(partNumberType, 4, 8) AS NUMBER) 
     IS NOT NULL), 
manufacturer     VARCHAR2(100), 
PRIMARY KEY (assPartNumberTypeID, listOrder), 
FOREIGN KEY (assPartNumberTypeID) 
REFERENCES AssPartNumberListType 
);

Simply storing a list of lists of part numbers for a description is probably insufficient in this case, because the context of the part numbers would not be represented. In the XML instance example, it appears that some of the parts are manufactured by this company but others are manufactured by competitors. It would be difficult to determine which is which without representing the context. Listing 24.6 maps a description to a database representation with the following five columns:

  • This manufacturer’s assemblypartNumberListType

  • The competitor’s assemblypartNumberListType

  • The mixed content text

  • An order (because the type specifies a sequence)

  • A surrogate primary key (because the type does not specify that there is any content or that the content is unique)

Parsing the description into these five columns is beyond the scope of this chapter.

Listing 24.6. DescriptionExample Tables Creation with a Column Constraint
--Start the sequence at 1, don't let it cycle, and have 
--the numbers in the sequence ordered. 
CREATE SEQUENCE Seq_AssCatalogEntryDescType INCREMENT BY 1 
START WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER; 

CREATE TABLE DescriptionExample ( 
ourAssPartNumberListTypeID  NUMBER(9), 
theirAssPartNumberListTypeID NUMBER(9), 
assCatalogEntryDescType    VARCHAR2(1000), 
sequenceOrder         NUMBER(3) NOT NULL, 
assCatalogEntryDescTypeID   NUMBER(9) NOT NULL, 
PRIMARY KEY (assCatalogEntryDescTypeID, sequenceOrder), 
FOREIGN KEY (ourAssPartNumberListTypeID) 
REFERENCES AssPartNumberListType, 
FOREIGN KEY (theirAssPartNumberListTypeID) 
REFERENCES AssPartNumberListType 
);

Listing 24.7 inserts the description element type into the database by using an anonymous PL/SQL block to maintain the primary keys between insert statements.

Listing 24.7. Inserting a Description Element Type into the Database
--Since there is one sequence for the primary key in 
--AssPartNumberListType and multiple keys are needed 
--for the insert into DescriptionExample, 
--create an anonymous PL/SQL block. 

DECLARE 
val1 PLS_INTEGER; 
val2 PLS_INTEGER; 
BEGIN  

SELECT Seq_AssPartNumberListType.NEXTVAL 
INTO  val1 
FROM  dual; 

SELECT Seq_AssPartNumberListType.NEXTVAL 
INTO  val2 
FROM  dual; 

INSERT INTO AssPartNumberListType 
VALUES 
(val1); 

INSERT INTO AssPartNumberType 
(assPartNumberTypeID, listOrder, partNumberType) 
VALUES 
(val1, 1, 'ASM2000'), 

INSERT INTO AssPartNumberType 
(assPartNumberTypeID, listOrder, partNumberType) 
VALUES 
(val1, 2, 'ASM2002'), 

INSERT INTO AssPartNumberListType 
VALUES 
(val2); 

INSERT INTO AssPartNumberType 
(assPartNumberTypeID, listOrder, partNumberType, 
manufacturer) 
VALUES 
(val2, 1, 'ASM0020', 'Acme'), 

INSERT INTO AssPartNumberType 
(assPartNumberTypeID, listOrder, partNumberType) 
VALUES 
(val2, 2, 'ASM0030'), 

INSERT INTO DescriptionExample 
(ourAssPartNumberListTypeID, theirAssPartNumberListTypeID, 
assCatalogEntryDescType, sequenceOrder, 
assCatalogEntryDescTypeID) 
VALUES 
(val1, val2, 
'Our product line in this area, the 
<assemblypartList>ASM2000 ASM2002</assemblypartList> 
are far superior to our competitors' products, the 
<assemblypartList manufacturer="Acme">ASM020 ASM030   
</assemblypartList> which are manufactured in Elbonia. 
Even our last generation products 
<assemblypartList>ASM0200 ASM0202</assemblypartList> 
are far superior to our competitors' existing products.', 1, 
Seq_AssCatalogEntryDescType.NEXTVAL); 

COMMIT; 

END;

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.120.109