6 Comments

  1. sky_lt
    3/18/2011

    Nice examples:

    Can you provide XML document example which refers to “http://www.server.com/XMLSchema_V1.0.xsd” schema

    Also is it possible to add additional virtual columns based on xml to those tables?

    Does row locking works on such tables? i mean “where current of” statements

    thanks

  2. 3/18/2011

    You will have to provide me with a schema first…

    Yes, it is possible to add virtual columns based on the xml xpath tree definition.

    Yes, Locking works as it should for that database version.

    • sky_lt
      3/18/2011

      Hi Marco,

      I took that shema name “http://www.server.com/XMLSchema_V1.0.xsd” from your example and thought you have xml doc near by.

      I want to check once more:
      Currently i am planing to use XMLDB capabilities on new DB version (11.2) so i want to recheck:

      Is it posible to have additional indexed virtual columns on
      table which is list partitioned as in your examle table “TEST_LIST_XML”

      the ddl should be like(i can not test as i do not have 11.2g enviroment yet):
      CREATE TABLE TEST_LIST_XML
      ( “REGION” VARCHAR2(3),
      “DOC” “SYS”.”XMLTYPE”
      ) SEGMENT CREATION IMMEDIATE
      NOCOMPRESS NOLOGGING
      TABLESPACE “XML_DATA”
      XMLTYPE COLUMN “DOC” STORE AS SECUREFILE BINARY XML
      (TABLESPACE “XML_DATA”
      NOCOMPRESS KEEP_DUPLICATES)
      XMLSCHEMA “http://www.server.com/XMLSchema_V1.0.xsd”
      ELEMENT “ROOT”
      DISALLOW NONSCHEMA
      VIRTUAL COLUMNS
      (xmlrootid AS (extractvalue(object_value,’/ROOT/ID’)),
      xmldata AS (extractvalue(object_value,’/ROOT/ID/data/desc’)));
      PARTITION BY LIST(“REGION”)
      (PARTITION LIST_PART_01 VALUES (‘AAA’, ‘BBB’)
      ,PARTITION LIST_PART_02 VALUES (‘CCC’, ‘DDD’));

      Is it possible to use virtual columns(“xmlrootid” and “xmldata” in my example) in SQL queries or they ar designed only for constraints?

      Thanks

  3. 4/11/2011

    Yes this can be done, but

    a) You should use XMLEXISTS and XMLCAST to define your virtual columns in this 11.2 database version

    b) You should support these virtual columns via a structured XMLIndex or B-Tree unique index to make it performance wise reasonable

Comments are closed.