Defines an index for a column of type XML, with a specified substructure as the key.
CREATE [UNIQUE] INDEX [authorization-identifier.]index-identifier |
For rules and other details about operands other than the KEY clause, see CREATE INDEX Format 1 (Define index) in Chapter 3.
substructure-path ::=[XML-namespace-declaration]... substructure-path-expression |
Element | Meaning |
---|---|
Leading forward slash (/) | Indicates that the element in the step expression after the forward slash is the topmost XML element of the XML type value. |
Middle forward slash (/) | Indicates that the XML element or attribute in the step expression after the forward slash is a child element or an attribute of the XML expression in the step expression specified before the forward slash. |
step-expression | Indicates an XML element or attribute. However, a step expression referring to an XML attribute can be specified only at the end of the expression. |
@ | Indicates that the qualified name after the at mark (@) is the name of an XML attribute. A qualified name without @ refers to an XML element. |
qualified-name | Indicates the name of an XML element or XML attribute. |
prefix: | Indicates that the immediately following local-name is a local name within the XML namespace indicated by the prefix. The prefix that is bound to the XML namespace is specified in the XML namespace declaration. If prefix: is omitted, local-name is the name of the default XML namespace. |
local-name | Indicates the name of an XML element or attribute in the XML namespace. |
No. | Example | Meaning |
---|---|---|
1 | '/bookinfo/category' | The key is the XML element category, which is a child of bookinfo, the topmost XML element. |
2 | '/bookinfo/@book_id' | The key is the attribute book_id, which is a child of bookinfo, the topmost XML element. |
3 | 'declare namespace b="http://www.foo.co.jp/bookinfo"; /b:bookinfo/b:category' | The key is the XML element category, which is a child of bookinfo, the topmost XML element. However, the names of the XML elements bookinfo and category are located in the XML namespace whose URI is http://www.foo.co.jp/bookinfo. |
4 | 'declare default element namespace "http://www.foo.co.jp/bookinfo"; /bookinfo/category' | Same as No. 3. |
Table 1-41 Correspondence between XQuery data types and SQL data types
XQuery data type of typed-value properties of nodes in an XQuery data model substructure | SQL data type | |||
---|---|---|---|---|
INTEGER | DECIMAL | FLOAT | VARCHAR | |
xs:untypedAtomic | C | C | C | C |
xs:int | E | NC | NC | NC |
xs:decimal | NC | E | NC | NC |
xs:double | NC | NC | E | NC |
xs:string | NC | NC | NC | E |
Other | NC | NC | NC | NC |
Table 1-42 Lengths of key values
Data type | Length of key value | |
---|---|---|
INTEGER | 4 | |
DECIMAL[(m[, n])] | ![]() ![]() ![]() | |
FLOAT | 8 | |
VARCHAR | Actual data length is 255 bytes or less | n1 + 1 |
Actual data length is 256 bytes or more | n1 + 2 |
Table 1-43 XQuery data type when an SQL data type is evaluated in XQuery
SQL data type | XQuery data type |
---|---|
INTEGER | xs:int |
DECIMAL | xs:decimal |
FLOAT | xs:double |
VARCHAR | xs:string |
CREATE INDEX INDX1 ON BOOK_MANAGEMENT_TABLE(bookinfo)
KEY FROM '/bookinfo/category' AS VARCHAR (100)
CREATE INDEX INDX2 ON BOOK_MANAGEMENT_TABLE(bookinfo)
KEY USING UNIQUE TAG FROM '/bookinfo/price' AS INTEGER
CREATE UNIQUE INDEX INDX3 ON BOOK_MANAGEMENT_TABLE(bookinfo)
KEY FROM '/bookinfo/@book_id' AS INTEGER